SQL模式-dbo.< tablename>必须去 [英] SQL schemas - dbo.<tablename> has to go

问题描述

我对SQL还是很陌生，所有与我类似的相关问题实际上都无法说明.我的问题是这样的: 我们拥有广泛使用的管理软件，可以使用MDB或SQL. 前一段时间，我将MDB迁移到新的SQL Server实例，此设置已经运行了一段时间.我不再具有Access数据库所具有的一些缺点.

I'm still pretty new to SQL and all the related issues that resemble mine really don't clarify anything. My problem is this: We have a widely used piece of administration software, which can either use MDB or SQL. Some time ago, I migrated the MDB to a new SQL server instance, this setup has been running smoothly for some time now. I no longer had some of the drawbacks that an Access database has.

现在，我们正在使用的新软件将连接到同一数据库以追加数据.也可以将其设置为MDB或SQL模式. 问题是，当该软件解决方案设置为SQL模式时，它找不到表，因为它们都具有dbo.作为前缀.现在，它代替tbl(tablename)在Management Studio中读取dbo.tbl(tablename). 如果我将数据库转换回MDB格式，则表可以正常读取，并且该新软件解决方案可以在MDB模式下正常读取表. 这让我很沮丧，因为这种线程中的每个线程都解释了有关SQL模式和所有权的一些内容，但是都非常模糊. 我认为我是否可以删除dbo.这个新的软件解决方案可以从数据库中所有对象的前缀开始，再次找到表，而主要的管理软件也应如此.

Now, a new piece of software we're using connects to that same database to append data. It can also be set to either MDB or SQL mode. The problem is, when that software solution is set to SQL mode, it can't find the tables because they all have dbo. as a prefix. Instead of tbl(tablename) it now reads dbo.tbl(tablename) in the management studio. If I convert the database back to MDB format, the tables read normally and this new software solution can read the tables fine in MDB mode. This is frustrating me as every thread on this kind of explains some stuff about SQL schemas and ownership but it's all pretty vague. I think if I can remove the dbo. prefix from all the objects in the database, this new software solution can find the tables again, and the main administrative software should too.

请注意，就SQL而言，我是新手

Please note that I'm a novice as far as SQL goes

推荐答案

每个表都必须属于SQL Server中的一个架构.传统的假设是dbo是默认值，这听起来像是您的迁移脚本将所有表都分配给了它.您需要确保连接到SQL数据库的帐户可以访问dbo模式(通常它将拥有该模式).

Every table has to belong to a schema in SQL Server; a legacy assumption is that dbo is the default, which it sounds like your migration script assigned all of the tables to. You need to make sure that the account which is connecting to your SQL database has access to the dbo schema (typically it will own that schema).

这篇关于SQL模式-dbo.< tablename>必须去的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！