March 22, 2016 at 9:43 am
We have replication running between a SQL 2000 publisher (I know its old but one main application won't work without it), 2008 R2 Distributor and a 2008 R2 Subscriber. Things have been running great for years. We recently upgrade from 2008 R2 SP1 to SP3 on the Distributor and subscriber. Most of the subscriptions came back up fine after upgrade but a couple failed for various reasons. When we tried to reinitialize them we got the dreaded Skipping file <filename> because it has already been delivered for a previous article or by a previously interrupted snapshot. It eventually failed saying Could not find stored procedure 'sp_MSdel_dboTableName' I tried rolling back the SP3 upgrade but got the same result.
When I ran a trace on the process I found what was causing the snapshot reset:
RPC:Starting exec sp_MSdel_dboBBJTHEAD;2 '75696'
User error message Could not find stored procedure 'sp_MSdel_dboBBJTHEAD'.
Does anyone know why replication would be using a Numbered stored procedure?
March 23, 2016 at 9:47 am
Update to the problem - I create numbered copies of all the stored procedures and reran the snapshot. I received the same error as before. I copied the actual statement from the trace file and ran it in a query window. It still did not find the procedure even though I knew it was there. I put square brackets around the procedure call and low and behold it worked.
exec [sp_MSins_dboARTRNTRX;2] - Works
exec sp_MSins_dboARTRNTRX;2 can't find procedure.
Does anyone know how to get around this?
March 24, 2016 at 9:53 am
I have discovered the solution by trial and error. Uninstalling SP3 and the hotfix from the Distributor only seems to have corrected the problem.
March 29, 2016 at 2:20 pm
When we moved this fix to production, it work for one subscription and failed for two others. After a little more trial and error I was able to determine that transactions running against the production database will also cause the error. After shutting off access to the database and reinitializing the subscriptions it worked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply