Wiki Home

Sql Dmo


Namespace: WIN_COM_API
SQL-DMO is being officially depricated in SQL Server 2005. It will still ship, but will no longer be updated nor will it support all the new features. It is being replaced with SQL-SMO, which is a .NET-based API.

SQL-DMO is the SQL Server Distributed Management Objects collection. It allows you to manage a SQL server as an object in your program.

For an example of using these (and the SQLNamespace ojects) see http://www.civilsolutions.com.au/publications.htm and have a look at the Managing SQL Server/MSDE from VFP—Parts 1-3. -- Andrew Coates
working on a VFP example:
Local ;
	loServer as SQLDMO.SQLServer, ;
	loUser as SQLDMO.User, ;
	loQueryResults as SQLDMO.QueryResults
	
loServer = CreateObject("SQLDMO.SQLServer")
loServer.Login = "sa"
loServer.Password = "abc"
loServer.Connect("V333w98b")

lcSqlScript = FileToStr( "util\mkdb.sql" )
lcRetMessage = Space(200)
loQueryResults = loServer.ExecuteWithResultsAndMessages(lcSqlScript,,lcRetMessage )




This would be the single add loggin way ...

LOCAL oserver as SQLDMO.SQLServer
LOCAL ologin as SQLDMO.login

oserver = CREATEOBJECT("SQLDMO.SQLServer")
oserver.Login = "sa"
oserver.Password ='saPassword'
oserver.connect("OBELIX\MDVSQL")

oLogin = CreateObject("SQLDMO.Login")
oLogin.Name = "Bob"
ologin.Type = 2
oLogin.SetPassword("","password")
oServer.Logins.Add(oLogin)

RELEASE oserver, ologin


Check to see if the server is running (VB code, needs to be converted to VFP.)
Anyone have the code to start the server?
Private Sub StartSQL()
    Const k_ErrSQLServerRunning = -2147023840

    Dim lErr As Long, sErr As String
    Dim sServerName As String
    Dim sUser As String
    Dim sPWD As String

    Dim oSqlServer As SQLDMO.SQLServer
    Set oSqlServer = New SQLDMO.SQLServer

    On Local Error Resume Next
    oSqlServer.Start True, sServerName, sUser, sPWD
    lErr = Err.Number
    sErr = Err.Description
    On Local Error GoTo 0


    If lErr <> 0 Then
        If lErr <> k_ErrSQLServerRunning Then
            Err.Raise lErr, Description:=sErr
        Else
            oSqlServer.Connect sServerName, sUser, sPWD
        End If
    End If

End Sub

Translated to VFP:
PROCEDURE StartSQL
    LPARAMETERS tlStartMode AS Boolean ,;
        tcServerName AS STRING ,;
        tcUserName AS STRING,;
        tcPassword AS STRING

    #DEFINE CRLF CHR(13)+CHR(10)
    TRY

        LOCAL loSQLDMO AS SQLDMO.SQLServer
        LOCAL loError AS EXCEPTION
        loSQLDMO = CREATEOBJECT("SQLDMO.SQLServer")

        loSQLDMO.Start(tlStartMode,tcServerName,tcUserName,tcPassword)

    CATCH TO loError

        MESSAGEBOX("Unable to Start MS-SQLServer"+;
            "Extended Error:"+loError.MESSAGE +CRLF +;
            "Error Number:"+Transform(loError.ErrorNo),16,"Error")

    FINALLY
        loSQLDMO = NULL
        RELEASE loSQLDMO
    ENDTRY
ENDPROC


Esparta Palma
Category Code Samples Category Data
( Topic last updated: 2004.11.28 11:59:48 AM )