Application Note - Using Stored Procedure Data Linksred Procedure Data Links
Manager Contents
User Contents
Introduction

Stored procedure data links allow selection list data or field defaults to be loaded from SQL databases located anywhere on your network. This application note demonstrates how the feature may be used.

Implementation Example

This example will use the pubs database on Microsoft SQL Server. The pubs database is a sample database that can be installed if desired. To verify that your server contains this database, open SQL Server Management Studio and browse the databases on your system. It can be downloaded from Microsoft if it does not exist.

Step 1 - Create a Stored Procedure

  • Open SQL Server Enterprise Manager and browse to the CATSWeb database.
  • Select Stored Procedures.
  • Right-click and select New Stored Procedure.
  • Edit the text in the procedure window until it looks like this:

    CREATE PROCEDURE sp_TestPubsAuthors
    AS
    Select au_lname from pubs.dbo.authors

    Notice that "pubs.dbo.authors" is a fully-qualified source specification in the form database.owner.object_name. "au_lname" is the name of the column in the authors table that contains last name information for the authors.

  • Save the procedure by clicking OK.
  • Reopen the procedure by locating sp_TestPubsAuthors in the list of stored procedures and double-clicking it.
  • Click the Permissions button.
  • Grant EXEC (execute) privileges to the user name that is used by CATSWeb to access the database (i.e. the user name that appears in CATSWeb Connection Manager).
  • Click OK and save the stored procedure.

Step 2 - Test the Stored Procedure

  • From the Tools menu in SQL Server Management Studio, select New, Query.
  • In the DB pull-down list, select the CATSWeb database.
  • In the window, enter the name of the stored procedure (sp_TestPubsAuthors).
  • Press F5 or click the green arrow in the toolbar to execute the stored procedure. The window will split into two panes, and the lower window will show the results of the stored procedure. You should see the name of the table column (au_lname) followed by a list of author last names. If you do not see this, debug your stored procedure as required.

Step 3 - Create a Stored Procedure Data Link in CATSWeb

In CATSWeb Manager, select Data Links, then add a new Stored Procedure Data Link. Enter or select these values in the fields:

Data Link Name: "Pubs Authors"
Description: "A data link that provides a list of author last names from Pubs database"
Procedure Name: "sp_TestPubsAuthors"
Data Field: Leave blank or enter "au_lname"

Leave all the parameter entries blank since your stored procedure does not require or accept parameters.

NOTE: After you save the data link, CATSWeb will automatically rename it to "[Pubs Authors]" so that it (as a user-defined data link) can be distinguished from common lists or system data links.

Step 4 - Bind the Data Link to a Form Field

In CATSWeb Manager, select Field Definitions for the category in which you want to add the link. Click a field to redefine for this purpose. On the Form Definition Edit page, select "[Pubs Authors]" from the Bind to List pull-down. Change the caption to "Authors" and edit other values as desired.

Step 5 - Mission Accomplished

Save the field definition. Scroll down to the sample form that you added the link to. The pull-down list should now display a list of author last names from the Pubs database.

Linking to Databases on Other Servers

The technique illustrated above can be extended to allow databases that reside on other servers to be linked into CATSWeb. Assume that you wish to link in data from the Pubs database that resides on a server called "MfgServer". The following modifications and additions to the above procedure are required:

  • Open SQL Server Management Studio and browse the SQL Server that the CATSWeb database resides on.
  • Open the Security folder and click on Linked Servers.
  • Right-click and select New linked Server. Add the "MfgServer" server.

Stored procedures can now be developed that refer to tables on MfgServer. For example:

CREATE PROCEDURE sp_TestPubsAuthors
AS
Select au_lname from MfgServer.pubs.dbo.authors

Notice that "MfgServer.pubs.dbo.authors" is a fully-qualified source specification in the form server.database.owner.object_name.

Repeat the steps above to test the procedure, link it in, etc.

Note that for this technique to work, the MfgServer must have a user name/password combination that is identical to the user name/password used by CATSWeb for accessing the CATSWeb database. That user name must have Select permission for the tables that will be accessed.

If your stored procedure needs to receive parameters from CATSWeb, see this topic for an example and more information.