Using Visual Basic with SQL-DMO and SQL-NS PART II

June 25, 2000

Disclaimer:

This code is free for anyone to use. No one may sell it, lease it, rent it, or otherwise charge or collect fees for it. Also by using this code you release me and the people you downloaded this example code from, from any and all legal recourse for anything this code may do to your database or computer systems.

Please refer to PART I for the SQL-DMO code and explanations.

SQL-NS

In this "Part II" segment will look at some basic functions that we can pass different parameters to in order to call special SQL-NS functionality and wizards (just like in EM)

1.      How to get a list of the servers via (frmMenu)

a.       Mod1.SQL_Connector

                                                                           i.      SQL-DMO to get a list of all of the SQL Servers running on the domain you are logged into.

                                                                         ii.      In the following code we create a SQLDMO Application, and a Name List object in which we fill with available SQL Server names running on the same domain.

                                                                        iii.      We then use SQLDMO to get the current version of SQL Server we are connection to, since SQL 7.X SQL-NS will only connect to SQL Server 7.X and above.

                                                                       iv.      We then connect via Trusted or the "sa" password depending on what the user has chosen.

			Public Function SQL_CONNECTOR(objForName As Object)

			Dim iVersion As Integer
				' SQLDMO Connect string
				Set MySqlServer = CreateObject("SQLDMO.SQLServer")

				If bConnected = True Then
				    MySqlServer.Connect ServerName:=sSRVNameDMO, _
				            		    Login:="sa", _
				            		    Password:=sPassword
				Else
				    MySqlServer.LoginSecure = True
				    MySqlServer.Connect ServerName:=sSRVNameDMO
				End If

				' Get the version to make sure that we can connect
				iVersion = MySqlServer.VersionMajor

				' SQLNS Connect
				iVersion = 7
				    If (iVersion >= 7) Then
				        If bConnected = True Then
				            Set objSQLNS = New SQLNamespace
				            objSQLNS.Initialize "SQL Tasks", SQLNSRootType_Server, "Server=" & 
				            sSRVNameDMO & ";UID=sa;pwd=" & sPassword & ";", objForName.hWnd

				            ' get a root object of type Server and walk down the hierarchy from there
				            hArray(0) = objSQLNS.GetRootItem
				        Else
				            Set objSQLNS = New SQLNamespace
				            objSQLNS.Initialize "SQL Tasks", SQLNSRootType_Server, "Server=" & 
				            sSRVNameDMO & ";Trusted_Connection=YES;", objForName.hWnd

				            ' get a root object of type Server and walk down the hierarchy from there
				            hArray(0) = objSQLNS.GetRootItem
				        End If
				    Else
				        MsgBox "You must be running SQL Server 7.X or above for SQL-NS", vbOKOnly, "SQL-NS Error"
				    End If

			End Function    

2.      How to call some basic wizards in SQL Server (frmMaint)

a.       frmMaint.cmdBackup_Click

                                                                           i.      Here is a basic function call ( listed below ) that we can use to call a variety of different wizards.

                                                                         ii.      If you substitute the commented out commands below you will get the appropriate wizards (just like in EM).

			Private Sub cmdBackup_Click()

				Call SQLNSWizard(frmMaint, SQLNS_CmdID_WIZARD_BACKUP)
				' Other Wizard commands ( Sample )
				    ' SQLNS_CmID_WIZARD_DTSIMPORT
				    ' SQLNS_CmID_WIZARD_DTSEXPORT
				    ' SQLNS_CmID_WIZARD_SECURITY
				    ' SQLNS_CmdID_WIZARD_CREATETRACE

			End Sub

b.      frmMaint. SQLNSWizard

                                                                           i.      Here is the bread and butter for a catch all SQL-NS "Wizard Function"

                                                                         ii.      Here we pass in the form name, and the SQL-NS wizard command we want to execute.

                                                                        iii.      We then use the current form and get the SQL-NS Root of the server we are currently connected to.

                                                                       iv.      If everything is ok (our return was not 0) then set the SQL-NS namespace object equal to the wizard we are calling

                                                                         v.      Last but not least we execute by the command ID

			Public Function SQLNSWizard(objForName As Object, sComand As SQLNS.SQLNSCommandID)

			Dim nsObject As SQLNS.SQLNamespaceObject
			Dim lSQLNS(1) As Long

				On Error GoTo SQLNSWizrd_Error

				With objForName

				    .MousePointer = 13
				    ' Call the appropriate wizard you passed in
				    lSQLNS(1) = objSQLNS.GetRootItem
				    If lSQLNS(1) <> 0 Then
				        Set nsObject = objSQLNS.GetSQLNamespaceObject(lSQLNS(1))
				        nsObject.ExecuteCommandByID (sComand)
				    End If
				End With

			' Cleanup
			SQLNSWizrd_Exit:
				objForName.MousePointer = 0
				Exit Function

			' Error routine
			SQLNSWizrd_Error:
				Me.MousePointer = 0
				Resume Next 'Replace with good error handling
				Resume SQLNSWizrd_Exit

			End Function

3.      Some more basic SQL-NS functionality for the SQL Server job scheduler (frmJobs)

a.       frmJobs.cmdErrorlog_Click

                                                                           i.      Once again I am using a "catch all" function to get the SQL Server job scheduler errorlog box (This is not per job, but the Job agent as a whole)

			Private Sub cmdErrorlog_Click()

				Call SQLNS_JOBSERVER("Display Errorlog")
				' Other paramaters you can pass ( Sample )
				    'Call SQLNS_JOBSERVER("Properties")

			End Sub

b.      frmJobs.cmdStart_Click

                                                                           i.      This function merely "Starts" the SQL Scheduler

                                                                         ii.      Please note This code does not check to see if it is currently running.

			Private Sub cmdStart_Click()

				' Check to see if you really want to do this
				If MsgBox("Do you want to start the Scheduler Service?", vbYesNo, "Start Service") = vbYes Then
				    ' Start the serivce
				    Call SQLNS_JOBSERVER("Start Service")
				    MsgBox "Service Started", vbInformation, "Start Service"
				End If

			End Sub

c.       frmJobs.SQLNS_JOBSERVER

                                                                           i.      Here is the "Catch all" function that we can pass different parameters to.

                                                                         ii.      All we have to pass in is the command we want to execute against the SQL JobServer object

                                                                        iii.      First we have to traverse through the SQL-NS root system

                                                                       iv.      To get the first layer we must get the Server / Management layer.

                                                                         v.      Next we must go one more layer deep to the Job Server layer.

                                                                       vi.      Once there we can then Set or SQL-NS namespace object and execute the command

			Private Sub SQLNS_JOBSERVER(sCommand As String)

				' Get first level server->management
				hArray(1) = objSQLNS.GetFirstChildItem(hArray(0), SQLNSOBJECTTYPE_MANAGEMENT)
				' Get second level server->management->Job_Server
				hArray(2) = objSQLNS.GetFirstChildItem(hArray(1), SQLNSOBJECTTYPE_JOBSERVER)

				' Execute namespace
				Dim objSQLNSObj As SQLNamespaceObject
				Set objSQLNSObj = objSQLNS.GetSQLNamespaceObject(hArray(2))

				' Execute command
				objSQLNSObj.Commands(sCommand).Execute

			CleanUp:
				Set objSQLNSObj = Nothing
				Exit Sub
			End Sub

Once again, I hope you find this information educational and learn from it. You can build your own custom applications to do anything and then some that you may rely heavily on EM for.

Download Project

Back to Part 1 of this article