SQL DMO in VB.Net

SQL DMO in VB.Net

Post by Kris Szembe » Sat, 07 Dec 2002 11:56:02



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.

 
 
 

SQL DMO in VB.Net

Post by Greg Obleshchu » Tue, 10 Dec 2002 10:57:53


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.


 
 
 

1. SQL-DMO and VB.NET.

I am trying to check the to see when a database was last restored on one of
my SQL Servers' using SQL-DMO.

All the references point to the backup device and getting the query results
for its dataset. That is fine if you want to know when the database as last
backed up or when the last Log File backup was executed but, it does nothing
when trying to see when a database was last backed up.

If someone could point me in the right direction it would very helpful.

Thanks

2. DTS Error - ActiveX component can't create object

3. VB.Net SQL-DMO EnumUsers problem

4. Good UNBOUND Data Grid Controls

5. VB.NET DMO

6. Schema & XML

7. DMO Backup Events in VB .NET

8. 4GL Newbie Question

9. Newbie needs code pages for SQL Server 2000 access from asp.net page using vb.net

10. US-GA-.NET DEVELOPER WITH ORACLE FORMS VB.NET+ASP.NET

11. SQL DMO in .NET

12. SQL Server DMO Backup Object Events Not Firing Correctly with Visual Basic .NET

13. SQL DMO in .NET