Introduction
A couple of days ago I've faced an issue to get default paths for databases being created. It is simple to get it with system stored procedure
xp_instance_regread. But the problem is to check these paths for all SQL Server versions starting from 8.0 (i.e. MS SQL Setver 2000). It is easy to find it with your favorite search website for versions higher than 8.0, but this stuff seemed too old to use. No way, some people still use it!
Issues
For example, if one tries to use table variable to store xp_instance_regread result, he gets an error message.
DECLARE @paths TABLE (InstancePath VARCHAR(255))
DECLARE @value_name VARCHAR(500)
DECLARE @regInstanceKey VARCHAR(500)
SET @value_name = 'DefaultData'
INSERT INTO @paths
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',
@vn = @value_name,
@s = @regInstanceKey OUTPUT;
The following error message appears while executing above query:
Msg 197, Level 15, State 1, Line 7
EXECUTE cannot be used as a source when inserting into a table variable.
So the solution could be a temporary table. This suits for all versions of MS SQL Server starting from 8.0
The second issue is SQL Server 2000 stores its instances not like its higher versions. So we need to know which version of SQL Server server installed and which instance we are using at moment.
To get this one could check
'InstalledInstances' key values. This key stores all names of instances installed using space as separator:
DECLARE @regInstanceKey VARCHAR(500)
CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
@value_name = 'InstalledInstances',
@s = @regInstanceKey OUTPUT;
SELECT * FROM #instances
DROP TABLE #instances
We should get the result set of instances in one table after execution the query above:
Name | InstanceName |
InstalledInstances - Item #1 | MSSQLSERVER |
InstalledInstances - Item #2 | SQL2008 |
InstalledInstances - Item #3 | SQL2008R2 |
After that we could ensure that we use the right instance
SELECT @ic = COUNT(*) FROM #instances
WHERE InstanceName = @@servicename
One might ask a question: why to use this instead of using
@@servicename? We just ensure that our SQL Server instances were configured correctly. To check the current instance version is the only thing to be done.
DECLARE @ver VARCHAR(255)
DECLARE @version INT
SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
ELSE SET @version = CAST(@ver AS INT)
SELECT @version
Solution
So we almost there, now we're ready to write the full script for the subject
IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories
CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))
DECLARE @regInstanceKey VARCHAR(500), @s VARCHAR(500);
DECLARE @instance VARCHAR(255) -- current instance
DECLARE @ver VARCHAR(255) -- MS SQL version
DECLARE @key VARCHAR(255) -- Registry key on server side
DECLARE @value_name VARCHAR(255) -- Registry value name
DECLARE @instance_name VARCHAR(255) -- Key value name for the instance of SQL Server 2000
DECLARE @version INT -- SQL Server version (integer)
DECLARE @ic INT
SET @instance_name = ''
INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
@vn = 'InstalledInstances',
@s = @regInstanceKey OUTPUT;
SELECT @ic = COUNT(*) FROM #instances
WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
SELECT @instance = InstanceName FROM #instances
WHERE InstanceName = @@servicename
SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
ELSE SET @version = CAST(@ver AS INT)
IF ( @version = 8) BEGIN
IF (@instance <> 'MSSQLSERVER' ) SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
ELSE SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
END
ELSE IF ( @version > 8) BEGIN
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
@vn = @instance,
@value = @instance_name out
SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
END
SET @value_name = 'DefaultData'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@s = @regInstanceKey OUTPUT;
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
INSERT INTO #directories
VALUES ('DefaultData', @regInstanceKey)
SET @value_name = 'DefaultLog'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@s = @regInstanceKey OUTPUT;
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
INSERT INTO #directories
VALUES ('DefaultLog', @regInstanceKey)
SET @value_name = 'BackupDirectory'
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
@k = @key,
@vn = @value_name,
@s = @regInstanceKey OUTPUT;
INSERT INTO #directories
VALUES ('BackupDirectory', @regInstanceKey)
IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
END
SELECT * FROM #directories
DROP TABLE #instances
DROP TABLE #directories
After executing this we should expect something like the following result:
DefaultData | F:\Bases\Data |
DefaultLog | F:\Bases\Log |
BackupDirectory | F:\Bases\Backup |