StarQuest Technical Documents

Automating SQDR tasks using COM (VBScript, Visual Basic, and C#)

Last Update: 11 April 2019
Product: StarQuest Data Replicator
Version: v4.00 and later
Article ID: SQV00DR0025

Abstract

SQDR tasks can be controlled programmatically, either 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:

SQDR-managed Scheduled Tasks:

Scheduled tasks that are configured within SQDR are 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 or C# 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.

You may need to specify the task as "run with the highest privilege".

Remote Servers

You can perform operations on remote SQDR servers if the following prerequisites are met:

  • COM registration for SQDR has been defined in the registry. The can be accomplished by installing the SQDR Data Replicator Manager application.
  • You must have network connectivity to the remote system.
  • If the Windows Firewall is enabled on the remote system, create an inbound rule to allow access to the SQDR service application (C:\Program Files\StarQuest\SQDR\sqdrsvc.exe) and enable the inbound rule for COM+ Network Access (DCOM-In)..
  • Run the application as a domain user that is a member of the local Administrators group on the remote system.

See below for examples of specifying a remote system 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", you may need to run the application with elevated privileges. Either 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 4).

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 - Running a subscription on a remote server

The following VBScript will run a subscription on a remote server named myserver. Note that it uses the VBScript version of CreateObject(), in which the optional second argument indicates a server name, rather than the WScript version of CreateObject(), in which the second argument indicates a subroutine prefix.

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

VBScript Example 3 - Obtain status & statistics

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 4 - Enumerate group members; handle UAC

This example enumerates the members of a group. It also handles UAC (User Access Control) 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#. Install either the SQDR Data Replicator Manager installer package or the full SQDR installer on the development machine to register the typelib information.

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 - Running a subscription

  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 - Running a subscription on a remote server

Follow the steps in Example 1, but place this code between "Sub Main()" and "End Sub"

Dim sqdr As SQDRSVCLib.SQDataReplicator
On Error GoTo err1
sqdr = CreateObject("SQDRSVC.SQDataReplicator", "myserver")
sqdr.StartReplication("MYSOURCE", "MYSUBSCRIPTION")
Exit Sub
err1:
MsgBox("Connection failed: Error " & Err.Number & " - " & Err.Description)

Visual Basic Example 3 - Obtain status & statistics

  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

  1. 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 or sqdrsvc.tlb.
  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 - Run a subscription

  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.

C# Example 2 - Run a subscription on a remote server

  1. Follow the steps in Example 1.
  2. Add the statement using SQDRSVCLib; to the head of the code.
  3. Place this code in the main() routine:

ISQDataReplicator sqdr = null;
Type tServerType = System.Type.GetTypeFromProgID("SQDRSVC.SQDataReplicator", "myserver", true);
if (tServerType == null) { throw new Exception("Could not find SQDR Service!"); }
sqdr = (ISQDataReplicator)Activator.CreateInstance(tServerType);
sqdr.StartReplication("MYSOURCE", "MYSUBSCRIPTION");

REFERENCE:

This is 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 versions of SQDR. Note that the sourceName is case sensitive.

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

EnableIncrementalPolling (groupName, n)
SourcePoll (sourceName, n)
TargetPoll (destName, n)
(specify n=0 to stop polling; n=1 to resume polling)

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

ResetGroupStatistics (groupName)
ResetSubscriptionStatistics (sourceName, subscriptionName)

SourceReplay (sourceName, newPosition, IsTX)
DestReplay (destName, newPosition, IsTX)
GroupReplay (groupName, newPosition, IsTX)
ReplayGroupMember (groupName, sourceName, subscriptionName, newPosition, IsTX)

MoveSubscription(sourceName,subscriptionName,toGroupName)

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)

SQLQualifiers (SHORT srcDestType, BSTR srcDestName)
SQLSchemas (SHORT srcDestType, BSTR srcDestName.BSTR catalogName)

GetSubscription (sourceName, subscriptionName) - see this example program
SetSubscription()

The following methods return a string, a long integer, or a BOOLEAN value:

GetStringProperty(propertyName)
GetInt32Property(propertyName)
GetBooleanProperty (propertyName)

For example, GetStringProperty("buildVersion") returns the version of the service as a string. Use the SQDR Service Properties application to view all available service properties. Most of the properties are related to configuration and are typically viewed and modified using Data Replicator Manager or SQDR Configuration, with the exception of the Advanced Settings documented in the Appendix of the SQDR Help file.

Additional examples included in SQDR distribution

Several additional VBScript examples are installed with SQDR:

TargetChecker:

Use these methods to perform table validation (comparing row counts between the source and destination tables of an incremental group). See TargetChecker.vbs (installed with the product) and the Tools chapter of Data Replicator Manager help (drmgr.chm) for more information. You can use SQL wildcard characters for the group and subscription parameters; If you do not supply any parameters, the SQL wildcard character % is used for both group and subscription.

TargetChecker (groupName, subscriptionName)

GetTargetCheckResults (groupName, subscriptionName)
- returns a recordset

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

 


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.