StarQuest Technical Documents

Automating SQDR 5.x tasks using COM (VBScript, PowerShell, Visual Basic, and C#)

Last Update: 27 January 2021
Product: StarQuest Data Replicator
Version: v5.x & earlier
Article ID: SQV00DR025

Abstract

SQDR tasks can be controlled programmatically, either using COM with VBScript, PowerShell, 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.

This document refers to the COM-based programming support in SQDR v5.x and earlier. For information about the PowerShell support in SQDR v6.x and later, refer to Automating SQDR tasks using PowerShell.

Solution

• Scheduling
• Remote Servers
• Using COM and VBScript
• Using COM and PowerShell
• Using COM and Visual Basic
• Using COM and C#
• Reference - list of commonly-used methods
• Additional examples included in SQDR distribution

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. In the Actions panel, use the browse button and point at the script or application.

In the case of a PowerShell script, specify powershell as the application and -F <full path to script file> (e.g. -F C:\Scripts\myscript.ps1) for arguments.

You will also need to supply user & password credentials (run as user..) and configure when you want the script to run. Typically you can use SYSTEM (Local System Account) as the user; no password is required in that case.

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

To capture output when running as a scheduled task, you can wrap the application or script in a command file which redirects the output and instruct Scheduled Tasks to use that script. For example

MySQDRscript.cmd:

@echo Started: %date% %time% >> C:\scripts\output.log
PowerShell -noninteractive -NoLogo C:\scripts\MySQDRscript.ps1 2>&1 >> C:\scripts\output.log
@echo Ended: %date% %time% >> C:\scripts\output.log

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

VBScript Example 5 - Export one or all subscriptions for a source to JSON

' VBScript to export subscription in json format

usage = "cscript ExportSubscription.vbs [source_name, sub_name(use % for all)]" & VbCrLf & VBTab & "(use doublequotes for source or subscription names withspace chars) "
outputString = ""
finalString = FormatDateTime(Now) & VbCrLf

' 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 " & usage
  WScript.Quit
End If

On Error Resume Next

Sub OnUsage
  WScript.StdOut.WriteLine("Usage: " & usage)
  WScript.Quit
End Sub

Set args = WScript.Arguments
Select Case args.Count

Case 2
' Service COM interface
  Set sqdrsvc =     WScript.CreateObject("SQDRSVC.SQDataReplicator")
  If Err.Number <> 0 Then
    WScript.Echo "Error " & Hex(Err.Number) & ": " &     Err.Description
    WScript.Quit
  End If

  If args.Item(1) = "%" Then
    WScript.Echo "Retrieving all subscriptions for source " & args.Item(0) & VbCrLf
  ' Get all subscriptions
  Set rs = sqdrsvc.EnumerateSubscriptions(args.Item(0))

  Do While Not rs.EOF
    subscription = rs.Fields("SubscriptionName")
    outputString = subscription & VbCrLf &     sqdrsvc.ExportSubscription(args.Item(0),subscription)
    finalString = finalString & VbCrLf & outputString & VbCrLf
    rs.MoveNext
  Loop
  Else
    outputString =     sqdrsvc.ExportSubscription(args.Item(0),args.Item(1))
    finalString = finalString & VbCrLf & outputString
  End If

Case Else
  OnUsage()
End Select

If Err.Number <> 0 Then
  WScript.Echo "Error " & Hex(Err.Number) & ": " &   Err.Description
  Err.Clear
Else
  WScript.Echo finalString
End If

Using COM and PowerShell

PowerShell Example 1 - Running a subscription

Copy the following text into a file called runsub.ps1, and then execute powershell -F runsub.ps1, or open a PowerShell command window and enter .\runsub.

$sqdrsvc = New-Object -ComObject "SQDRSVC.SQDataReplicator"
$sqdrsvc.StartReplication("MYSOURCE", "MYSUBSCRIPTION")

PowerShell Example 2 - Exporting Service Properties

$sqdrsvc = New-Object -ComObject "SQDRSVC.SQDataReplicator"
$Cmd='{"command": "exportprops","parameters": null}'
$sqdrsvc.Service($Cmd)

PowerShell Example 3 - Exporting and Importing the subscriptions of an Incremental Group

Here is an example of using PowerShell scripts and the Service method to export the subscriptions to a JSON file, and then using the JSON file as input to import the same subscriptions.

# ExportSubs.ps1
# Usage: PS C:\temp> .\ExportSubs MY_IRGROUP
# This will create a JSON file called MY_IRGROUP.json

param([string]$GroupName, $Path=".\subs.json")
$sqdrsvc = New-Object -ComObject "SQDRSVC.SQDataReplicator"
$Cmd='{"command": "exportsubs","parameters": {"GroupName": "' + $GroupName + '"}}'
$Reply=""
$sqdrsvc.Service($Cmd) | Set-Content -Path $Path
Write-Host $GroupName subscriptions written to $Path

# ImportSubs.ps1
# Usage: PS C:\temp> .\ImportSubs MY_IRGROUP
# note that well formed JSON encloses the subscription in brackets,
# but we need to trim the trailing (invisible) LF, CR and remove the enclosing brackets before providing to “Service” –

param([string]$GroupName, [string]$Path=".\subs.json")
$Subs = (Get-Content -Path $Path -Raw).TrimEnd("`r`n")

$sqdrsvc = New-Object -ComObject "SQDRSVC.SQDataReplicator"
$Cmd='{"command": "importsubs","parameters": {"toGroupName": "' + $GroupName + '", "validate":false,' + $Subs.Trim("{}") + '}}'
$Reply=""

$Reply = $sqdrsvc.Service($Cmd)
Write-Host $Reply

This function is useful for automating the creation of multiple incremental groups that differ slightly - i.e. create the first group and its subscriptions, export to a JSON file, modify the JSON file (e.g. using sed or the PowerShell example below to change a string), and then import the modified JSON file.

PowerShell example of changing a string:

(Get-Content c:\temp\original-file.json).replace('original-value', 'new-value') | Set-Content c:\temp\new-file.json

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

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 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 or an I/R group
StopReplication (sourceName, subscriptionName)

EnableIncrementalPolling (groupName, n) - Pause/Resume I/R group
SourcePoll (sourceName, n) - Pause/Resume I/R source
TargetPoll (destName, n ) - Pause/Resume destination
ReEnlistSubscriptions(groupName, n) - Reset I/R group
(specify n=0 to stop polling; n=1 to resume polling)

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

DeleteConnection(connection-number)

ResetGroupStatistics (groupName)
ResetSubscriptionStatistics (sourceName, subscriptionName)

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

ServiceDown (ShutDownTime) - ShutDownTime in seconds
There is no corresponding method to start the service, but any COM action such as running a subscription or checking the status of a subscription will restart the service if it is not running.

The following methods return a recordset:

GetSubscriptionStatistics (sourceName, subscriptionName)

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

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

EnumerateSubscriptions (sourceName)
EnumerateGroups ()
EnumerateGroupMembers (groupName)

EnumerateSrcDest(srcDestType)
where srcDestType is one of the following:
1 = Source
2 = Destination
3 = Staging connection

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

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

StringProperty (propertyName)
Int32Property (propertyName)
BooleanProperty (propertyName)

For example, StringProperty("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.

Configuration-related methods

The above methods are either operational or retrieve information. The following have the potential to damage your configuration and should be used with caution; in general we recommend using the Data Replicator Manager for configuration changes. Also be aware that a running instance of Data Replicator Manager may get confused about the state of a configuration if you make changes from another application.

MoveSubscription (sourceName, subscriptionName, toGroupName)
DeleteSubscription (sourceName, subscriptionName, groupName)
RenameSubscription (sourceName, subscriptionName, newSubscriptionName)

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

DeleteSrcDest (srcDestType, srcDestName)
where srcDestType is one of the following:
1 = Source
2 = Destination
3 = Staging Connection
Use with caution. If the source is enabled for incremental replication, be sure to delete the matching Staging Connection.

RenameGroup (groupName, newGroupName)
DeleteGroupMember (groupName, sourceName, subscriptionName)
DeleteGroup ( groupName, 0 | 1 )
Set the second parameter to 1 to delete the member subscriptions as well as the group (applicable only to snapshot groups; deleting incremental groups should always delete the member subscriptions).

The Service method can be used for complex tasks such as exporting and importing the subscriptions of an incremental group or exporting the service properties. See the PowerShell example above.

Service(Command: command, Parameters: parameters, [Validate: true|false] )

Additional examples included in SQDR distribution

Several additional VBScript examples are installed with SQDR:

• TargetChecker.vbs
• EnableKeyLog.vbs
• PauseResumeIRGroup.vbs

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