Remote insert question - SQL Server Forums

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)

Remote insert question

Author

Topic

ACALVETT
Starting Member

34 Posts

Posted - 2006-06-21 : 20:34:39

I have an issue i need to understand and have not been able to find an answer yet.

If i do a

insert into server.db.dbo.remotetable
select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the destination server where as if the insert into references a local table only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert?

To test this i've supplied some very simple code. Just creare source on source server and destination and log tbl on remote server. Setup a linked server and run the remote insert then do a local insert and look at the log.

All advise gratefully received!

Cheers

Andrew

CREATE TABLE [dbo].[source] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the source table
insert into source values ('MYSERVER','1/1/2000')
insert into source values ('MYSERVER','1/2/2000')
insert into source values ('MYSERVER','1/3/2000')

--Run the insert

insert into destinationsrv.destdb.dbo.destination
select * from source

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-22 : 00:02:02

I would delete/disable the triggers first, then give it a shot.

Always handy to strip out all the bits that complicate the issue....

HTH,

Tim

ACALVETT
Starting Member

34 Posts

Posted - 2006-06-22 : 06:04:16

Thanks for the reply.

I need the trigger in live and am stumped as to why i get 3 insert events when run remotely and only 1 when run locally.

If anyone can point me in the right direction to understand this or clues as to how to resolve.

Thanks

A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 06:29:24

Can you post the triggers here?

As Timmy says, need to look at the complicated stuff.

Peter Larsson
Helsingborg, Sweden

ACALVETT
Starting Member

34 Posts

Posted - 2006-06-22 : 06:34:00

I've included a trigger in my first post for the destination table. Using this trigger will exactly recreate the problem i`m experiencing.

Cheers

A

dano33
Starting Member

1 Post

Posted - 2006-07-08 : 13:10:11

I don't have a trigger in the mix, but I'm experiencing the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2006-07-08 : 21:34:13

Very interesting! I've never known this feature! Just checked it and
Really, ONE insert on "our" side fires remote trigger for several times (= the number of affected rows).

======================
Let me hug & kiss you...

ACALVETT
Starting Member

34 Posts

Posted - 2006-07-10 : 19:32:26

I went on a mission to try and find the answer for this question but unfortunately i did not find a definaitive answer and had to settle for "This is how it is" and "Change my code to pull the data" instead of pushing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources