9202: Nested stored procedures not allowed (LTM 1033)

6.5 Nested Replicated Stored Procedure

Symptoms

The Adaptive Server error log displays these error messages:

96/03/04 14:01:53.34 RepAgent (10) Error: 9202, Severity: EX_CMDFATAL, State: 1
Nested replicated stored procedure detected.
Transaction log may be corrupt. Please contact SYBASE
Technical Support.

W. 96/03/04 14:01:53. WARNING #1033 logscan
thread(NYDS.nydb1) - /ltmscan.c(4689) Nested replicated
stored procedure is not allowed. The procedure name =
'south_nested', pid = '456', rid = '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.

If 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, and
An error message is displayed in the Adaptive Server error log.

Solution

Do not use nested replicated stored procedures.

Solving the problem

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, where pageid is the ID for the page you received by executing dbcc gettrunc in step a:
```
dbcc traceon(3604)
dbcc pglinkage(dbid, pageid, 0,2,0,1)
```
3. Set a new secondary truncation point on a valid page after the nested stored procedure transaction by executing the following command, where pageid is the ID for the page after the current page you retrieved using dbcc pglinkage in step b:
```
dbcc settrunc ('ltm', 'pageid', pageid)
```
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.