Search code examples
asp.netsql-serversql-server-2008exceptioninvalid-object-name

"Invalid object name" after restoring SQL Server 2008 database

I'm switching my web host and backed up my database. Due to some restriction with my new host I could not restore the .bak file and had to send to them so they would restore it. Once they had restored it, I ran my application I get this

System.Data.SqlClient.SqlException: Invalid object name "<table name>"

whenever I try to query a table from the application. However, I have no problems logging in through management studio with same user name and password and querying the tables.

I'm running a mvc 3 site with SQL Server 2008

Does anyone know why I might getting these exceptions when trying to run my application?

EDIT:

Some more information:

the user name I was using in my old db was Kimpo54321 so all tables I had created got prefixed like this Kimpo54321. so I tried adding it to the very first query in my web app so it would be SELECT * FROM Kimpo54321.<tablename> and the query passed without the exception.

Now I did not have to prefix each table name with this earlier in my application and I don't want to apply it to all my queries. Is there a way to fix this?

EDIT:

I ran this to get a alter schema line for each table and changed everything to dbo and its finally working. thnx aaron for pointing me in the right direction finding the answer

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name 
    FROM sys.Objects o 
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id 
    WHERE s.Name = 'yourschema'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
Solution

  • Are you referencing the schema (e.g. dbo.table vs. table)? It is possible that your user at the new host has a different default schema than at your old host. How are you "querying the tables" - right-clicking and selecting one of the options, or using the exact same query issued by the application?