Kris,
This works for me no problem.
SQLDMO is pretty slow in DotNet. I use it a bit and trying alternatives to
it now. If you need a SQL server listing module try this
Module Module1
' RETCODEs
Private Const SQL_ERROR As Integer = -1
Private Const SQL_INVALID_HANDLE As Integer = -2
Private Const SQL_NEED_DATA As Integer = 99
Private Const SQL_NO_DATA_FOUND As Integer = 100
Private Const SQL_SUCCESS As Integer = 0
Private Const SQL_SUCCESS_WITH_INFO As Integer = 1
' SQLError defines
Private Const SQL_NULL_HENV As Integer = 0
Private Const SQL_NULL_HDBC As Integer = 0
Private Const SQL_NULL_HSTMT As Integer = 0
Private Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv As
Integer, ByRef phdbc As Integer) As Short
Private Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef phenv As
Integer) As Short
Private 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
Private Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc As
Integer) As Short
Private Declare Function SQLError Lib "odbc32.dll" (ByVal henv As
Integer, ByVal hdbc As Integer, ByVal hstmt As Integer, ByVal szSqlState As
String, ByRef pfNativeError As Integer, ByVal szErrorMsg As String, ByVal
cbErrorMsgMax As Short, ByRef pcbErrorMsg As Short) As Short
Private Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc As
Integer) As Short
Private Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv As
Integer) As Short
Public Function StServerList() As String()
Dim rc As Short
Dim henv As Integer
Dim hdbc As Integer
Dim stCon As String
Dim stConOut As String
Dim pcbConOut As Short
Dim ichBegin As Short
Dim ichEnd As Short
Dim stOut As String
Const COMMA As String = ","
rc = SQLAllocEnv(henv)
rc = SQLAllocConnect(henv, hdbc)
stCon = "DRIVER=SQL Server"
' Get the size of the buffer to create and create it
rc = SQLBrowseConnect(hdbc, stCon, Len(stCon), stConOut,
Len(stConOut) + 2, pcbConOut)
stConOut = New String(vbNullChar, pcbConOut + 2)
' Get the actual server list
rc = SQLBrowseConnect(hdbc, stCon, Len(stCon), stConOut,
Len(stConOut) + 2, pcbConOut)
If (rc <> SQL_SUCCESS) And (rc <> SQL_NEED_DATA) Then
' ignore the errors if any occur
Else
' Parse out the server list
ichBegin = InStr(InStr(1, stConOut, "server=",
CompareMethod.Text), stConOut, "{", CompareMethod.Text)
stOut = Mid(stConOut, ichBegin + 1)
ichEnd = InStr(1, stOut, "}", CompareMethod.Binary)
StServerList = Split(Left(stOut, ichEnd - 1), ",")
End If
' Disconnect, free the connection handle, then
' free the environment handle.
rc = SQLDisconnect(hdbc)
rc = SQLFreeConnect(hdbc)
rc = SQLFreeEnv(henv)
End Function
End Module
--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
version 2.2, the best just got better
AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp
Quote:> I am trying to use DMO for SQL Server 2000 in the VB.NET
> application. The following code works fine in VB6. In
> VB.NET it gives me the exception:
> An unhandled exception of
> type 'System.InvalidCastException' occurred in
> WindowsApplication1.exe
> Additional information: QueryInterface for interface
> SQLDMO.NameList failed.
> while executing the last statement below:
> Private oSQLServerDMOApp As SQLDMO.Application
> Public WithEvents oSQLServer As SQLDMO.SQLServer
> Public goSQLServer As SQLDMO.SQLServer
> Public namX As SQLDMO.NameList
> Public gShowServerEvents As Boolean
> oSQLServerDMOApp = New SQLDMO.Application()
> gShowServerEvents = False
> namX = oSQLServerDMOApp.ListAvailableSQLServers()
> Has anybody had (found a solution to) similar problem.
> Any suggestions very appreciated.
> Thanks Kris.
by