How to find the sqldataroot in the Registry for default and Instances

[sonname]

I am using Installshield to install a database. I'm trying get the path for the sqldataroot. I would like this to work for SQL Server 2000 & 2005 as well as for instance names, but I know that the registry settings are different based on versions. How do you go about getting to this information in a common way? Thanks

 

[mrdenny]

By using the extended stored procedure xp_instance_regread to query the registry and get the information.
Code stolen from Microsoft via SQL Profiler

declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
select @SmoDefaultFile, @SmoDefaultLog

If the values are null then query the master.dbo.sysfiles table and use the folder that the master databases files are in.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com