Hello All,

I have 02 DBs on different servers. I am facing some problems in writing after insert triggers (when ever a record is inserted in SVR1.DB1.dbo.TABLE1 similar records have to be inserted in SVR2.DB2.dbo.TABLE2) . I created a linked server but now, when I tried to give 04 part name [SVR2.DB2.dbo.TABLE2] (in the insert into statement) it said 4 part name in not valid, give 03 part name only. To overcome this, I created a synonym for the remote table. Now the bigger problem is, this remote table (SVR2.DB2.dbo.TABLE2) has an identity column and I have to take the value of identity column of SVR1.DB1.dbo.TABLE1. When I am trying to SET IDENTITY_INSERT ON on the table synonym it says : Msg 8105, Level 16, State 1, Line 2
'dbo.DB_COMPANY_LOCAL' is not a user table. Cannot perform SET operation.

Is there a solution or,workaround for this problem? Please advice ASAP.

Thanks in advance..... 

Are you sure you need to refer to dbo.DB_COMPANY_LOCAL as your synonym and not only to DB_COMPANY_LOCAL?

Refering to synonym without the owner name didn't do any good.

Anyways, I have found the solution... SET IDENTITY_INSERT doesn't work on remote server/table directly. So, i tried this workaround which worked for me..

exec [linkedserver].MyDatabase.dbo.sp_executesql N'set identity_insert MyDatabase.dbo.MyTable on ;
insert into MyDatabase.dbo.MyTable (identity_clumn, other_columns, ... ) values (1, ''test'', ...) ;
set identity_insert MyDatabase.dbo.MyTable off ;'

Thanks for the workaround!