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

Recently, a small project is not very complex, it is necessary to backup some data of one database to another library. Not local, it could be another database on the network. Think about it, using stored procedures and triggers.

is not very complex, first I need to operate the remote database, and then 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

The fact is to operate a remote database. Insert the data. And 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 run successfully, but the second time prompted Ims_srv_lnk to exist and not run. Back to think about the code inside the connection server that piece deleted, the result is OK.

Source: http://bluesnowsoft.com

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