July 21, 2015 at 5:42 am
Hi all,
I have the following scenario.
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
BEGIN
DECLARE @Value1 VARCHAR(25), @Value2INT;
SELECT @Value1 = Value, @Value2 = OtherValue
FROM inserted;
EXEC [WIBBLE].Target.dbo.Proc
@Param1 = @Value1, @Param2 = @Value2;
END
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
I've googled a bit, and while I can find a number of people with the same issue no effective fix or definitive statement that it can't be done. Does anyone know whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
If not, wandering further down the path labelled Grim Kludge, anyone know if this would work in a <Shudder> CLR Trigger? Other than that, the only thing I can think of is replication to the remote and updating based on the changes to the replicated table - I have no idea which I reckon's worse ... :pinch:
If anyone has any less horrible suggestions, they'd also be welcome
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 21, 2015 at 9:18 am
andrew gothard (7/21/2015)
Hi all,I have the following scenario.
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
BEGIN
DECLARE @Value1 VARCHAR(25), @Value2INT;
SELECT @Value1 = Value, @Value2 = OtherValue
FROM inserted;
EXEC [WIBBLE].Target.dbo.Proc
@Param1 = @Value1, @Param2 = @Value2;
END
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
I've googled a bit, and while I can find a number of people with the same issue no effective fix or definitive statement that it can't be done. Does anyone know whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
If not, wandering further down the path labelled Grim Kludge, anyone know if this would work in a <Shudder> CLR Trigger? Other than that, the only thing I can think of is replication to the remote and updating based on the changes to the replicated table - I have no idea which I reckon's worse ... :pinch:
If anyone has any less horrible suggestions, they'd also be welcome
Aside from the issue at hand your trigger has a MAJOR flaw. It assumes there will only ever be one row in inserted. If you do a search for remote stored procedure calls you will find dozens of explanations of how to make it work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2015 at 10:00 am
Sean Lange (7/21/2015)
andrew gothard (7/21/2015)
Hi all,I have the following scenario.
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
BEGIN
DECLARE @Value1 VARCHAR(25), @Value2INT;
SELECT @Value1 = Value, @Value2 = OtherValue
FROM inserted;
EXEC [WIBBLE].Target.dbo.Proc
@Param1 = @Value1, @Param2 = @Value2;
END
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
I've googled a bit, and while I can find a number of people with the same issue no effective fix or definitive statement that it can't be done. Does anyone know whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
If not, wandering further down the path labelled Grim Kludge, anyone know if this would work in a <Shudder> CLR Trigger? Other than that, the only thing I can think of is replication to the remote and updating based on the changes to the replicated table - I have no idea which I reckon's worse ... :pinch:
If anyone has any less horrible suggestions, they'd also be welcome
Aside from the issue at hand your trigger has a MAJOR flaw. It assumes there will only ever be one row in inserted. If you do a search for remote stored procedure calls you will find dozens of explanations of how to make it work.
That's absolutely true - this wouldn't have been the code that'd go into production, it's basically just proof of concept; "Can I get it to work" at this point, then add multi record capability and error handling. Except, I can't.
Certainly, as you say, the remote call per-se is no problem at all. Took about ten minutes to get that up and running. However, once it's put into a trigger it just breaks.
I can sort it out using replication to a table on the remote table with a trigger on that doing the update - but apart from that solution being as ugly as a bulldog chewing a wasp - I'm the only SQL Server DBA here. If there's a tweak to be made urgently and I'm unavailable, they can tweak code no problem. But expecting them to reconfigure Replication is too big an ask, which is why I'm hoping someone's sucessfully managed to update remotely using a trigger - preferably T-SQL CLR if absolutely necessary (possibly - not too keen).
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 21, 2015 at 12:40 pm
andrew gothard (7/21/2015)
Sean Lange (7/21/2015)
andrew gothard (7/21/2015)
Hi all,I have the following scenario.
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
BEGIN
DECLARE @Value1 VARCHAR(25), @Value2INT;
SELECT @Value1 = Value, @Value2 = OtherValue
FROM inserted;
EXEC [WIBBLE].Target.dbo.Proc
@Param1 = @Value1, @Param2 = @Value2;
END
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
I've googled a bit, and while I can find a number of people with the same issue no effective fix or definitive statement that it can't be done. Does anyone know whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
If not, wandering further down the path labelled Grim Kludge, anyone know if this would work in a <Shudder> CLR Trigger? Other than that, the only thing I can think of is replication to the remote and updating based on the changes to the replicated table - I have no idea which I reckon's worse ... :pinch:
If anyone has any less horrible suggestions, they'd also be welcome
Aside from the issue at hand your trigger has a MAJOR flaw. It assumes there will only ever be one row in inserted. If you do a search for remote stored procedure calls you will find dozens of explanations of how to make it work.
That's absolutely true - this wouldn't have been the code that'd go into production, it's basically just proof of concept; "Can I get it to work" at this point, then add multi record capability and error handling. Except, I can't.
Certainly, as you say, the remote call per-se is no problem at all. Took about ten minutes to get that up and running. However, once it's put into a trigger it just breaks.
I can sort it out using replication to a table on the remote table with a trigger on that doing the update - but apart from that solution being as ugly as a bulldog chewing a wasp - I'm the only SQL Server DBA here. If there's a tweak to be made urgently and I'm unavailable, they can tweak code no problem. But expecting them to reconfigure Replication is too big an ask, which is why I'm hoping someone's sucessfully managed to update remotely using a trigger - preferably T-SQL CLR if absolutely necessary (possibly - not too keen).
Ahh gotcha. You need to look at distributed transactions. There are a few pieces that need to be configured for this to work correctly. It has been quite some time since I set one up so I don't want to offer advice from my fuzzy memory.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply