9202: nested stored procedures not allowed

Symptom

These error messages are displayed in the Adaptive Server error log.

00:00000:00011:2006/06/13 16:26:05.12 server
Error: 9202, Severity: 20, State: 0
00:00000:00011:2006/06/13 16:26:05.12 server
RepAgent(5): Nested replicated stored procedure
detected. Transaction log may be corrupt. Please contact
SYBASE Technical Support. (current marker = 1372, 12)
00:00000:00011:2006/06/13 16:26:05.12 server Rep Agent
Thread for database 'pdb' (dbid = 5) terminated
abnormally with error. (major 92, minor 2)

Explanation

A nested stored procedure is called from within another stored procedure. The stored procedure that calls the nested stored procedure is called the outer stored procedure.

When stored procedures with nested stored procedures are marked for replication with sp_setrepproc:

The RepAgent shuts down.
The RepAgent forwards only the outer stored procedure call to the Replication Server.
An error message is displayed in the Adaptive Server error log.

Solution

Do not use nested replicated stored procedures.

Working around the nested stored procedure

Skip the nested stored procedure transaction:
1. Find the page of the secondary truncation point, by executing:
```
dbcc gettrunc
```
2. Find a valid page after the nested stored procedure transaction by executing the following command:
```
dbcc traceon(3604)
dbcc pglinkage(dbid, pageid, 0,2,0,1)
```
  where pageid is the ID for the page you received by executing dbcc gettrunc in step a.
3. Set a new secondary truncation point on the valid page after the nested stored procedure transaction by executing:
```
dbcc settrunc (‘ltm’, ‘pageid’, pageid)
```
  where pageid is the id for the page after the current page you retrieved using dbcc pagelinkage in step b.
4. Reset the locater by executing:
```
rs_zeroltm
```
Reapply only the nested stored procedure transaction.
Restart RepAgent.

This procedure can also cause data loss because manually setting the secondary truncation point to a later page in the log skips any begin transaction statements on skipped pages. Those transactions do not replicate.