INSERT INTO from linked server

Question

INSERT INTO from linked server

Alan Paull

SSC Veteran

Points: 211
More actions
April 20, 2006 at 7:22 am

#91630

Hi
I am trying to use the INSERT INTO statement inserting values into a database on a local server from a database on a server linked for the purpose. After a number of inserts have been carried out the linked server is dropped. I am carrying this process out using the ado connection object in asp. The sql is
INSERT INTO userResponse
(questionnaireStructureId, questionnaireId, questionId, userVersionId, thisValue, dateStamp)
SELECT questionnaireStructureId, questionnaireId, questionId, userVersionId, thisValue, dateStamp
FROM lnksvrver001bwtest12620060420.ver001bwtest126.dbo.userResponse
The other insert statements are successful but this one fails. However, if i run the sql in Query Analyser the insert is successful. There is an FOR INSERT trigger attached to the table i am trying to insert into. The trigger operates successfully when data is either manually entered or entered via an insert query in query analyser.
Can anyone offer any ideas of how i can investigate this further as this has now taken two days of my time and i really need to move on.
Cheers

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Did you try only the "select...." to see if it returns any rows ?!

You say that "other insert statements are successful"...could you post the code for both..one of the successful ones and the one that fails...maybe there's a difference between the two that someone here can catch !!

**ASCII stupid question, get a stupid ANSI !!!**

Jeet SSCommitted Points: 1536 More actions · Answer 2

So is the insert failing because the trigger is failing?

What is the error? It might be a permission issue?

Thanks Jeet

Alan Paull SSC Veteran Points: 211 More actions · Answer 3

The problem turned out to be a syntax error in the trigger. I don't know why this would manifest itself in failing when run through ado but not when run through query analyser, but thats the way it goes, ho hum.

Thanks for the help and time

Cheers

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

That sounds quite strange, indeed. It would be nice if you could post up the before and after versions of the trigger so we could become more educated as to the nature of this strangeness.

Glad it's working.

thx jg

robertcaustin Mr or Mrs. 500 Points: 551 More actions · Answer 5

Hi, This is a post contrary to the information I have read in this post so please don't take exception.

I have a SQL Express 2008 server that is read from a SQL Std 2005 server in a different location. Basically the 2008 server(s) is collecting data, and the 2005 server polls each 2008 instance and pulls the data from those servers into a consolidated database.

This has presented a number of interesting problems along the way, but the biggest to date is informing the remote (2008 Express) server its data has been read so it can keep its data tables small.

I initially ran the update (from 2005 to linked 2008 Express)

Update [remoteIP].[LinkDB].[dbo].[TransferList] Set UpdateDate = GetDate() Where iKey = @Processed And UpdateDate IS NULL

This proved to take 40 seconds each time.

So I tried to add a line to a table on the remote server, and use a trigger on the remote server to update the TransferList. Strangly this failed:

INSERT INTO [remoteIP].[LinkDB].[dbo].[TransferList_Processed] (iKey, UpdateDate) Select @Processed, GetDate()

This statement resulted in an error

OLE DB provider "SQLNCLI" for linked server "IP,port" returned message "Cursor operation conflict".

There was no support on this statement so I tried creating a table on the remote server that contained a single row, and added an Update Trigger that applied any change to the [TransferList] table.

Update [remoteIP].[LinkDB].[dbo].[TransferList_Acknowledge] Set iKey = @Processed

The trigger on this table takes iKey and updates the [TransferList] table

The interesting part is this update takes 2 seconds, whereas the conditional update took 40 seconds.

My question:

Any idea why the INSERT INTO [RemoteTable] failed?

Any idea why the Conditional update would take 20 times longer (the updated table is only 20,000 rows so it is not a processing issue)?