sql server 2008 r2 "Cannot find the object because it does not exist or you do not have permissions"

i want to execute sql command with server name. here is some example query

 SELECT * FROM [SYSTEM1\SQLEXP开发者_如何学运维RESS].Sample1.dbo.table1 
 INSERT INTO [SYSTEM1\SQLEXPRESS].Sample1.dbo.table1 values('BALA')

the above two executed successfully. in my table1 id filed is auto increment so when i execute the following command it return the error message

 SET IDENTITY_INSERT [SYSTEM1\SQLEXPRESS].Sample1.dbo.table1 ON
 INSERT INTO [SYSTEM1\SQLEXPRESS].Sample1.dbo.table1 values(2,'KARTHIK')
 SET IDENTITY_INSERT [SYSTEM1\SQLEXPRESS].Sample1.dbo.table1 OFF

Cannot find the object [SYSTEM1\SQLEXPRESS].Sample1.dbo.table1 because it does not exist or you do not have permissions. how to solve this problem

Looks like this is a known issue with linked servers. The workaround is:

exec [Server-SQL].MyDatabase.dbo.sp_executesql N'SET IDENTITY_INSERT 
Sample1.dbo.table1 ON;
INSERT dbo.table1 values(2,''KARTHIK'');
SET Sample1.dbo.table1 OFF;'

Basically run the command as an RPC instead of a direct query.

AFAIK you need to execute the command locally. Why don't you create a stored procedure that does this, then the stored procedure can say:

SET IDENTITY_INSERT dbo.Table1 ON;

And you can call the procedure using:

EXEC [SYSTEM1\SQLEXPRESS].Sample1.dbo.MyProcedure;