日期:2014-05-18  浏览次数:20538 次

在sql server 2005中使用openrowset报错??
我在想SQL   Server   2005里测试一下openrowset   个函数,
先到外围配置管理器里把响应openrowset/opendatasource我勾打上,然后再写如下代码
select   *   from  
OPENROWSET
(
'SQLOLEDB ',
'Provider=SQLOLEDB.1;Integrated   Security=SSPI;Persist   Security   Info=False;
Initial   Catalog=AdventureWorksDW;Data   Source=localhost ',
'SELECT   TOP   1000   *   FROM   vTargetMail '
)  


但是系统提示报错
OLE   DB   provider   "SQLNCLI "   for   linked   server   "(null) "   returned   message   "Invalid   authorization   specification ".


OLE   DB   provider   "SQLNCLI "   for   linked   server   "(null) "   returned   message   "Invalid   connection   string   attribute ".


Msg   7399,   Level   16,   State   1,   Line   1
The   OLE   DB   provider   "SQLNCLI "   for   linked   server   "(null) "   reported   an   error.   Authentication   failed.

Msg   7303,   Level   16,   State   1,   Line   1
Cannot   initialize   the   data   source   object   of   OLE   DB   provider   "SQLNCLI "   for   linked   server   "(null) ".

不知哪位老兄碰到过个这种问题,是如何解决的???




------解决方案--------------------
在本机相同的库中执行
SELECT a.*
FROM OPENROWSET( 'SQLNCLI ', 'Server=127.0.0.1;Trusted_Connection=yes; ',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name ') AS a
--结果
Executive General and Administration Executive 16
Executive General and Administration Facilities and Maintenance 14
Executive General and Administration Finance 10
Executive General and Administration Human Resources 9
Executive General and Administration Information Services 11
Inventory Management Purchasing 5
Inventory Management Shipping and Receiving 15
---执行你的会报同样的错,说明参数有误

------解决方案--------------------
select * from
OPENROWSET
(
'SQLOLEDB ',
'SINODIGI-BAE950 '; 'sa '; 'sa ',AdventureWorks.HumanResources.Department
) as a
已经通过。
--
select * from
OPENROWSET
(
'SQLOLEDB ',
'SINODIGI-BAE950 '; 'sa '; 'sa ',库名.所有者.表名
) as a

------解决方案--------------------
--你的语句可改为
select * from
OPENROWSET
(
'SQLOLEDB ',
'localhost '; 'sa '; 'sa密码 ', 'SELECT TOP 1000 * FROM AdventureWorksDW.dbo.vTargetMail '
) as a