Welcome to another blog post on SQL Server! In this article, we will be exploring the functionality of SQL-NS (SQL Server Notification Services) and how it can enhance your experience with SQL Server.

1. Getting a List of Servers

One of the first things we can do with SQL-NS is to get a list of all the SQL Servers running on the domain we are logged into. This can be achieved by using the SQL-DMO code. Here is an example:

Dim iVersion As Integer
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

iVersion = MySqlServer.VersionMajor

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
        hArray(0) = objSQLNS.GetRootItem
    Else
        Set objSQLNS = New SQLNamespace
        objSQLNS.Initialize "SQL Tasks", SQLNSRootType_Server, "Server=" & 
        sSRVNameDMO & ";Trusted_Connection=YES;", objForName.hWnd
        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

2. Calling Basic Wizards in SQL Server

SQL-NS allows us to call various wizards just like in Enterprise Manager (EM). Here is an example of how to call the backup wizard:

Private Sub cmdBackup_Click()
    Call SQLNSWizard(frmMaint, SQLNS_CmdID_WIZARD_BACKUP)
End Sub

You can substitute the commented out commands to call other wizards such as DTS Import, DTS Export, Security, and Create Trace.

3. SQL Server Job Scheduler Functionality

SQL-NS also provides functionality for the SQL Server job scheduler. Here are a couple of examples:

To display the error log for the job scheduler:

Private Sub cmdErrorlog_Click()
    Call SQLNS_JOBSERVER("Display Errorlog")
End Sub

To start the SQL Scheduler:

Private Sub cmdStart_Click()
    If MsgBox("Do you want to start the Scheduler Service?", vbYesNo, "Start Service") = vbYes Then
        Call SQLNS_JOBSERVER("Start Service")
        MsgBox "Service Started", vbInformation, "Start Service"
    End If
End Sub

These are just a few examples of the functionality that SQL-NS provides for the SQL Server job scheduler.

By exploring SQL-NS, you can build custom applications that can perform various tasks and provide a more efficient way of managing your SQL Server environment.

Thank you for reading this article. I hope you found it informative and that it helps you in your SQL Server journey!