INSERT INTO LINKED SERVER - 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)

INSERT INTO LINKED SERVER

Author

Topic

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-13 : 13:46:20

Is there any way to get this insert to work:

exec [wmg-pm-reports].PracticeManager.dbo.cusRecallIdentityInsertOn
INSERT INTO [wmg-pm-reports].PracticeManager.dbo.recall
SELECT RecallId,Inactive,PatientProfileId,Date,DoctorResourceId,FacilityId,
	552,LastContactDate,1,ApptId,Notes,Created,CreatedBy,LastModified,LastModifiedBy
FROM RECALL
exec [wmg-pm-reports].PracticeManager.dbo.cusRecallIdentityInsertOff

I get this error:


Server: Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table '[wmg-pm-reports].[PracticeManager].[dbo].[recall]' because of column 'RecallId'. The user did not have permission to write to the column.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 13:56:05

I presume RecallId has an IDENTITY attribute?

I've had this problem before and, no, I don't think I found a solution that would get
SET INSERT_IDENTITY RemoteTableName ON
into scope.

I expect we stuffed the data into a staging table on the remote server, and then called an Sproc on that serve to physically make the insert (and toggle INSERT_IDENTITY ON/OFF)

Kristen

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-13 : 14:00:02

exec [wmg-pm-reports].PracticeManager.dbo.cusRecallIdentityInsertOn is a stored procedure on that destination server, that does
SET INDENTITY_INSERT ON for that table. This doesn't work?

Kristen
Test

22859 Posts

Posted - 2007-09-13 : 14:11:06

"This doesn't work?"

Correct. Its out of scope for the Insert that follows. (That may not be the correct term, but that's how I would "describe" the scenario)

Kristen

ccrespo
Yak Posting Veteran

59 Posts

Posted - 2007-09-14 : 08:49:18

The point of this insert, was to put it into a trigger and to insert or update a remote table. I have two production databases which are being updated and inserted on regularly. The point of this was to synchronize the data, eg. if update on table, then update the other, and vice versa between both of the servers. I'm thinking I should just use bcp as an export on a trigger for each update on a row for both server/databases and if exists then insert, else update all fields in the other server/database. Just like a file transfer, I wonder how the performance will be.. Anyone have any easier ideas?

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 08:54:43

"Anyone have any easier ideas"

Are you trying to allocate IDs on BOTH databases?

If you are basically entering on one database, and duplicating that row in the second database, then get rid of the IDENTITY property on the column in the second database.

Only other thins I can suggest is generate, and execute, dynamic SQL [on the remote server], so that the IDENTITY_INSERT and INSERT statements are in the same "scope"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources