INF: How to Set Up the ProcSrv ODS Server Application

ID: Q194523

The information in this article applies to:

Microsoft SQL Server version 6.5

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

SUMMARY

This article describes how to set up the ProcSrv Open Data Services (ODS) sample (that comes with the SQL Programmer's Toolkit) on the same computer that the SQL Server is installed on. After it has been set up, the ProcSrv application will appear to a SQL Server as another remote SQL Server.

MORE INFORMATION

To set up ProcSrv on the SQL Server, perform the steps below.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys And Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT, you should also update your Emergency Repair Disk (ERD).

Define the alternate named pipe that the ODS ProcSrv server application will listen on. To do this, you must make entries in the registry for the ProcSrv application for the alternate named pipe. To do this, perform the following steps:
1. Start Regedt32. In HKEY_LOCAL_MACHINE on Local Machine, go to the Software\Microsoft\MSSQLServer key.
2. On the Edit menu, click Add Key. In the Key Name box, type "ProcSrv" (without the quotation marks), but leave the Class line blank.
3. Highlight the new "ProcSrv" key and on the Edit menu, click Add Value. In the Value Name box, type "ListenOn" (without the quotation marks). For the Data Type, select REG_MULTI_SZ. In the Multi-String Editor, type the following string:
```
         SSNMPN60,\\.\pipe\procsrv\query
      
```

On the SQL Server, connect with ISQL/W or SQL Enterprise Manager and use sp_addserver and sp_configure to identify and enable the ProcSrv application as a remote server in the same way you would identify a SQL Server as a remote server. For example, use the following:
```
      sp_addserver ProcSrv
 
```
Then ensure that the sp_configure value for "remote access" is enabled, or set to one (1). If it is not, run the following:
```
      sp_configure 'remote access', 1
      go
      reconfigure with overrride
 
```

On the SQL Server that you defined the ProcSrv application as a remote SQL Server, set up an advanced client entry to connect to the alternate named pipe that the ProcSrv application will be listening on. To do this, perform the following steps:

NOTE: The sample below shows that the ProcSrv and the SQL Server are on the same computer and are listening on named pipes.
1. In the SQL Client Configuration Utility, click the Advanced tab and add the following:
```
         Server: ProcSrv
         DLL Name: Named Pipes
         Connection String: \\.\pipe\procsrv\query
    
```
2. Click Add/Modify.
3. Use Regedt32 to verify that the setting in the HKEY_LOCAL_MACHINE on Local Machine Software\Microsoft\Mssqlserver\Client\ConnectTo key contains:
```
         ProcSrv: REG_SZ: dbnmpntw,\\.\pipe\procsrv\query
   
```

Compile the ProcSrv application.

From a command prompt, start the server application to use the registry key as defined in step 1 above. Use the following command line to start ProcSrv.exe:
```
      ProcSrv  -RProcSrv
 
```

Test the sample ProcSrv application to see if usage information is returned. To do this, connect to the SQL Server and issue the following query to the ProcSrv application, which appears as a remote SQL Server:
```
      execute ProcSrv...proclist
 
```
The command prompt that ProcSrv is executing from will reflect that a connection has been made:
```
      Client process ID: 000000d6
      User name: sa
      Application program name: MS SQLEW
      Remote Server: Test
 
```

NOTE: If you will have other SQL Servers connect to the ProcSrv sample, modify their advanced entries in the SQL Client Configuration Utility to connect to the pipe on the Windows NT computer that the ProcSrv application is executing from and listening on, as in the following example:


   ProcSrv: REG_SZ: dbnmpntw,\\<nt_machine_name>\pipe\procsrv\query

For more documentation on this sample, see Chapter Eight, "Sample Application[ASCII 150]Procedure Server" in the "Programming Open Data Services 6.0" book or the SQL Server Books Online.

Additional query words: prodsql gateway

Keywords : SSrvODS
Version : WINNT:6.5
Platform : winnt
Issue type : kbhowto kbinfo