SQL Server instance

[Ariez]

I would like to list all sqlserver instances on a lan a fill a combo with it.

And diferrenciate betwen a local instance from a remote one..

 

any info would help...

[Robby]

They had a sample of this in the MS 101 VB samples, they currently have a new 101 samples for VB and C# 2003, I'm not sure if it contains this sample. check it out anyway...

http://www.microsoft.com/downloads/details.aspx?FamilyId=08E3D5F8-033D-420B-A3B1-3074505C03F3&displaylang=en

[Ariez]

I'm downloading the file, thanks a lot Robby...

 

(the file is 6.66 MB, a creepy download!)

[Ariez]

I have the old one installed.

Do you happen to remember what project?

[Robby]

hmm, looking through the titles the only one that comes close is "VB.NET - Data Access - How-To Create a Database" , I don't think this one iterates the instances, I may have been mistaken as to where I saw that sample.

 

I'll see if I can find it at home later.

[TomScarr]

Is there any update on this Robby? I am another person who wants to find a way of generating a list of available SQL servers (including named instance servers) from a domain. I don't want to use SQL DMO as that is a pain to deploy, and the only other method I have is using an API call that works, except it does not retrieve all the named instance servers (eg/ UKSQL\UKSQL2000 etc).

 

I have dowloaded the 101 samples but I cannot find anything relevant to that. Most of them just seem to connect to Northwind.

 

Any help would be much appreciated.

 

thanks....Tom

[Ariez]

I've dropped this since I need to move on.

But I thought that looping through the processes for sqlservr.exe would give you local instances of SQL server.

Maybe there's something here if we can list external processes this way.

Just an idea... can't test it now, but if anyone follow through please post it here...

 

many thanks.

[TomScarr]

I've since found this API call that works. I am still slightly surprised that there isn't some way to get this using some managed code:

 

Public Const SQL_HANDLE_DBC As Short = 2

 

Public Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef phenv As Integer) As Short

 

Public Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal hType As Short, ByVal hInput As Integer, ByRef phOutput As Integer) As Short

 

Public Declare Function SQLBrowseConnect Lib "odbc32.dll" (ByVal hDbc As Integer, ByVal szConnStrIn As String, ByVal cbConnStrIn As Short, ByVal szConnStrOut As String, ByVal cbConnStrOutMax As Short, ByRef pcbconnstrout As Short) As Short

 

Public Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Integer) As Short

 

Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Integer) As Short

 

Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal hEnv As Integer) As Short

 

Public Function GetSQLServers() As String

 

Dim retCode As Integer

Dim hDbc As Integer

Dim hEnv As Integer

Dim strCon As String

Dim strOutCon As String

Dim intConLenOut As Short

 

strCon = "DRIVER={SQL Server};"

strOutCon = Space(1000)

 

retCode = SQLAllocEnv(hEnv)

retCode = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, hDbc)

retCode = SQLBrowseConnect(hDbc, strCon, Len(strCon), strOutCon, Len(strOutCon) + 2, intConLenOut)

 

strOutCon = Left(strOutCon, intConLenOut)

 

strOutCon = Mid(strOutCon, InStr(1, strOutCon, "Server={") + 8, InStr(1, strOutCon, "}") - (InStr(1, strOutCon, "Server={") + 8))

 

GetSQLServers = strOutCon

 

retCode = SQLDisconnect(hDbc)

retCode = SQLFreeConnect(hDbc)

retCode = SQLFreeEnv(hEnv)

 

End Function