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.
Back to Part 1 of this article