December 8, 2009 at 12:24 am
I have created synonym for inseting data into some table.
But that table has identity column.
So the problem is I need to set off the identity feature before inserting data and on after finished. I am facing problem while doing so as I am using synonym for inserting data.
I also seen that this SET operation is session dependent.
Is there any other way for the resolve this issue ?
December 8, 2009 at 2:07 am
The real goal of having table synonyms is to allow it in DML statements and no other places.
Hence the above said will not be possible
However, if still this is required i took the workaround
EXEC [linkedserver].MyDatabase.dbo.sp_executesql N'SET IDENTITY_INSERT MyDatabase.dbo.MyTable ON ;
INSERT INTO MyDatabase.dbo.MyTable (identity_col, col2 ) VALUES (1, ''test'') ;
SET IDENITITY_INSERT MyDatabase.dbo.MyTable OFF ;'
Better solution is welcome!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply