Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>
Friday, March 30, 2012
Replication triggers
Dear All,
the subscriber database contains no longer the (upd ,
ins , del) triggers of the replication.
How do i create them again , or do i need to drop the
subscription and do it all over again.
Please feedback urgently
Thanks in Advance
Dalia,
you should be able to script them out by creating a seaparate (fake)
subscription, and then apply them to the subscriber. I'd be a little
concerned that other details may be missing though and be tempted to
reinitialize. Also, I suspect that the excahnge type value is incorrect
which has caused this issue.
HTH,
Paul Ibison
|||I urge you to re-initialize your merge subscription(s), rerun your snapshot, and deploy it by rerunning your merge agent.
sql
the subscriber database contains no longer the (upd ,
ins , del) triggers of the replication.
How do i create them again , or do i need to drop the
subscription and do it all over again.
Please feedback urgently
Thanks in Advance
Dalia,
you should be able to script them out by creating a seaparate (fake)
subscription, and then apply them to the subscriber. I'd be a little
concerned that other details may be missing though and be tempted to
reinitialize. Also, I suspect that the excahnge type value is incorrect
which has caused this issue.
HTH,
Paul Ibison
|||I urge you to re-initialize your merge subscription(s), rerun your snapshot, and deploy it by rerunning your merge agent.
sql
replication triggers
Hi all:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
index 'uc1MSmerge_tombstone'.
The statement has been terminated.
that is the error i got when i try to delet a record from table which has
merge replication on.
i had a check that this error actually is from the trigger generated by
replication..
so any helps will be appreciated.
Thanks
Nick
"nick" schrieb:
> Hi all:
> Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique
> index 'uc1MSmerge_tombstone'.
> The statement has been terminated.
> that is the error i got when i try to delet a record from table which has
> merge replication on.
> i had a check that this error actually is from the trigger generated by
> replication..
> so any helps will be appreciated.
> Thanks
> Nick
Run the merge agent and try again ...
Replication Triggers
Is there such a thing as a "Replication BLOB Trigger?"
Enover
I have never heard this term before, can you give us an example of what you
are talking about?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
> Is there such a thing as a "Replication BLOB Trigger?"
> --
> Enover
|||We are in the process of updating our software and trying to find ways to
reduce the update time. The vendor sent us an email stating that we need to
evaluate replication blob triggers and decide whether they need adjustment or
additions.
My first question (red-flag), was do we mess with replication triggers? If
we adjust the triggers, who would future hotfixes or Service Packs impact the
adjustments we made.
However, since I have never heard of such a term and could not find it any
documentation on Microsoft site, I thought I would ask. Do replication
triggers (update, delete , insert) treat ntext, text or image fields
different from other columns?
Enover
"Hilary Cotter" wrote:
> I have never heard this term before, can you give us an example of what you
> are talking about?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
>
>
|||Press the vendor on what they are referring to. There is no such thing.
What they may be referring to is that certain replication types - queued,
immediate, and merge - do not handle text and image columns well.
For queued - From BOL - Queued Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. Use merge replication
to synchronize these values. You cannot be assured there are no conflicts
because the update of the text or image table can occur if the data is not
well partitioned.
For immediate - From BOL - Immediate Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. You could use merge
replication to synchronize these values if updates to text or image columns
are needed at the Subscriber. You can be assured there are no conflicts if
all updates follow this guideline because the update of the text or image
table cannot occur unless the main table was updated, which is protected by
2PC.
For merge - From BOL - Planning for Merge Replication
text and image Data Types in Merge Replication
Merge replication supports the replication of text, ntext, and image columns
only if they have been updated explicitly by an UPDATE statement because it
causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers.
Using only the WRITETEXT and UPDATETEXT operations will not propagate the
change to other sites. If your application uses WRITETEXT and UPDATETEXT to
update the text or ntext columns, explicitly add a dummy UPDATE statement
after the WRITETEXT or UPDATETEXT operations, within the same transaction,
to fire the trigger and thereby guarantee that the change will be propagated
to other sites.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||No, you do not want to "mess" with the triggers that replication creates.
That doesn't mean you can't do that, but you will not be supported if you
do.
Also, there is no such thing as replication blob triggers. Ask the vendor
give you an explicit example of what a replication blob trigger is including
sample code.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||Thank you all for your help.
Enover
"Michael Hotek" wrote:
> No, you do not want to "mess" with the triggers that replication creates.
> That doesn't mean you can't do that, but you will not be supported if you
> do.
> Also, there is no such thing as replication blob triggers. Ask the vendor
> give you an explicit example of what a replication blob trigger is including
> sample code.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...
>
>
Enover
I have never heard this term before, can you give us an example of what you
are talking about?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
> Is there such a thing as a "Replication BLOB Trigger?"
> --
> Enover
|||We are in the process of updating our software and trying to find ways to
reduce the update time. The vendor sent us an email stating that we need to
evaluate replication blob triggers and decide whether they need adjustment or
additions.
My first question (red-flag), was do we mess with replication triggers? If
we adjust the triggers, who would future hotfixes or Service Packs impact the
adjustments we made.
However, since I have never heard of such a term and could not find it any
documentation on Microsoft site, I thought I would ask. Do replication
triggers (update, delete , insert) treat ntext, text or image fields
different from other columns?
Enover
"Hilary Cotter" wrote:
> I have never heard this term before, can you give us an example of what you
> are talking about?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:877A44BD-FFCE-4DE7-AF74-4D5B82072E6A@.microsoft.com...
>
>
|||Press the vendor on what they are referring to. There is no such thing.
What they may be referring to is that certain replication types - queued,
immediate, and merge - do not handle text and image columns well.
For queued - From BOL - Queued Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. Use merge replication
to synchronize these values. You cannot be assured there are no conflicts
because the update of the text or image table can occur if the data is not
well partitioned.
For immediate - From BOL - Immediate Updating Considerations
The Subscriber cannot update or insert text or image values because they
cannot be read from the inserted or deleted tables inside the trigger.
Similarly, the Subscriber cannot update or insert text or image values using
WRITETEXT or UPDATETEXT because the data is overwritten by the Publisher.
Instead, you could partition the text and image columns into a separate
table and modify the two tables within a transaction. You could use merge
replication to synchronize these values if updates to text or image columns
are needed at the Subscriber. You can be assured there are no conflicts if
all updates follow this guideline because the update of the text or image
table cannot occur unless the main table was updated, which is protected by
2PC.
For merge - From BOL - Planning for Merge Replication
text and image Data Types in Merge Replication
Merge replication supports the replication of text, ntext, and image columns
only if they have been updated explicitly by an UPDATE statement because it
causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers.
Using only the WRITETEXT and UPDATETEXT operations will not propagate the
change to other sites. If your application uses WRITETEXT and UPDATETEXT to
update the text or ntext columns, explicitly add a dummy UPDATE statement
after the WRITETEXT or UPDATETEXT operations, within the same transaction,
to fire the trigger and thereby guarantee that the change will be propagated
to other sites.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||No, you do not want to "mess" with the triggers that replication creates.
That doesn't mean you can't do that, but you will not be supported if you
do.
Also, there is no such thing as replication blob triggers. Ask the vendor
give you an explicit example of what a replication blob trigger is including
sample code.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Enover" <Enover@.discussions.microsoft.com> wrote in message
news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...[vbcol=seagreen]
> We are in the process of updating our software and trying to find ways to
> reduce the update time. The vendor sent us an email stating that we need
> to
> evaluate replication blob triggers and decide whether they need adjustment
> or
> additions.
> My first question (red-flag), was do we mess with replication triggers?
> If
> we adjust the triggers, who would future hotfixes or Service Packs impact
> the
> adjustments we made.
> However, since I have never heard of such a term and could not find it any
> documentation on Microsoft site, I thought I would ask. Do replication
> triggers (update, delete , insert) treat ntext, text or image fields
> different from other columns?
>
> --
> Enover
>
> "Hilary Cotter" wrote:
|||Thank you all for your help.
Enover
"Michael Hotek" wrote:
> No, you do not want to "mess" with the triggers that replication creates.
> That doesn't mean you can't do that, but you will not be supported if you
> do.
> Also, there is no such thing as replication blob triggers. Ask the vendor
> give you an explicit example of what a replication blob trigger is including
> sample code.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Enover" <Enover@.discussions.microsoft.com> wrote in message
> news:CE716760-EFE8-4737-9110-0C64CE23E7A6@.microsoft.com...
>
>
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 Transformation
I am currently trying to setup Transformation on a Transactional Publication.
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
Subscribe to:
Posts (Atom)