Linked server with Identity_insert on
I am having a strange problem with linked servers. Let's say I have two SQL servers Server-A and Server-B. Further,
Server-A has a database called db1. If I create a linked server on Server-B to Server-A.db1, everything at
that point works fine. While connected to only Server-B, I can issues queries to tables, views what ever I want within db1.
The problem occurs when I try to insert records in a DB1.table1 from DB2.table2 via linked server. DB1.table1 has an IDENTITY column so I need to fire the query in a format like "SET IDENTIY_INSERT ON Server-A.db1.UserName.Table1;<INSERT QUERY>". When I run this query I get the error like as-
Table 'Server-A.db1.UserName.Table1' does not exist or cannot be opened for SET operation.
Any ideas are welcome......