Obtaining Instance Name

Question

Post reply

Obtaining Instance Name

Christopher Stephen

Grasshopper

Points: 14
More actions
November 18, 2005 at 2:16 am

#67654

Hi all,
Is it possible to get the name of the instance of SQL server that is running on a machine when it is not (local)?
ie. there is only one instance installed and it is named eg "NameOfMachine\SomeInstanceName".

Using "local" or "NameOfMachine" in the connection string does not find it.
Cheers,Chris.

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

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

David.Poole SSC Guru Points: 76140 More actions · Answer 1

I don't have BOL to hand but I seem to remember that you can do something like SELECT @@SERVERNAME

LinkedIn Profile

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

See SERVERPROPERTY in Books OnLine

select SERVERPROPERTY ('MachineName')

,SERVERPROPERTY ('InstanceName')

,SERVERPROPERTY ('ServerName')

Note that SERVERPROPERTY ('InstanceName') is NULL when this is the default instance.

SQL = Scarcely Qualifies as a Language

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 3

Do you know what Network Libraries the server is listening on? Is it a full-blown SQL Server or an MSDE install?

Also, you can tell the instance name by examining the registry (HKLM\System\CurrentControlSet\Services\MSSQL$<Instance Name> ). The same is true of the file system if default paths were set: C:\Program Files\Microsoft SQL Server\MSSQL$<Instance Name>.

K. Brian Kelley
@kbriankelley

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

If this is a one-time and manual process, open a command window and enter

isql -L

If this is on-going in a program, you can use SQL DMO:

Dim namX As NameList

Set namX = oSQLServerDMOApp.ListAvailableSQLServers

There is sample source for Visual Basic located at \devtools\samples\sqldmo\vb\Login

SQL = Scarcely Qualifies as a Language

Christopher Stephen Grasshopper Points: 14 More actions · Answer 5

Thanks for your suggestions.

The method we decided on implementing was calling the GetSQLServerInstanceNames method from the SQL DMO library.

Cheers,

Chris.

Obtaining Instance Name

Cookies on SQLServerCentral