CODEWITHSUNDEEP
sql-server-2008
Karthik
Karthik

Reputation: 15

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\SQLEXPRESS].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

Upvotes: 1

Views: 4304

Answers (2)

JNK
JNK

Reputation: 65187

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.

Upvotes: 4

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

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;

Upvotes: 3

Related Questions