在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 AdministrationExecutive16
Executive General and AdministrationFacilities and Maintenance14
Executive General and AdministrationFinance10
Executive General and AdministrationHuman Resources9
Executive General and AdministrationInformation Services11
Inventory ManagementPurchasing5
Inventory ManagementShipping and Receiving15
---执行你的会报同样的错，说明参数有误
[解决办法]
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
