Replication Trigger Problem (SQL 2005): Assistance Please

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.

Replication Trigger Problem (SQL 2005): Assistance Please

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.

replication timeout

I have 3 production machines which are replicated to a dr site, all
machines are using a central distributor machine. I have created a
separate distributor database for each system.
My problem is that one of my systems will not replicate I am doing
snapshot and transactional, it just keeps timing out I have put sql
profiler on the dr box and can see that the data gets to the box but
it just times out. All boxes publisher, distributor, subscriber can
see each other so why is it timing out?
what is the exact error message you are getting?
"stephen" <twolf@.icon.co.za> wrote in message
news:75c81dee.0404170439.22bbd18d@.posting.google.c om...
> I have 3 production machines which are replicated to a dr site, all
> machines are using a central distributor machine. I have created a
> separate distributor database for each system.
> My problem is that one of my systems will not replicate I am doing
> snapshot and transactional, it just keeps timing out I have put sql
> profiler on the dr box and can see that the data gets to the box but
> it just times out. All boxes publisher, distributor, subscriber can
> see each other so why is it timing out?
|||Have you looked into creating a different snapshot profile?
If you go into Publisher/Distributor Properties, Agent Profiles, and
then Snapshot Tab, If you look at the existing "Default profile" the
Query time-out is set to 300; you might want to increase that number
to maybe 1000 or so.
Here is something that I have found interesting though that you might
want to know ahead of time so you DON'T CANCEL the replication
process:
Depending on how high you increase this number to, there is always the
potential of getting a somewhat bizare error message while the
replication is running stating that no response has been received from
the snapshot agent, and the Replication monitor will display the red X
symbols (like if it had failed). However, the process is still alive
and well (you can check it under jobs to see if it is still in fact
running), so once it goes past the table that may be causing it to
time-out, the Replication monitor will refresh and the Xs will be
removed.
Try this on a test environment first as every environment reacts
diferently, and always remember to keep a current backup just in case.
Hope this helps.
Jose
twolf@.icon.co.za (stephen) wrote in message news:<75c81dee.0404170439.22bbd18d@.posting.google. com>...
> I have 3 production machines which are replicated to a dr site, all
> machines are using a central distributor machine. I have created a
> separate distributor database for each system.
> My problem is that one of my systems will not replicate I am doing
> snapshot and transactional, it just keeps timing out I have put sql
> profiler on the dr box and can see that the data gets to the box but
> it just times out. All boxes publisher, distributor, subscriber can
> see each other so why is it timing out?

replication tables with forign key

Hi all

i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.

can anyone help me? :confused:

Thanx
M.J.Daneshhttp://www.microsoft.com/technet/prodtechnol/sql/2000/books/c09ppcsq.mspx for reference on configuring Merge/Transactional/snapshot replication types.

Replication Model

Hello,
I am just playing about with replication and trying to work the best way
to keep a central database updated.
We have a laptop user who will need to be able to work in disconnected mode
(MSDE) then connect up and replicate changes back to a server from which
others can run reports. The server copy will never be updated besides from
the changes replicated down from the 1 laptop user.
What would be the best model for this? Seems I have a few options but was
hoping someone could point me in the right direction of which machine would
be the publisher, the type (merge, snapshot) and whether push or pull. I
understand the concepts of publisher, subscriber etc, but am just unsure of
how it all fits together.
Thanks is advance
- EdHi
It sounds like your application would probably be a candidate for merge
replication, especially if there is a future need for more than one client
updating the data and if you are replicating larger volumes of data.
Snapshot replication would be more applicable if the data was not updatable
and the frequency of replication low.
You don't say which version of SQL Server is running on the server, this may
effect how you administer the replication and you may want to check out the
topic "Understanding SQL Server 2000 Desktop Engine (MSDE 2000)" in Books
Online.
John
"Ed." wrote:
> Hello,
> I am just playing about with replication and trying to work the best way
> to keep a central database updated.
> We have a laptop user who will need to be able to work in disconnected mode
> (MSDE) then connect up and replicate changes back to a server from which
> others can run reports. The server copy will never be updated besides from
> the changes replicated down from the 1 laptop user.
> What would be the best model for this? Seems I have a few options but was
> hoping someone could point me in the right direction of which machine would
> be the publisher, the type (merge, snapshot) and whether push or pull. I
> understand the concepts of publisher, subscriber etc, but am just unsure of
> how it all fits together.
> Thanks is advance
> - Ed
>
>

