Copy data from a server on a insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Copy data from a server on a insert

Author

Topic

klegrand
Starting Member

29 Posts

Posted - 2006-12-29 : 08:21:05

I have to copy data from a database to another server on an insert of record in a table.
I'm using a linked server for this. I have writen a sp to execute this. If I execute the sp manually, then the data is copied to the other server.
If I use the same sp in a insert trigger, then it doesn't work.

Any solutions for this ???

Code of insert trigger :


ALTER TRIGGER [Local_ProductionScheduleToMESS_Insert]
   ON  [dbo].[Local_ProductionScheduleToMESS]
   AFTER INSERT
AS 
Declare 
	@ID int

Select @ID = PPS_ID from inserted

Exec spLocal_ProductionScheduleToMESS @ID

Code of sp :

ALTER PROCEDURE [dbo].[spLocal_ProductionScheduleToMESS]
	@ID int
AS

--Send the Planned product run to MESS
insert into [mec_pri_02\mec_pri_02].Depal.dbo.tbl_ProficyProductSchedule (StartTime, EndTime, ProductionLine, ProcessOrder, Brandcode, Quantity, Status)
	select StartDate, EndDate, ProductionLine, ProcessOrder, Brandcode, Quantity, Status
	from dbo.Local_ProductionScheduleToMESS
	where PPS_ID = @ID

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-30 : 01:10:25

The trigger is written in such a fashion so that if you try to insert more than 1 row in the triggered table, it will still only copy one row per insert command even if that insert puts a 1000 new rows into the table. The trigger needs to be rewritten to do a setbased insert (your proc is RBAR all the way) by using a JOIN to the INSERTED table.

("RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row")

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-30 : 01:15:50

Something like this...


  ALTER TRIGGER Local_ProductionScheduleToMESS_Insert
     ON dbo.Local_ProductionScheduleToMESS
  AFTER INSERT
     AS 

 INSERT INTO [mec_pri_02\mec_pri_02].Depal.dbo.tbl_ProficyProductSchedule 
        (StartTime, EndTime, ProductionLine, ProcessOrder, Brandcode, Quantity, Status)
 SELECT StartDate, EndDate, ProductionLine, ProcessOrder, Brandcode, Quantity, Status
   FROM INSERTED

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources