Home Forums SQL Server 2012 SQL 2012 - General xp_instance_regread returns blank, but serverproperty returns values for Default directories. RE: xp_instance_regread returns blank, but serverproperty returns values for Default directories.

  • SSMS reads the information from a number of sources and it's difficult to tell which one it uses when the path is not set.

    This is what you get if you trace SSMS while it opens the instance properies window:

    DECLARE @HkeyLocal NVARCHAR(18)

    DECLARE @ServicesRegPath NVARCHAR(34)

    DECLARE @SqlServiceRegPath SYSNAME

    DECLARE @BrowserServiceRegPath SYSNAME

    DECLARE @MSSqlServerRegPath NVARCHAR(31)

    DECLARE @InstanceNamesRegPath NVARCHAR(59)

    DECLARE @InstanceRegPath SYSNAME

    DECLARE @SetupRegPath SYSNAME

    DECLARE @NpRegPath SYSNAME

    DECLARE @TcpRegPath SYSNAME

    DECLARE @RegPathParams SYSNAME

    DECLARE @FilestreamRegPath SYSNAME

    SELECT @HkeyLocal = N'HKEY_LOCAL_MACHINE'

    -- Instance-based paths

    SELECT @MSSqlServerRegPath = N'SOFTWARE\Microsoft\MSSQLServer'

    SELECT @InstanceRegPath = @MSSqlServerRegPath + N'\MSSQLServer'

    SELECT @FilestreamRegPath = @InstanceRegPath + N'\Filestream'

    SELECT @SetupRegPath = @MSSqlServerRegPath + N'\Setup'

    SELECT @RegPathParams = @InstanceRegPath + '\Parameters'

    -- Services

    SELECT @ServicesRegPath = N'SYSTEM\CurrentControlSet\Services'

    SELECT @SqlServiceRegPath = @ServicesRegPath + N'\MSSQLSERVER'

    SELECT @BrowserServiceRegPath = @ServicesRegPath + N'\SQLBrowser'

    -- InstanceId setting

    SELECT @InstanceNamesRegPath = N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

    -- Network settings

    SELECT @NpRegPath = @InstanceRegPath + N'\SuperSocketNetLib\Np'

    SELECT @TcpRegPath = @InstanceRegPath + N'\SuperSocketNetLib\Tcp'

    /* ...SNIP... */

    DECLARE @BackupDirectory NVARCHAR(512)

    IF 1 = isnull(cast(SERVERPROPERTY('IsLocalDB') AS BIT), 0)

    SELECT @BackupDirectory = cast(SERVERPROPERTY('instancedefaultdatapath') AS NVARCHAR(512))

    ELSE

    EXEC master.dbo.xp_instance_regread @HkeyLocal

    ,@InstanceRegPath

    ,N'BackupDirectory'

    ,@BackupDirectory OUTPUT

    DECLARE @InstallSqlDataDir NVARCHAR(512)

    EXEC master.dbo.xp_instance_regread @HkeyLocal

    ,@SetupRegPath

    ,N'SQLDataRoot'

    ,@InstallSqlDataDir OUTPUT

    DECLARE @MasterPath NVARCHAR(512)

    DECLARE @LogPath NVARCHAR(512)

    DECLARE @ErrorLog NVARCHAR(512)

    DECLARE @ErrorLogPath NVARCHAR(512)

    SELECT @MasterPath = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)))

    FROM master.sys.database_files

    WHERE NAME = N'master'

    SELECT @LogPath = substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name)))

    FROM master.sys.database_files

    WHERE NAME = N'mastlog'

    SELECT @ErrorLog = cast(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(512))

    SELECT @ErrorLogPath = substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)))

    DECLARE @SmoRoot NVARCHAR(512)

    EXEC master.dbo.xp_instance_regread @HkeyLocal

    ,@SetupRegPath

    ,N'SQLPath'

    ,@SmoRoot OUTPUT

    /* ...SNIP... */

    DECLARE @InstallSharedDirectory NVARCHAR(512)

    EXEC master.sys.xp_instance_regread @HkeyLocal

    ,@SetupRegPath

    ,N'SQLPath'

    ,@InstallSharedDirectory OUTPUT

    /* ...SNIP... */

    SELECT

    @BackupDirectory AS [BackupDirectory]

    ,ISNULL(@InstallSqlDataDir, N'') AS [InstallDataDirectory]

    ,CAST(@@SERVICENAME AS SYSNAME) AS [ServiceName]

    ,@ErrorLogPath AS [ErrorLogPath]

    ,@SmoRoot AS [RootDirectory]

    ,@LogPath AS [MasterDBLogPath]

    ,@MasterPath AS [MasterDBPath]

    ,SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]

    ,SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog]

    ,ISNULL(@InstallSharedDirectory, N'') AS [InstallSharedDirectory]

    As you can see, when the path is not set, it probably assumes the path to master and mastlog as default data and log location.

    Interestingly enough, the serverporperties appear to be always set, but there's no way to tell how they are calculated. Probably the same logic found in SSMS, or similar. I have always found it to be the same value as in the registry, when set. I have never found a different path between the two, when both set.

    Hope this helps

    Gianluca

    -- Gianluca Sartori