SQL Server triggers, stored procedure operations remote database Insert data, resolve server existing problems

Recently got a small project, it is not very complicated, need to backup some data of one database to another library, not local, possibly other database on the network, think about, use stored procedures and triggers. is not very complicated, first I need to operate the remote database, so I wrote a stored procedure:

CREATE PROCEDURE sendinfotoremotedb @CardNo varchar, @CardStyle varchar, @userId varchar, @UserName varchar ( @passDate datetime, @inOut intasbegin--exec sp_addlinkedserver<span style= "White-space:pre" ></span> [Email protected]= ' Ims_srv_lnk ', <span style= ' white-space:pre ' ></span>[email protected]= ', <span Style= "White-space:pre" ></span>[email protected]= ' msdasql ', <span style= "White-space:pre" ></ Span>[email protected]= ' Driver={sql SERVER}; server=xxxx.com; uid=xxx; Pwd=xxx;database=xxx ' INSERT into Ims_srv_lnk. IMSDBBAK.dbo.tb_record (Cardno,cardstyle,userid,username,passdate,inout) VALUES (@CardNo, @CardStyle, @userId, @ UserName, @passDate, @inOut) Endgo

is actually the operation of remote database. Insert the data, then this way the local database writes a trigger:

Create Trigger Tgr_passrecord_insert on  Passcheckrecord for   Insert--insert trigger   as   --Define variable    declare        @CardNo varchar (), @CardStyle varchar, @userId varchar, @UserName varchar, @passDate datetime, @inOut int   --Query the inserted table for the inserted record information  Select @CardNo = Cardno, @CardStyle = Cardstyle, @userId =userid, @UserName = UserName, @passDate =passdate, @inOut =inoutfrom inserted;set @CardNo = @CardNo; Set @CardStyle = @CardStyle; set @userId = @userId; set @UserName = @UserName; set @passDate = @passDate; Set @inOut = @inOut;  exec [dbo]. [Sendinfotoremotedb]    @CardNo = @CardNo,      @CardStyle = @CardStyle,     @userId = @userId,   @UserName = @UserName,   @passDate = @ Passdate,   @inOut = @inOutprint ' Send message successfully! ';

then the result was first executed successfully, but the second time prompted Ims_srv_lnk to exist and could not be executed. Back to think about the code inside the connection server that piece deleted, the result is OK.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Server triggers, stored procedures manipulate remote databases to insert data, and resolve problems that exist on the server