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

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles