July 29, 2003 at 3:30 am
Does anyone know whether SET IDENTITY_INSERT
ON is not allowed on a linked server? I know there are issues with @@IDENTITY, and we're having problems turning off the identity property on the linked server prior to performing an insert.
Any help would be appreciated - please let me know if I've not included sufficient info or posted to the wrong forum.
Many thanks in advance,
Andrew
July 29, 2003 at 6:28 am
Could be a permissions issue. Does the executing user have appropriate permissions to the table on the linked server?
July 29, 2003 at 6:40 am
The following works OK for me (SQL7 SP4)
To insert one row
SELECT * FROM OPENQUERY(CCC03,'SELECT NULL SET IDENTITY_INSERT [database]..ON INSERT INTO [database]..
(COL1,COL2) VALUES (value1,value2) SET IDENTITY_INSERT [database]..
OFF ')
To insert multiple rows
SELECT * FROM OPENQUERY(CCC03,'SELECT NULL SET IDENTITY_INSERT [database]..ON ')
SELECT * FROM OPENQUERY(CCC03,'SELECT NULL INSERT INTO [database]..(COL1,COL2) VALUES (value1,value2) ')
SELECT * FROM OPENQUERY(CCC03,'SELECT NULL INSERT INTO [database]..(COL1,COL2) VALUES (value1,value2) ')
SELECT * FROM OPENQUERY(CCC03,'SELECT NULL SET IDENTITY_INSERT [database]..OFF ')
Far away is close at hand in the images of elsewhere.
Anon.
July 29, 2003 at 6:43 am
David,
What function is the 'SELECT NULL' performing at the beginning of your statement?
Thanks,
Andrew
July 29, 2003 at 6:46 am
You cannot use OPENQUERY without a SELECT. The SELECT NULL fools sql server and it executes the whole query.
Far away is close at hand in the images of elsewhere.
Anon.
July 29, 2003 at 6:49 am
Another possiblity is to create a proc in the linked server that does all the work (SET IDENTITY_INSERT etc) and call that proc to do the insert
EXEC server.database..proc ID,cola,colb....
Far away is close at hand in the images of elsewhere.
Anon.
July 29, 2003 at 6:52 am
David,
Good info on the OPENQUERY stuff; wasn't aware of that.
Hunta,
I would tend to lean more towards David's suggestion of a stored procedure for maintenance sake.
July 29, 2003 at 7:13 am
I agree that this should be done with stored procedures. It's to maintain a total of four environments (live, uat, sys-test and dev), so I was hoping to have the SP's on a single environment rather than having to maintain the same SP's in four places. I guess it's not the end of the world though.
July 29, 2003 at 7:17 am
I do all my procs thru script files. Compile into dev, test and if all OK compile into uat and live. I still only have one script but different environments. Makes changing and testing easier.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply