Prev: Replication Issue Also
Next: Error in replication
From: Ron James on 26 Aug 2008 11:30
We use a DBUpgrade program to maintain our database schemas. Part of this
process is to configure replication. On our test servers we often configure
the publisher, subscriber and distributor on the same physical server. When
doing so we are bumping into a timing issue with sp_addsubscription.

We call ODBC directly. We pool DB Connections but we alloc and free
statement handles for each batch of SQL we execute. (This is library code
that we've been using happily for ~10 years). So when executing
sp_addsubscription we:

- alloc a statement handle using SQLAllocHandle
- execute the SP
- free the statement handle using SQLFreeHandle

For small numbers of published articles (~60) this works fine but for larger
numbers (~200) we end up with uncommitted transactions on the connection.
This blocks one or more SQL Agent SPIDs and our server is then busted.

We can 'fix' the problem by sleeping for a short time before SQLFreeing the
statement handle although this is obviously not a great solution. Note that
sleeping after the statement handle is closed does not 'fix' the problem. We
still end up with uncommitted transactions on that connection (Indicated by
select @@TranCount on another statement handle associated with this
connection).

I then discovered some more weirdness: I wrapped sp_addsubscription in
another SP - sp_MyAddSubscription with a view to calling sp_addsubcription
and then waiting for <something?>. But I discovered that when
sp_addsubscription finished executing, my SP also terminated immediately
without executing any subsequent code. As a test, in my SP, I called
sp_addsubscription and then immediately raised an error using raiserror. My
program did not detect the error. If I placed the raiserror before the call
to sp_addsubscription my program detected the error as expected.

 | 
Pages: 1
Prev: Replication Issue Also
Next: Error in replication