Using LiveCompare to run Stored Procedures from a Remote SQL Server Machine

Using LiveCompare to Run Stored Procedures from a Remote SQL Server Machine

Introduction
This document describes how to use LiveCompare to run stored procedures from a database registered with a different SQL server machine than the one used to store your workspace databases. Each stored procedure should return an SQL table, which is copied to a Table dataset in LiveCompare.

In these examples, I'll use local to refer to the SQL Server used for workspace databases, and remote to refer to the SQL Server on which the stored procedures have been created.

Linking the Local and Remote SQL Servers
The first step is to create a link to the remote SQL Server so that the local SQL Server can run its stored procedures. This can be done as follows: 

1. Start Query Analyzer on the local machine
2. Execute the following SQL statement:

exec sp_addlinkedserver '<remote server>',
N'SQL Server'

For example:

exec sp_addlinkedserver 'AMETHYST',
N'SQL Server'

Verify that you can run a remote stored procedure from the local machine, for example:

execute AMETHYST.ICHelpDesk.dbo.nonclosedissues

Linked servers are shown in the Security/Linked Servers branch of the Enterprise Manager hierarchy. To remove a linked server, use:

sp_dropserver '<remote server>', 'droplogins'

For example:

sp_dropserver 'AMETHYST', 'droplogins'

Configuring Component Services

The next step is to configure the MSDTC settings on the local SQL Server so that it is able to execute remote transactions. This can be done as follows: 

1. Go to the ‘Administrative Tools' section of the Control Panel, and run the ‘Component Services' applet.
2. In the Component Services hierarchy, navigate to the ‘Console Root > Component Services > Computers > My Computer' folder, and choose ‘Properties' from the context menu.
3. In the ‘My Computer Properties' dialog, select the ‘MSDTC' tab and click ‘Security Configuration...'
4. Complete the ‘Security Configuration' dialog so that it looks like this:

1. Close the Component Services applet and restart SQL Server.

Calling Stored Procedures from LiveCompare
Create a LiveCompare workspace on the local SQL Server machine, with SQL authentication using the ‘sa' account. Add a workflow which uses the Execute SQL action to call a remote stored procedure. The Execute SQL action should have a Result dataset, and a Statements parameter which looks like this:

CREATE TABLE Result
(
<definition matching the table returned by the stored procedure>
)

INSERT INTO Result
EXECUTE <remote server>.<database>.dbo.<stored procedure>

For example: 

CREATE TABLE Result
(
HelpdeskID int,
Company nvarchar(50) NULL,
RaisedBy nvarchar(50) NULL,
AssignedTo nvarchar(50) NULL,
DateRaised nvarchar(20) NULL,
Product nvarchar(50) NULL,
Title nvarchar(50) NULL,
Priority nvarchar(50) NULL,
AgeInDays int
)
INSERT INTO Result
EXECUTE AMETHYST.ICHelpDesk.dbo.nonclosedissues 

Note: Use the ‘nvarchar' type for datetime fields to allow the Result dataset to be used successfully with other LiveCompare actions (for example, the Create Access Report action).

Running the Workflow
Run the workflow and verify that it produces the correct results.