June 10, 2010 at 9:21 am
I've been tasked to create a INSERT/UPDATE trigger for SQL Server 2000 to fire off the same INSERT/UPDATE statement to a corresponding table on a SQL Server 2005 with the same data. I've got the trigger to execute no problem. The question I have is this...
How do I pass the executed line of T-SQL code to a variable so I can then run it within my trigger?
In other words, if someone runs the following code:
"INSERT INTO dbo.Email ( Recipient, Sender) VALUES ('rec@home.com', 'sender@nowhere.com')"
how would I assign, or pass, that "INSERT INTO..." statement to a variable so I can execute it to another server?
Or is there another way to get the same results as to what I am trying above? I'm open to suggestions.
Thanks!
June 10, 2010 at 9:31 am
John Waclawski (6/10/2010)
I've been tasked to create a INSERT/UPDATE trigger for SQL Server 2000 to fire off the same INSERT/UPDATE statement to a corresponding table on a SQL Server 2005 with the same data. I've got the trigger to execute no problem. The question I have is this...How do I pass the executed line of T-SQL code to a variable so I can then run it within my trigger?
In other words, if someone runs the following code:
"INSERT INTO dbo.Email ( Recipient, Sender) VALUES ('rec@home.com', 'sender@nowhere.com')"
how would I assign, or pass, that "INSERT INTO..." statement to a variable so I can execute it to another server?
Or is there another way to get the same results as to what I am trying above? I'm open to suggestions.
Thanks!
I think there are few ways to go about this;
1. have the trigger directly update the other server. this assumes a linked server exists to the other server. downside: if the other server is down/network issues cause the insert to fail, the trigger rols back.
--inside the trigger on the local Email table
INSERT INTO RemoteLinkedServer.Databasename.dbo.Email ( Recipient, Sender)
SELECT Recipient, Sender FROM INSERTED
2. create a job that compares the two tables on a schedule, and inserts/updates any differences between the two. that way, if the job fails, even 40 times in a row, it will be auto-fixed the next time it runs successfully. bad thing is linked servers are SLOW
INSERT INTO RemoteLinkedServer.Databasename.dbo.Email ( Recipient, Sender)
SELECT myLocal.Recipient, myLocal.Sender
FROM Email myLocal
LEFT OUTER JOIN RemoteLinkedServer.Databasename.dbo.Email MyAlias
ON myLocal.Recipient = MyAlias.Recipient
AND myLocal.Sender = MyAlias.Sender
WHERE MyAlias.Recipient IS NULL--does not exist
3. an SSIS project to do the same as above.
Lowell
June 10, 2010 at 9:46 am
Lowell (6/10/2010)
I think there are few ways to go about this;
1. have the trigger directly update the other server. this assumes a linked server exists to the other server. downside: if the other server is down/network issues cause the insert to fail, the trigger rols back.
--inside the trigger on the local Email table
INSERT INTO RemoteLinkedServer.Databasename.dbo.Email ( Recipient, Sender)
SELECT Recipient, Sender FROM INSERTED
2. create a job that compares the two tables on a schedule, and inserts/updates any differences between the two. that way, if the job fails, even 40 times in a row, it will be auto-fixed the next time it runs successfully. bad thing is linked servers are SLOW
INSERT INTO RemoteLinkedServer.Databasename.dbo.Email ( Recipient, Sender)
SELECT myLocal.Recipient, myLocal.Sender
FROM Email myLocal
LEFT OUTER JOIN RemoteLinkedServer.Databasename.dbo.Email MyAlias
ON myLocal.Recipient = MyAlias.Recipient
AND myLocal.Sender = MyAlias.Sender
WHERE MyAlias.Recipient IS NULL--does not exist
3. an SSIS project to do the same as above.
Thanks, Lowell. I'll give your suggestions a go. I appreciate the time you took to come up with this.
As for the linked servers being slow. I know...I totally agree. The fortunate thing about this whole situation is if any of these tables get updated more then 2-3 a month, that's "busy". I'm almost half tempted to forgo the whole trigger aspect & go for the backup, restore on a weekly basis. Or perhaps do a daily compare of the databases, if their different, update the new one with the original.
With this information in mind, what ideas would you have regarding this?
June 10, 2010 at 10:57 am
another option would be Merge Replication, now that i'm thinking about it;
changes would get shipped out on a regular basis, and once set up, you do not have to fiddle with it any more. I'd think that might be the best option of all.
It's actually very easy to set up, I've only done it on the same server between two different databases, but it worked great for me (master zipcode database merging to other db's when changes occur)
Lowell
June 10, 2010 at 11:11 am
Lowell (6/10/2010)
another option would be Merge Replication, now that i'm thinking about it;changes would get shipped out on a regular basis, and once set up, you do not have to fiddle with it any more. I'd think that might be the best option of all.
It's actually very easy to set up, I've only done it on the same server between two different databases, but it worked great for me (master zipcode database merging to other db's when changes occur)
Is a Merge Replication available in SQL Server 2000?
June 10, 2010 at 11:13 am
yes it is; as a matter of fact, here's something at codeproject that has a screenshot-by-screenshot, step by step example:
http://www.codeproject.com/KB/database/MergeReplication.aspx
Lowell
June 10, 2010 at 2:38 pm
Lowell (6/10/2010)
yes it is; as a matter of fact, here's something at codeproject that has a screenshot-by-screenshot, step by step example:http://www.codeproject.com/KB/database/MergeReplication.aspx
I found out for this particular server, we're using SQL Server 2000 Standard Edition which doesn't have Replication available to us.
I know in SQL Server 2005 there's a function called "EVENTDATA" that we use for triggers that we're able to pass the executing T-Sql statement to a variable. Which is exactly what we're looking for. Is there something similar to this in SQL Server 2000?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply