Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.
Here is the problem:
When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:
execute sp_mapdown_bitmap 0x0000000040, @.bm output
The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).
Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.
We are getting this behavior on several SQL Server 2005's...
Additional notes on the environment:
-CLR Triggers written in VB, on most tables performing application functions
-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.
-Again this problem only exists on some tables in the database.
Any assistance would be greatly appreciated.
Can you give us some more detail about the problem? When you say you cannot do any updates, do you mean the updates are taking a long time to return, or do you suspect the SPID is hung? How many rows are in the table?
|||No the updates completely fail with error message, because the Update triggers for the replication have the line:
execute sp_mapdown_bitmap 0x0000000040, @.bm output
...placed in them, that makes a call to xp_mapdown_bitmap, which as far as I can tell does not exist on the server (If I try and execute xp_mapdown_bitmap from a query tool, I get the does not exist error).
This problem existed before SP1 and after. We have completely rebuilt the publication, made the publication only do one of the problem tables, nothing we can do seems to correct the error, except commenting the line out of the Triggers.
|||Without knowing the error, it's hard to pinpoint the problem. Are you getting an error message?|||xp_mapdown_bitmap is not public, that is why you will get the does not exist error. Please give the specific error message when update fails so we can investigate further.
Thanks
Wanwen
|||
Ok, after some further investigation the problem seems to be occuring when one CLR Trigger, executes an update on anothre table that has the replication trigger on it. Commenting out the line sp_mapdown_bitmap from the replication trigger fixes the problem (on the table being updated). The error we get from inside the CLR trigger on the UPDATE command is the following:
Msg 6549, Level 16, State 1, Procedure AR_Invoice_InvoiceNumber, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'AR_Invoice_InvoiceNumber':
System.Exception: <Message>The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.</Message> > System.Data.SqlClient.SqlException: The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
System.Exception:
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
at SQLHVMS.Receivables.Triggers.AR_Invoice_InvoiceNumber()
. User transaction, if any, will be rolled back.
The statement has been terminated.
--
Initially we had no transaction logic in the CLR trigger, since then we have tried placing a transaction around the contents of our CLR trigger (using sqlconnection.begintran, commit, etc) that seemed to have no effect.
Again what confuses me is commenting out that mapdown call in the replication update trigger immediately fixes the problem. So is there some issue with executing that statement when a CLR trigger is the source of the call of the update?
Jason
|||Hi Jason,
This is actually a known issue with respect to the execution of extended procedure within SQLCLR. Unfortunately, the fix hasn't been fully approved for SP2 yet so I will attach your posting to the bug which will hopefully expedite the approval process. But since there really is no good workaround for your scenario (triggers are always executed in transaction context), you may want to push for a QFE by logging a bug @. http://lab.msdn.microsoft.com/productfeedback/, our internal reference number for this bug is SQLBU450991.
Thank you for reporting the issue.
-Raymond
|||Thanks for the response. Can you please enlighten me on what the possible negative side effects of just commenting out the sp_mapdown_bitmap would be in one of the Replication triggers?
I sort of need a workaround for the mean time if possible...
|||Hi Jason,
Since I am no merge replication expert, I can only imagine that commenting the call out will break merge replication. Ok, I should probably let the merge experts chime in 
on that. I do have this rather far-fetched idea of committing\starting transactions around the sp_mapdown_bitmap call in your trigger in an attempt to trick the server into thinking that the transaction balances out. How about making most of your trigger T-SQL and call CLR procs to do the things that you absolutely have to do in CLR?
-Raymond
|||Jason,
sp_mapdown_bitmap is necessary to adjust the bitmap for updated columns when column id in the table is not continuous, e.g. there has been columns dropped from the table. Commenting out sp_mapdown_bitmap could lead to data non-convergence. It is not a valid workaround.
Will workaround mentioned by Raymond work for you?
Wanwen
|||
The transaction idea didnt seem to have any effect. And switching away from CLR would be a multi-week resolution to the problem, which would be hard to swallow...
If we are 100% sure there will be no added/removed columns, as a temporary workaround is commenting out that line not an option?
|||The fact your update trigger is calling sp_mapdown_bitmap shows there is a need to do the map down adjustment. It could be because there has been dropped column, or table contains computed or timestamp column. You could run a simple query to find out:
select column_id from sys.columns where object_id = OBJECT_ID(<your_source_table>)
to see if column_id is continous from 1 to max column id
select * from sys.columns where object_id = OBJECT_ID(<your_source_table>) and (is_computed = 1 or system_type_id = type_id('timestamp'))
to see if you have computed column or timestamp column.
As I said before, commenting out calling sp_mapdown_bitmap in this case will lead to data non-convergence. We already brought up the issue and hopefully the bug could be fixed in SP2
After running the queries, a "hole" did show up in the column id's in one of the tables causing the problem (there is no computed or timestamp columns). Is there anyway to close the gap or renumber the columns?
As much as I want to wait for SP2, we all know it could be quite awhile and we were really hoping to go live with the solution shortly...
|||Jason,
You could
1.drop that article from your publication
2.run snapshot and merge agent if necessary
3.drop the physical tables from publisher and subscriber
4.recreate the table at publisher, make sure there are no holes in column id this time
5.add that article back to your publication
6.rerun snapshot and merge agent.
Or you could clean up your environment and start from beginning if that is more convenient for you.
Please let me know if you have any further questions.
Wanwen
|||Dear Raymond,Please also see my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=865299&SiteID=1&mode=1
since I am also running into a problem with CLR trigger nesting which seems related to the bug. Thanks in advance.
No comments:
Post a Comment