FIX: New Trace Flag to Enhance Stored Procedure Replication (284228)

SYMPTOMS

Replication of stored procedure execution is a feature introduced in Microsoft SQL Server version 7.0, which allows the execution of stored procedures to be replicated rather than the individual data changes. This feature is designed to include automatic owner qualification for stored procedures with the publisher side owner name and allows different users on the publisher to run stored procedures with the same name and still be replicated to the subscriber. This feature is not supported for heterogeneous subscribers, because some heterogeneous subscribers do not support owner qualification at the server level or ODBC driver level. A new trace flag, 8206, has been introduced in SQL Server 2000 that supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 1.

The replication of stored procedure execution feature is implemented much like transactional replication with table articles. The stored procedure execution is replicated by regenerating the stored procedure name from the log records and is qualified with the publisher side owner name. With the 8206 trace flag enabled, the stored procedure name that is regenerated, is qualified with the user-specified, subscriber side owner name for SQL Server subscribers and is not owner qualified for heterogeneous subscribers. The logreader then inserts the commands into the distribution database for the distribution agent to apply at the subscriber.

This feature is introduced in a hotfix build, to make the feature available immediately. You need to apply this hotfix and then turn on trace flag 8206 for replicating stored procedures with user specified owner qualification or to heterogeneous subscribers.

The English version of this fix should have the following file attributes or later:

   File name      Platform
   -----------------------

   s80237i.exe    X86

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

MORE INFORMATION

You need to enable the trace flag on the publishing server by performing either of the following:

Run the following code from the SQL Server Query Analyzer:
```
DBCC TRACEON (8206)
						
```
NOTE: You need to repeat this step every time SQL Server is stopped and restarted.

-or-
If you want to have the 8206 trace flag turned ON permanently then:
1. Stop the MSSQLServer service.
2. Add -T8206 to the startup parameters. To do this, from Enterprise Manager:
  1. Right-click the servername, and then click Properties.
  2. In the Properties dialog box, click the General tab, and then click startup parameters.
  3. Add the traceflag.
3. Start the MSSQLServer service.
For more information, refer to the SQL Server Books Online topics "Trace Flags" and "Using Startup Options".

From this point forward, the logreader generates commands for execution of stored procedures without owner qualification, for heterogeneous subscribers. For SQL Server subscribers, the logreader generates the stored procedure call with the subscriber side owner name specified in the article and if the owner name is not specified, without owner qualification.