SQL Server链接服务器到Microsoft Access [英] SQL Server Linked Server to Microsoft Access
问题描述
我尝试通过执行以下操作访问Microsoft Access 2003表来利用SQL Server 2008中的链接服务器.
I have tried to make use of linked servers in SQL Server 2008 by doing the following to access a Microsoft Access 2003 Table.
EXEC sp_addlinkedserver access1t, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb'
EXEC sp_addlinkedsrvlogin access1t, FALSE, Null, Admin, Null
GO
CREATE VIEW TI001APCE1265 AS SELECT * FROM access1t...Table1
但是,我得到了错误:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1t" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure TI001APCE1265, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1t".
我的访问数据库上没有密码/用户名,但查看我了解的文档,我必须像上面一样使用sp_addlinkedsrvlogin.我也没有登录也尝试过.
There is no password/username on my access database but looking at the documents I understood I had to use the sp_addlinkedsrvlogin like the above. I have tried it without a login either.
我仍然遇到相同的错误-怎么回事?!
Bit I still get the same error - what is going?!
谢谢
Array([0] => Array([0] => 42000 [SQLSTATE] => 42000 [1] => 7303 [code] => 7303 [2] => [Microsoft] [SQL Server本机客户端10.0] [SQL Server]不能 初始化的数据源对象 OLE DB提供程序 用于链接的"Microsoft.Jet.OLEDB.4.0" 服务器"access1265293168". [信息] => [Microsoft] [SQL Server本机客户端10.0] [SQL Server]不能 初始化的数据源对象 OLE DB提供程序 用于链接的"Microsoft.Jet.OLEDB.4.0" 服务器"access1265293168". )[1] => 数组([0] => 01000 [SQLSTATE] => 01000 [1] => 7412 [code] => 7412 [2] => [Microsoft] [SQL Server本机客户端10.0] [SQL Server] OLE DB 提供程序"Microsoft.Jet.OLEDB.4.0" 链接服务器"access1265293168" 返回消息未指定的错误". [消息] => [Microsoft] [SQL Server Native Client 10.0] [SQL Server] OLE DB 提供程序"Microsoft.Jet.OLEDB.4.0" 链接服务器"access1265293168" 返回消息未指定的错误". ))
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 7303 [code] => 7303 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168". [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168". ) [1] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 7412 [code] => 7412 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168" returned message "Unspecified error". [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "access1265293168" returned message "Unspecified error". ) )
推荐答案
您是否尝试过使用命名参数?:
Have you tried with named parameters?:
EXEC sp_addlinkedserver
@server = 'access1t',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\tester.mdb'
GO
可能您只需要用单引号'access1t'
括起来的服务器名称即可.
It may be you just need your server name in single quotes 'access1t'
.
[您登录的用户是否有权访问C:驱动器的根目录? ]
[ Does your logged in user have permission to access the root of C: drive? ]
这篇关于SQL Server链接服务器到Microsoft Access的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!