Path for sqlservr.exe - Query?

Question

Path for sqlservr.exe - Query?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
January 6, 2011 at 4:07 pm

#232738

Is there a way i can find the actual path of sqlservr.exe? Any system table?.I could use command shell but i do not how to findout if it is installed on other drive than C.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

the server executable itself is always installed on the same disk the %SYSTEMDRIVE% directory exists; databases, traces ,previous backups you can discover form some of the system views, but all those items can have been moved to disks other than the OS drive;

what is it you are trying to find?

here's a decent list of all the windows command line variables:

http://www.google.com/url?sa=t&source=web&cd=4&ved=0CDYQFjAD&url=http%3A%2F%2Fvlaurie.com%2Fcomputers2%2FArticles%2Fenvironment.htm&rct=j&q=windows%20cmd%20variavles&ei=4lMmTdTOG4H88Aa_tZWvAQ&usg=AFQjCNG1Je4Y-1NkvZtV7stgKOl-rBKPPA&sig2=Dg9jehSzQkG-LSPhezUlXA&cad=rja

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adiga One Orange Chip Points: 27284 More actions · Answer 2

@sqldba_icon,

You can also query the SQLBinRoot key in the registry. It is located in the following key for SQL Server 2008 default instance. You may navigate to the Setup key for SQL Server 2005 in registry.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

I understand where the binaries are located. Basically i have 50 servers and i do not want to physically log into services console of each of these servers to find where the sqlserver.exe is located. I just wanted to pass a dos command through xp_cmdshell usign 2008 client which will should tell me which drive is the executable placed. How do i do that now? Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Pls Check this ,

Declare @path nvarchar(100)

Declare @instance_name nvarchar(100)

Declare @instance_name1 nvarchar(100)

Declare @system_instance_name nvarchar(100)

Declare @key nvarchar(1000)

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

If @instance_name!='MSSQLSERVER'

Set @instance_name=@instance_name

Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

If @instance_name1!='MSSQLSERVER'

Set @instance_name1='MSSQL$'+@instance_name1

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@path OUTPUT

Select @path as Binaries_Path

Thank You,

Best Regards,

SQLBuddy

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

sqlbuddy123 (1/7/2011)
Pls Check this ,
Declare @path nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @instance_name1 nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @key nvarchar(1000)
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name
Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@path OUTPUT
Select @path as Binaries_Path
Thank You,
Best Regards,
SQLBuddy

This is exactttttttttly what i was looking for. Awesome. Thank you so muchhhhhhhh

S. Kusen SSChampion Points: 10936 More actions · Answer 6

sqlbuddy123 (1/7/2011)
Pls Check this ,
Declare @path nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @instance_name1 nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @key nvarchar(1000)
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name
Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@path OUTPUT
Select @path as Binaries_Path
Thank You,
Best Regards,
SQLBuddy

Great script. Resurrecting this thread from 2011 to say that if you wind up getting a NULL for the @path variable, extend it out to a nvarchar(200). We had a long named instance of SQL, and it went beyond the 100 character limit for that key value, and as a result, it returned null rather than a truncated string.

Thought that might help someone down the road!

Cheers,

Steve 😀