救命！ SQL Server错误 - [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]无效的对象名称... [英] Help! SQL Server error - [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name ...

问题描述

亲爱的，


在带有SP3的Win2000服务器上，我试图从VB6访问SQL Server 7.0

数据库，TestDB通过SQL Server ODBC系统DSN使用ADO

2.7。在SQL Server企业管理器中，有一个名为Tester的登录名。

在其属性窗口中，否是服务器角色。被分配，但它的

数据库访问被设置为TestDB。此登录也是作为TestDB的

用户进行的。 withpublic，db_datareader和db_datawriter

被选为数据库角色成员资格。我正在尝试的所有表格

来访问TestDB是在Tester下创建的。


我的代码如下：


设置conn =新ADODB.Connection

conn.Open" DSN = TestDSN; UID = Tester; PWD = test"


设置cmd =新ADODB.Command

cmd.ActiveConnection = conn

cmd.CommandText = SQL


set rs = cmd.Execute（）


如果我设置了SQL类似于SELECT * FROM tbl_test，我总是

得到错误-2147217865说[Microsoft] [ODBC SQL Server

驱动程序] [SQL Server]无效的对象名称tbl_test。如果我将SQL设置为

SELECT * FROM Tester.tbl_test，则一切都正常运行。请问任何人

请告知为什么第一个SQL不起作用？或者在其他

字样中，为什么我必须在表名与其所有者前缀，而DB

连接已经在该所有者名称下进行？提前致谢。


Tracy

Dear all,

On Win2000 server with SP3, I am trying to access a SQL Server 7.0
database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO
2.7. In SQL Server Enterprise Manager, there is a login named "Tester".
In its property window, NO "Server Roles" was assigned but its
"Database Access" was set to "TestDB". This login was also made as the
user of "TestDB" with "public", "db_datareader" and "db_datawriter"
selected as its "Database role membership". All the tables I am trying
to access in "TestDB" were created under "Tester".

My code is like:

Set conn = New ADODB.Connection
conn.Open "DSN=TestDSN;UID=Tester;PWD=test"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = SQL

set rs = cmd.Execute()

If I set the SQL to something like "SELECT * FROM tbl_test", I always
get an error of "-2147217865" saying "[Microsoft][ODBC SQL Server
Driver][SQL Server] Invalid object name tbl_test". If I set the SQL to
"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyone
please kindly advise why the first SQL is not working? Or in other
words, why must I prefix the table name with its owner while the DB
connection is already made under that owner name? Thanks in advance.

Tracy

推荐答案

我可能原因是你的ODBC DSN设置为集成安全性

而不是SQL身份验证。然后忽略连接字符串中的用户ID，并使用Windows身份验证进行连接。找不到对象

因为当前用户没有'测试''。


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP


" FreeOperator" <一个****** @ yahoo.com>在消息中写道

news：11 ********************** @ o13g2000cwo.googlegr oups.com ...

I likely cause is that your ODBC DSN is setup for integrated security
instead of SQL authentication. The user id in the connection string is then
ignored and the connection is made using Windows authentication. The object
isn''t found because the current user isn''t ''Tester''.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

亲爱的，

在带有SP3的Win2000服务器上，我试图通过SQL Server ODBC系统DSN从VB6访问SQL Server 7.0数据库TestDB使用ADO
2.7。在SQL Server企业管理器中，有一个名为Tester的登录名。
在其属性窗口中，否是服务器角色。被分配但是它的数据库访问权限被设置为TestDB。此登录也是作为TestDB的
用户进行的。 withpublic，db_datareader选择db_datawriter和db_datawriter作为其数据库角色成员资格。我试图在TestDB中访问的所有表格。在Tester下创建。

我的代码如下：

设置conn =新ADODB.Connection
conn.OpenDSN = TestDSN; UID = Tester; PWD = test"

设置cmd =新的ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = SQL

设置rs = cmd.Execute（）

如果我将SQL设置为类似SELECT * FROM tbl_test的东西，我总是会收到错误-2147217865。说[Microsoft] [ODBC SQL Server
驱动程序] [SQL Server]无效的对象名称tbl_test。如果我将SQL设置为
SELECT * FROM Tester.tbl_test，则一切都正常运行。请问任何人
请告知为什么第一个SQL不起作用？或者换句话说，为什么我必须在表名与其所有者前缀，而DB
连接已经在该所有者名下进行？提前致谢。

Tracy

Dear all,

On Win2000 server with SP3, I am trying to access a SQL Server 7.0
database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO
2.7. In SQL Server Enterprise Manager, there is a login named "Tester".
In its property window, NO "Server Roles" was assigned but its
"Database Access" was set to "TestDB". This login was also made as the
user of "TestDB" with "public", "db_datareader" and "db_datawriter"
selected as its "Database role membership". All the tables I am trying
to access in "TestDB" were created under "Tester".

My code is like:

Set conn = New ADODB.Connection
conn.Open "DSN=TestDSN;UID=Tester;PWD=test"

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = SQL

set rs = cmd.Execute()

If I set the SQL to something like "SELECT * FROM tbl_test", I always
get an error of "-2147217865" saying "[Microsoft][ODBC SQL Server
Driver][SQL Server] Invalid object name tbl_test". If I set the SQL to
"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyone
please kindly advise why the first SQL is not working? Or in other
words, why must I prefix the table name with its owner while the DB
connection is already made under that owner name? Thanks in advance.

Tracy

嗨丹，


谢谢你您的回复，但我选择了SQL身份验证对于

我的系统ODBC DSN。我想在SQL Server 7.0中用户

权限设置一定有问题。只是不知道它是什么:-（


谢谢，

Tracy

Hi Dan,

Thank you for your kind reply, but I''ve picked "SQL authentication" for
my system ODBC DSN. I guess It must be something wrong with the user
permission settings in SQL Server 7.0. Just don''t know what it is :-(

Thanks,
Tracy

您的SQL Server是否配置为允许SQL Server和Windows？这个

也将强制Windows身份验证而不是SQL身份验证。


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP


" ; FreeOperator"< an ****** @ yahoo.com>写在留言中

新闻：11 ******************* ***@l41g2000cwc.googlegr oups.com ...

Is your SQL Server configured to allow both SQL Server and Windows? This
will also force Windows authentication instead of SQL auth.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"FreeOperator" <an******@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

嗨Dan，

感谢您的回复，但我选择了SQL身份验证。对于我的系统ODBC DSN。我想SQL Server 7.0中的用户
权限设置一定有问题。只是不知道它是什么:-(
谢谢，
Tracy

Hi Dan,

Thank you for your kind reply, but I''ve picked "SQL authentication" for
my system ODBC DSN. I guess It must be something wrong with the user
permission settings in SQL Server 7.0. Just don''t know what it is :-(

Thanks,
Tracy

这篇关于救命！ SQL Server错误 - [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]无效的对象名称...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！