2

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')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kimpo
  • 5,835
  • 4
  • 26
  • 30

2 Answers2

5

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?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Ah yes was editing my question while your answer was posted. It's all starting to make sense now i just right clicked a table in management studio select top 1000 and in fact it prefixes the table name with the old schema (kimpo54321).I tried clicking in the Logins folder in the menu tree in management studio and tried to add the kimpo54321 schema in user mappings. I get an error message "cannot alter the user dbo" and when i try adding both schemas nothing happens, the default schema is still dbo. How can this be changed? – Kimpo Jul 15 '11 at 18:54
0

This is likely an issue where the Web App's user needs to be re-added to the restored database. Certain users do not maintain their permissions when a database is restored onto a different sql server.

Brent D
  • 898
  • 5
  • 16