July 25, 2002 at 8:44 am
We have a third party software package for our payroll system and it uses its own SQL Server. I am wanting to transfer the data from this server to our production server. For example, the payroll server has the employees name, phone number, working location, etc. I need this information on our production server. I have set up a stored procedure using the appropriate link server commands and it works fine on its own. As soon as I try to call the stored procedure from an update trigger, I get the following error message. "The procedure 'sp_addlinkedserver' cannot be executed within a transaction." Is this the wrong approach? Or is there a work around to this error? Thanks in advance for the help.
July 25, 2002 at 9:46 am
Hey,
Can the Linked Server not be a permanent? Or is this an issue for your company?
If you could have a permanent Linked Server, this would resolve your issue
If you don't want that permanently you could do it this way...don't know if this a good method or not...comments are appriecate :o)
Create a DTS to transfer the data between the two servers.
Within the SP run xp_cmdshell and call the DTSRUN utility. example...
CREATE PROC MyTestProc
AS
EXEC master.dbo.xp_cmdshell 'dtsrun /S<Server> /U<UserID> /P<Password> /N<DTSName>'
GO
Of course, others could well have a better solution! :o)
Clive Strong
July 25, 2002 at 12:10 pm
Yes they can be linked permanently. How is this established? If the connection is ever lost between the two servers, how would you recomend to re-establish the connection?
July 25, 2002 at 4:28 pm
Doing this in a trigger essentially implements 2 phase commit between two servers, what DTC does. If your 2nd server is unavailable or the network is slow, you will not be able to do transactions on server1. A BAD idea in most cases.
I'd implement replication OR some job to move the data periodically (every hour, day, minute, whatever).
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 25, 2002 at 5:02 pm
Agree, I've got one dependency left like that it and it truly sucks. Replication is the easiest way to resolve it, the downside is that changes to the source table become a little more difficult.
Andy
July 26, 2002 at 7:47 am
Thanks for the replies. This is what I thought. Currently my approach is to create the appropriate triggers to update a local table (audit table) and then use a stored procedure. This stored procedure can link to the other server and I can fire it off perodically. The problem with using replication is that the payroll server is still on SQL 7.0 and the production server is SQL 2000. I also do not want to risk any security holes by setting up replication.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply