SQL Server Performance

Cannot perform SET operation on a TABLE SYNONYM

Discussion in 'SQL Server 2005 General Developer Questions' started by arunyadav, Jun 23, 2009.

  1. arunyadav New Member

    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.....
  2. FrankKalis Moderator

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

    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 ;'
  4. FrankKalis Moderator

    Thanks for the workaround! [:)]

Share This Page