Replication Model

Hello,
I am just playing about with replication and trying to work the best way
to keep a central database updated.
We have a laptop user who will need to be able to work in disconnected mode
(MSDE) then connect up and replicate changes back to a server from which
others can run reports. The server copy will never be updated besides from
the changes replicated down from the 1 laptop user.
What would be the best model for this? Seems I have a few options but was
hoping someone could point me in the right direction of which machine would
be the publisher, the type (merge, snapshot) and whether push or pull. I
understand the concepts of publisher, subscriber etc, but am just unsure of
how it all fits together.
Thanks is advance
- Ed
Hi
It sounds like your application would probably be a candidate for merge
replication, especially if there is a future need for more than one client
updating the data and if you are replicating larger volumes of data.
Snapshot replication would be more applicable if the data was not updatable
and the frequency of replication low.
You don't say which version of SQL Server is running on the server, this may
effect how you administer the replication and you may want to check out the
topic "Understanding SQL Server 2000 Desktop Engine (MSDE 2000)" in Books
Online.
John
"Ed." wrote:

> Hello,
> I am just playing about with replication and trying to work the best way
> to keep a central database updated.
> We have a laptop user who will need to be able to work in disconnected mode
> (MSDE) then connect up and replicate changes back to a server from which
> others can run reports. The server copy will never be updated besides from
> the changes replicated down from the 1 laptop user.
> What would be the best model for this? Seems I have a few options but was
> hoping someone could point me in the right direction of which machine would
> be the publisher, the type (merge, snapshot) and whether push or pull. I
> understand the concepts of publisher, subscriber etc, but am just unsure of
> how it all fits together.
> Thanks is advance
> - Ed
>
>

Replication Model

Hello,
I am just playing about with replication and trying to work the best way
to keep a central database updated.
We have a laptop user who will need to be able to work in disconnected mode
(MSDE) then connect up and replicate changes back to a server from which
others can run reports. The server copy will never be updated besides from
the changes replicated down from the 1 laptop user.
What would be the best model for this? Seems I have a few options but was
hoping someone could point me in the right direction of which machine would
be the publisher, the type (merge, snapshot) and whether push or pull. I
understand the concepts of publisher, subscriber etc, but am just unsure of
how it all fits together.
Thanks is advance
- EdHi
It sounds like your application would probably be a candidate for merge
replication, especially if there is a future need for more than one client
updating the data and if you are replicating larger volumes of data.
Snapshot replication would be more applicable if the data was not updatable
and the frequency of replication low.
You don't say which version of SQL Server is running on the server, this may
effect how you administer the replication and you may want to check out the
topic "Understanding SQL Server 2000 Desktop Engine (MSDE 2000)" in Books
Online.
John
"Ed." wrote:

> Hello,
> I am just playing about with replication and trying to work the best way
> to keep a central database updated.
> We have a laptop user who will need to be able to work in disconnected mod
e
> (MSDE) then connect up and replicate changes back to a server from which
> others can run reports. The server copy will never be updated besides from
> the changes replicated down from the 1 laptop user.
> What would be the best model for this? Seems I have a few options but was
> hoping someone could point me in the right direction of which machine woul
d
> be the publisher, the type (merge, snapshot) and whether push or pull. I
> understand the concepts of publisher, subscriber etc, but am just unsure o
f
> how it all fits together.
> Thanks is advance
> - Ed
>
>