“无效的对象名称”恢复SQL Server 2008数据库后 [英] "Invalid object name" after restoring SQL Server 2008 database

问题描述

我要切换虚拟主机并备份数据库。由于新主机的某些限制，我无法还原.bak文件，只能发送给他们，以便他们还原。他们恢复了它之后，我运行了我的应用程序

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：无效的对象名称<表名称> ;

每当我尝试从应用程序查询表时。但是，使用相同的用户名和密码通过Management Studio登录并查询表没有问题。

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.

我正在使用SQL Server 2008运行mvc 3站点

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?

编辑：

更多信息：

我在旧数据库中使用的用户名是Kimpo54321，因此我创建的所有表都带有这样的前缀Kimpo54321。因此我尝试将其添加到我的Web应用程序中的第一个查询中，这样它就会是 SELECT * FROM Kimpo54321< tablename> ，并且查询毫无例外地通过了。

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?

编辑：

我运行它来获取一个更改模式行每个表并将所有内容更改为 dbo 并最终运行。 thnx aaron为我指出了正确的答案

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')

推荐答案

您是否在引用架构（例如dbo.table与表）？您在新主机上的用户可能具有与旧主机上不同的默认架构。您如何查询表-右键单击并选择一个选项，还是使用应用程序发出的完全相同的查询？

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?

这篇关于“无效的对象名称”恢复SQL Server 2008数据库后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！