StarQuest Technical Documents

Automating SQDR tasks using COM (VBScript, Visual Basic, and C#) and SQL Server Stored Procedures

Last Update: 30 January 2012
Product: StarQuest Data Replicator
Version: v3.67 and later
Article ID: SQV00DR0025

Abstract

SQDR tasks can be controlled programmatically, either using SQL Server Stored Procedures or using COM with VBScript, Visual Basic or C#. For instance, the combination of VBScript and the Windows Scheduled Tasks allow you to easily perform SQDR tasks such as running a snapshot subscription or pausing an incremental subscription group on a scheduled basis.

Solution

Scheduling:

SQL Server (SQDR 3.73 and earlier):

SQDR v3.73 and earlier used scheduled invocations of SQL Server stored procedures to perform scheduling of SQDR tasks. This requires that SQL Server be running on the same machine as SQDR, and that the SQL Server Agent service is installed and running. The SQL Server Agent service is available in standard version of SQL Server 2005 and 2008, and in all versions of SQL Server 2000, including MSDE 2000; it is not part of SQL Server Express editions (2005 and 2008). Note that MSDE 2000 is not supported on newer Windows versions like Server 2008, Vista and Windows 7, and that the SQL Server Agent service is installed as part of SQL Server Express 2008 but is not functional.

SQDR v3.75 and later does not use the the SQL Server Agent service for scheduling; instead, all scheduling is handled by the SQDR service itself. The SQDR service must be running for scheduled events to occur; however you can use the Windows Scheduler to start the SQDR service if necessary, either using NET START or using COM (described below) to perform an SQDR operation such as running a replication (which will cause the service to start).

Windows Scheduler:

Use "Add scheduled tasks" of the "Scheduled Tasks" control panel to set a schedule to run a VBScript script or a Visual Basic program. Use the browse button and point at the script or application; you will also need to supply user & password credentials (run as user..) and configure when you want the script to run.

When running on Windows Vista and later, you may need to specify the task as "run with the highest privilege".

Using Stored Procedures:

The topic Running Replications Outside Replicator Manager (under Running Subscriptions and Groups) in the SQDR Manager Help addresses running the agent jobs and calling SQDR subs via stored procedures.

Here is an example of calling the stored procedure to run a subscription:

EXEC msdb.dbo.sp_start_job 'SQDR:<source>/<sub_name>'

Here is an example of running a group:

EXEC msdb.dbo.sp_start_job 'SQDR:/<group_name>'

Using COM and VBScript

In the examples below, substitute your own Source and Subscription names for MYSOURCE & MYSUBSCRIPTION. Unlike Visual Basic and C#, you need to know the names & parameters of the methods when using VBScript.

If you encounter the error "Error 80070005: Access is denied" on Windows Vista and later, you may need to run the application with elevated privileges. Either start run the VBScript script from a command prompt which has been started with "Run as Administrator", use a stub or wrapper script which runs the main VBScript elevated using the runas verb, or write the script so that it re-launches itself as administrator (elevated) if the script has no command-line arguments passed (see VBScript Example 3).

Two additional VBScript examples are installed with SQDR: EnableKeyLog.vbs & PauseResumeIRGroup.vbs.

VBScript Example 1: Running a subscription

Copy the following text into a file called runsub.vbs, and then execute "runsub.vbs":

set sqdrsvc = WScript.CreateObject("SQDRSVC.SQDataReplicator")
err = sqdrsvc.StartReplication("MYSOURCE", "MYSUBSCRIPTION")

Or if you prefer to pass in the Source and Subscription names as command line arguments, use this text:

' usage = "RunReplication.vbs <source_name> <subscription_name>"
set args = WScript.Arguments
set sqdrsvc = WScript.CreateObject("SQDRSVC.SQDataReplicator")
err = sqdrsvc.StartReplication(args.Item(0), args.Item(1))

VBScript Example 2:

This example checks whether a subscription is running, and calls either GetReplicationStatus() to display the statistics for the current active run or GetSubscriptionStatistics() to display the statistics for previous runs.

' This script must be run from Cscript since it uses WScript.Stdout
If LCase(Right(WScript.FullName, 11)) <> "cscript.exe" Then
  WScript.Echo _
  "Issue the following command to run this script from" & VbCrLf _
  & "the command prompt:" & VbCrLf _& VbCrLf _
  & "Cscript /nologo status.vbs"
  WScript.Quit
End If

set sqdrsvc = WScript.CreateObject("SQDRSVC.SQDataReplicator")
result = sqdrsvc.ReplicationIsRunning("MYSOURCE", "MYSUBSCRIPTION")

If result = 1 Then
  WScript.Echo("Subscription is running")
  WScript.StdOut.WriteLine("Statistics for current run:")
  set rsStatistics = sqdrsvc.GetReplicationStatus("MYSOURCE", "MYSUBSCRIPTION")
Else
  WScript.Echo("Subscription is not running")
  WScript.StdOut.WriteLine("Statistics for previous runs:")
  set rsStatistics = sqdrsvc.GetSubscriptionStatistics("MYSOURCE", "MYSUBSCRIPTION")
End If

' Display the recordset

Do While Not rsStatistics.Eof
  For Each f In (rsStatistics.Fields)
    WScript.StdOut.WriteLine("info " & f.Name & " = " & f.Value)
  Next
  rsStatistics.MoveNext()
Loop

VBScript Example 3:

This example enumerates the members of a group. It also handles UAC on Vista and later by re-launching itself as administrator (elevated). It uses cscript since it uses WScript.Stdout.

If WScript.Arguments.length =0 Then

Set objShell = CreateObject( "Shell.Application")
'Pass a bogus argument with leading blank space, say [ uac]
objShell.ShellExecute "cscript.exe", Chr(34) & _
WScript.ScriptFullName & Chr(34) & " uac", "", "runas", 1

Else
'Actual code here

set sqdrsvc = WScript.CreateObject("SQDRSVC.SQDataReplicator")
set results = sqdrsvc.EnumerateGroupMembers ( "MYGROUP")

Do While Not results.Eof

For Each f In (results.Fields)
WScript.StdOut.WriteLine("info " & f.Name & " = " & f.Value)
Next
results.MoveNext()

Loop

End If

 

Using COM and Visual Basic

The typelib information included in sqdrsvc.exe can be used to call the SQDR service from any automation-enabled
environment such as Visual Basic and C#.

In Visual Basic, you can view the available methods and properties provided by SQDR using the IntelliSense (autocompletion) feature of the Visual Studio editor.

1. Use the Project/Add Reference menu item and select "SQDRSVC 1.0 Type Library" to register the type library.
2. Enter:
   Dim sqdr As New SQDRSVCLib.SQDataReplicator
3. Enter
   sqdr.
   A dropdown list of available methods and properties will appear when you type the period.

Visual Basic Example 1:

1. Launch Visual Studio (VS.NET 2003 or later)
2. Use menus: File... New... Project..., select Visual Basic Projects, Console Application
   The initial source code for a new console app will be created.
3. Use menus: Project... Add Reference...Select COM tab, scroll down in the listbox to "SQDRSVC 1.0 Type Library", hit the "Select" button, then "Ok".
4. Between "Sub Main()" and "End Sub", put these two lines:
   Dim sqdr As New SQDRSVCLib.SQDataReplicator
   sqdr.StartReplication("MYSOURCE", "MYSUBSCRIPTION")
5. Hit F5 to build and run the application.
   

Visual Basic Example 2:

1. Use Menus: Project... Add Reference... "adodb" ... "Select"... "Ok"
2. Paste the following as your complete Console Application source:

Module Module1

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Main()

Dim sqdr As New SQDRSVCLib.SQDataReplicator
Dim rsStatus As ADODB.Recordset
Dim rsStatistics As ADODB.Recordset
Dim f As ADODB.Field

sqdr.ResetSubscriptionStatistics("MYSOURCE", "MYSUBSCRIPTION")

sqdr.StartReplication("MYSOURCE", "MYSUBSCRIPTION")

rsStatus = sqdr.GetReplicationStatus("MYSOURCE", "MYSUBSCRIPTION")

While Not rsStatus Is Nothing

Console.WriteLine("Running, " &
rsStatus.Fields.Item("Bytes").Value & " bytes reported...")
Sleep(1000)
rsStatus = sqdr.GetReplicationStatus("MYSOURCE", "MYSUBSCRIPTION")

End While

rsStatistics = sqdr.GetSubscriptionStatistics("MYSOURCE", "MYSUBSCRIPTION")

Do While Not rsStatistics.EOF

For Each f In (rsStatistics.Fields)
Console.WriteLine("info " & f.Name & " = " & f.Value)
Next
rsStatistics.MoveNext()

Loop
MsgBox("All done!")

End Sub

End Module

3. Hit F5 to build and run the application.

Using COM and C#

When working with C# , you can view the available methods and properties provided by SQDR using the IntelliSense (autocompletion) feature of the Visual Studio editor.

1. Use the Project/Add Reference menu item and using the COM tab, select "SQDRSVC 1.0 Type Library" to register the type library. You can also use the Browse tab, or right-click on the References of the project and select Add References, and browse to the installed location of SQDR and select sqdrsvc.exe.
2. Enter:
   SQDRSVCLib.SQDataReplicator sqdr = new SQDRSVCLib.SQDataReplicator();
3. Enter
   sqdr.
   A dropdown list of available methods and properties will appear when you type the period.

C# Example 1:

1. Launch Visual Studio (VS.NET 2003 or later)
2. Use menus: File... New... Project..., select Visual C#/Windows/Console Application
   The initial source code for a new console app will be created.
3. Use menus: Project... Add Reference...Select COM tab, scroll down in the listbox to "SQDRSVC 1.0 Type Library", hit the "Select" button, then "Ok". SQDRSVCLib should now appear under References.
4. Add these two lines to the main() routine:
   SQDRSVCLib.SQDataReplicator sqdr = new SQDRSVCLib.SQDataReplicator();
   sqdr.StartReplication("MYSOURCE", "MYSUBSCRIPTION");
5. Select Build Solution from the Build menu to build the application. Select Publish from the Build menu to deploy the application to another machine.

 

REFERENCE:

This a list of the methods most likely to be of interest to end users and is not complete. Contact StarQuest support if you
need to automate a task not listed here. This information is subject to change in future in future versions of SQDR. Note that the sourceName is case sensitive.

StartReplication (sourceName, subscriptionName)
StartReplication ("", groupName) - run a snapshot group
StopReplication (sourceName, subscriptionName)

EnableIncrementalPolling (groupName, 0) - stop polling
EnableIncrementalPolling (groupName, 1) - resume polling

ReplicationIsRunning (sourceName, subscriptionName)
returns 1 (true) or 0 (false)

ResetGroupStatistics (groupName)
ResetSubscriptionStatistics (sourceName, subscriptionName)

The following methods return a recordset:

GetSubscriptionStatistics (sourceName, subscriptionName)

GetReplicationStatus (sourceName, subscriptionName)
This returns data only when the subscription is running.

GetGroupStatistics (groupName)
GetGroupMemberStatistics (groupName, sourceName, subscriptionName)

EnumerateSubscriptions (sourceName)
EnumerateGroups ()
EnumerateGroupMembers (groupName)

Key Logging Functions:

See EnableKeyLog.vbs (installed as part of SQDR) and the technical note Using SQDR Key Logging Data for more information on using the Key Logging functions.

EnableKeyLog (groupName, subscriptionName, level)
where level is 0 (off), 1 (high), or 2 (low)

GetKeyLogEnabled(groupName, subscriptionName)
- returns a recordset

 

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2012 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.