1

I did spend an hour searching before I posted about this problem.

The table does exist and I can query that table and I can see the resultset but when i try to execute from Visual Studio 2008 I get the below error:

Cannot find the object "Products" because it does not exist or you do not have permissions

Why does this error occur and what should I do to resolve it?

using (System.Data.SqlClient.SqlCommand cmd = connection.CreateCommand() as System.Data.SqlClient.SqlCommand)
{
   cmd.CommandText = "SET IDENTITY_INSERT Products ON";
   cmd.CommandType = CommandType.Text;
   cmd.ExecuteNonQuery();
}

I tried specifying dbo.Products and before executing I also tried use dbname but that did not help.

John Pick
  • 5,562
  • 31
  • 31
Nick Kahn
  • 19,652
  • 91
  • 275
  • 406
  • Are you the owner of the table or do you have `ALTER` permissions on the table? Those are permission prerequisites to execute `SET IDENTITY_INSERT` on a table. –  Mar 05 '12 at 02:38
  • Does the ConnectionString specify the right database? – John Pick Mar 05 '12 at 02:41
  • @Pat: yes its pointing to the correct database, and as i said if i connect to a different table it works without any issue – Nick Kahn Mar 05 '12 at 02:45
  • @Shark: i able to run the `SET IDENTITY_INSERT ` on query and i dont get any errors – Nick Kahn Mar 05 '12 at 02:46
  • Perhaps the Products table was created in a different SQL Server instance than the one Visual Studio is connecting to, e.g., stage instead of development. – John Pick Mar 05 '12 at 02:49
  • Also check for typos in table names. I noticed you spelled Products as Prodcuts at one point. That could have happened when you created the table as well. (not trying to be a pain here, just trying to leave no stone unturned) – John Pick Mar 05 '12 at 02:51
  • Thanks John i looked at it and no typos i will have to talk to my dba – Nick Kahn Mar 05 '12 at 02:58

2 Answers2

3

after spent few hours and lost few hairs i found that the problem was in the connection strings (but this is still did not convence me) and as i said in question i was not having any issues but suddenly its pop-up and i havent change anything (who knows what dba have changed)

so I try tweak my connection string instead, and it does make a string to see if it makes a difference: and it does.

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

the only thing i have added to my connection string is Trusted_Connection= Yes

PS: SET IDENTITY_INSERT remains on for your session until you turn it off, and it can only be on for one table at a time

Hope this will help others...

Nick Kahn
  • 19,652
  • 91
  • 275
  • 406
  • Thanks, for people wants to know what is trusted connection https://stackoverflow.com/questions/1250552/what-is-a-trusted-connection – ValidfroM May 04 '18 at 09:36
0

The error is happening because you haven't set a database context yet. If you added "use (dbname) go " (with your database name) to the start of the query it would work.

[edit] Sorry didn't spot that you'd tried that. Maybe your connecting to the wrong server? Try changing the query in code to "create table argh (argh int)" and check it fails the second time. Then track down where it created it!

Peter Wishart
  • 11,600
  • 1
  • 26
  • 45