June 23, 2011 at 5:29 am
Hi,
1. There is a plan to write a trigger on a table in Server1 which inserts records on a table in linked server Server2.
2. The trigger should work like this
a. If the linked server is up and running it should insert the record there
b. if there is ANY sort of error/exception it should insert records into a another table - which is in local server server1.
Will this work to see if server is up and running or is there a better way?
declare @cmd varchar(200)
declare @DBName sysname
declare @ServerName sysname
declare @RetVal int
set @DBname = 'master' --set this to the database I want to check
set @ServerName = 'SQL11KC1'
set @cmd = 'isql -S ' + @ServerName + ' -E -Q"select 1 from ' + @DBName + '..sysobjects"'
exec @RetVal = master..xp_cmdshell @cmd
if @RetVal <> 0
begin
--INSERT Records in local table in Server1
end
--ELSE
--INSERT Records intotable on Linked server Server2
Also, what if the database is down?
I'm looking in a way that -"If ANY error occurs other than it is on local server Server1 itself, the records should be inserted to local table ELSE to a table on Server2.
Any suggestions would be of much helpful.
Thanks,
KB
June 23, 2011 at 5:38 am
June 23, 2011 at 7:13 am
Jayanth_Kurup (6/23/2011)
I am thinking the trigger should look something likeCREATE TRIGGER employee_insupd
ON employee
FOR INSERT
as
BEGIN
BEGIN TRY
Insert into server2.database.schema.table
select * from inserted
END TRY
BEGIN CATCH
Insert into server1.database.schema.table
select * from inserted
END CATCH
END
Hi Jayanth, thanks for your reply.
I did the same thing earlier, but received the error
TRY Block: INSERT into other server table
OLE DB provider "SQLNCLI10" for linked server "LinkedServerName" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
0
CATCH Block: INSERT into ErrorLog table
Msg 3930, Level 16, State 1, Procedure OnInsert, Line 21
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
I know the transaction to remote has been disabled.
But even for this error too, I need the trigger to insert it on local table while it fails on linked server.
Script I used:
--Local Server table
CREATE TABLE [dbo].Table_Test(
[ProblemID] [int] NOT NULL PRIMARY KEY,
[Batch] [varchar](50) NULL,
[Amount] [money] NULL,
[done] [bit] NOT NULL,
[DoneBy] [varchar](50) NULL,
[DateChecked] [datetime] NULL,
[Description] [varchar](50) NULL,
[DateEntered] [datetime] NULL
) ON [PRIMARY]
GO
--local target table to catch errors with data as well
CREATE TABLE [dbo].Table_Test_local(
[ProblemID] [int] NOT NULL PRIMARY KEY,
[Batch] [varchar](50) NULL,
[Amount] [money] NULL,
[done] [bit] NOT NULL,
[DoneBy] [varchar](50) NULL,
[DateChecked] [datetime] NULL,
[Description] [varchar](50) NULL,
[DateEntered] [datetime] NULL,
ErrorNumber int,
ErrorMessage varchar(4000),
ErrorSeverity int,
ErrorState int,
ErrorLine int,
ErrorProcedure varchar(4000)
) ON [PRIMARY]
GO
--TRIGGER
CREATE TRIGGER dbo.OnInsert ON dbo.Table_Test
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
PRINT 'TRY Block: INSERT into other server table'
INSERT INTO LinkedServerName.DBName.dbo.Table_Test
SELECT * FROM INSERTED
END TRY
BEGIN CATCH
IF XACT_STATE()=-1
BEGIN
PRINT 'ROLLBACK - UnCommitable situation'
ROLLBACK
END
print xact_state()
PRINT 'CATCH Block: INSERT into ErrorLog table'
INSERT INTO dbo.Table_Test_local
SELECT *,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE() FROM INSERTED
--select ERROR_NUMBER()
--select ERROR_Message()
--,ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE()
END CATCH
END
GO
--======================================
--Table on Linked Server(Some database)
CREATE TABLE [dbo].Table_Test(
[ProblemID] [int] NOT NULL PRIMARY KEY,
[Batch] [varchar](50) NULL,
[Amount] [money] NULL,
[done] [bit] NOT NULL,
[DoneBy] [varchar](50) NULL,
[DateChecked] [datetime] NULL,
[Description] [varchar](50) NULL,
[DateEntered] [datetime] NULL
) ON [PRIMARY]
--======================================
GO
--INSERT in local server
insert into Table_Test(ProblemID,Batch,DoneBy,DateChecked,Description)
select 100,'a',user_name(),getdate(),'Test trigger server'
June 23, 2011 at 8:31 am
instead of a trigger, i strongly recommend a scheduled job that migrates the data to the linked server.
if the linked server cannot be connected to(due to username/password, networking issues, or the MSDTC service not running, the trigger will fail, and that means the data will not be isnerted into the tabe because the trigger rolls back, right?
you'll end up with missing data.
a job that runs every x minutes that copies data would not have that problem, and it can migrate just what hasn't moved yet, so if the connection is lost for an hour or something, it would just pick up where it left off.
Lowell
June 23, 2011 at 8:38 am
Lowell (6/23/2011)
instead of a trigger, i strongly recommend a scheduled job that migrates the data to the linked server.if the linked server cannot be connected to(due to username/password, networking issues, or the MSDTC service not running, the trigger will fail, and that means the data will not be isnerted into the tabe because the trigger rolls back, right?
you'll end up with missing data.
a job that runs every x minutes that copies data would not have that problem, and it can migrate just what hasn't moved yet, so if the connection is lost for an hour or something, it would just pick up where it left off.
Hi Lowell,
Thanks for your suggestion. I will consider this technique as well.
On the same line, does that mean the CATCH block will not catch these exceptions?
Is there any alternate way in trigger?
Thanks,
KB
June 23, 2011 at 9:21 am
http://msdn.microsoft.com/en-us/library/aa561924%28v=bts.20%29.aspx
the above link shows how to troubleshoot issues with distributed transaction coordinator
and would have a solution for the trigger based approach. But like Lowel said the job is a more robust approach
August 16, 2011 at 6:09 am
Hi KB,
If you got the solution, could you please share with me.
Because I am is also in same situation. But I don't have option and I should use triggers.
can any one suggest solution?
Thanks in advance
Hareesh
August 16, 2011 at 6:28 am
kbsk333000 (6/23/2011)
Hi,1. There is a plan to write a trigger on a table in Server1 which inserts records on a table in linked server Server2.
...
I wonder what would cause you to plan like that? What is the real issue you're trying to solve?
Auditing? Mirroring? Do you understand the performance implications of such design?
I personally would never consider to have such trigger at all for many reasons and I cannot think of any reason which would make me to "plan" for such trigger.
December 10, 2011 at 12:56 am
I wonder what would cause you to plan like that? What is the real issue you're trying to solve?
Auditing? Mirroring? Do you understand the performance implications of such design?
I personally would never consider to have such trigger at all for many reasons and I cannot think of any reason which would make me to "plan" for such trigger.
In my case I have an ERP system that needs to supply a plant control system with customer, pricing and order information. This information needs to get to the plant in "real time" and the most logical way to do this is to have the update cause the export of data to the other system. If you do not think the trigger should be used for this purpose, what would you use?
December 10, 2011 at 8:03 am
richardkel (12/10/2011)
In my case I have an ERP system that needs to supply a plant control system with customer, pricing and order information. This information needs to get to the plant in "real time" and the most logical way to do this is to have the update cause the export of data to the other system. If you do not think the trigger should be used for this purpose, what would you use?
Richard my suggestion would still be the same; no trigger involved at all.
I'd use staging table that keeps track of primary keys, and a flag that tracks whether it was migrated successfully.
Combined with a scheduled job that attempts to do an insert on the other server; if the insert fails, it will simply retry in the next pass;
pseudocode wise, it'd be something like this: a scheduled job that runs every five minutes or so during the hours of business that data changes might occur.
the advantage is there's no trigger that might roll back accidentially on the Orders Table, all this process does is a few SELECT statements agaisnt existing data, and one local StagingTable that tracks the changes.
--assuming data from four tables need partial migration: orders,orderdetails,Customers,Pricing
--assuming an identity() exists on orders table, all other tables related via FK:
--get our last OrderId we know existed.
DECLARE @LastOrderReviewed int
SELECT @LastOrderReviewed = MAX(OrderID) From StagingTable
--get any new records to be processed.
INSERT INTO StagingTable
SELECT OrderID As OrderID,
'N' AS ProcessedSuccessfullyFlag
from Orders WHERE OrderID > @LastOrderReviewed
--now we have all the orders that need to migrate.
set xact_abort on
begin tran
insert into ERP.dbname.dbo.OrdersToBeProcessed(OrderID,ColumnList)
SELECT
StagingTable.OrderId,
OtherTables.ColumnList FROM StagingTable
INNER JOIN OtherTables ON StagingTable.OrderID = OtherTables.OrderID
WHERE StagingTable.ProcessedSuccessfullyFlag ='N'
insert into ERP.dbname.dbo.OrdersDetails(OrderID,OrderDetailID,ColumnList)
SELECT
StagingTable.OrderId,
orderdetails.orderDetailID,
OtherTables.ColumnList FROM StagingTable
INNER JOIN OtherTables ON StagingTable.OrderID = orderdetails.OrderID
INNER JOIN OtherTables ON StagingTable.OrderID = OtherTables.OrderID
WHERE StagingTable.ProcessedSuccessfullyFlag ='N'
--other inserts as needed.
--if we got this far without a gross error that caused the transaction to rollback,
-- we can set our ProcessedSuccessfullyFlag = 'Y'
UPDATE StagingTable SET ProcessedSuccessfullyFlag = 'Y' WHERE ProcessedSuccessfullyFlag ='N'
COMMIT TRAN
verification stuff/reports can then be run on the ERP side, by simply joining agaisnt the staging table on the other server. I think this is the simplest , cleanest and most resilient method; you have the scheduled job email you if it fails, so you know if it's failing for more than intermittent issues, or due to some unexpected DDL changes(someone adding a new not-null column in ht eERP system, for example)
Lowell
December 10, 2011 at 3:20 pm
Oops duplicate post
December 10, 2011 at 3:27 pm
Lowell (12/10/2011)
Richard my suggestion would still be the same; no trigger involved at all.
I'd use staging table that keeps track of primary keys, and a flag that tracks whether it was migrated successfully.
[p]Lowell, I just want to make sure I understand. The code that posts to the staging table would go into an insert/update trigger? But, because it is a local table without dependencies it is not likely to throw an error unless the database is damaged. This is actually a path I was thinking of following. I've been having issues with triggers and linked servers since people started implementing 2008. [/p]
[p]post by droid[/p]
December 11, 2011 at 9:31 am
Nobody has mentioned this option - which I believe is exactly what it was designed for.
Lookup Service Broker in Books Online. Using a service broker, the broker will be triggered on inserts to the source table and send the data to the linked server.
If access is not available, the entries in the service broker will queue up - and once the connection has been re-established will start processing the entries.
Lowell's suggestion is basically the same as a service broker. Just doing everything manually and setting up a job to run every xx minutes.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 11, 2011 at 9:36 am
I've never found a decent use for it before but this sounds like the perfect use for an "INSTEAD OF" trigger against a view. It would guarantee that any INSERT from any source would be handled properly.
--Jeff Moden
December 11, 2011 at 1:23 pm
[sarcasm]
As a firefighter sysadmin/dba, I cannot but embrace your choice of solution.
( insert in table a server a with a trigger to insert into serverb some table)
This will guarantee my job !
[/sarcasm]
Whenever someone holds a blocking lock on your linked server table, your source server 's operations will come to a halt because your insert transactions will fail !
As others suggested, there are other ways of doing this kind of things.
In my experience, most "must be immediate" requirements aren't that immediately needed at all.
Re-assess this requirement and sell a service broker based solution.
It has the big advantage both involved instances can still be taken offline without immediate downtime of the partner instances application(s)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply