在SQL SERVER 2008 R2下用Windows
身份认证的登录名创设了一个访问ORACLE数据库的链接服务器xxxxx,测试成功,木有问题,不过任何登录名使用该链接服务器时,报如下错误:
消息 7302,级别
16,状态 1,第 1 行
Server Error in ‘/Engtek’ Application.
Cannot create an
instance of OLE DB provider “OraOLEDB.Oracle” for linked server
“xxxxxx”.
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “DB1”. The provider supports the interface, but returns a failure code when it is used.
Description: An unhandled
exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and
where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Cannot obtain the schema
rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for
linked server “DB1”. The provider supports the interface, but returns a
failure code when it is used.
Source Error:
Line 25: return objBusinessBase.GetDataToDataSet("usp_WipProductionSeries_GetAll").Tables[0];
|
Source File:d:\Projects\Engtek\App_Code\System\Wip\WipProductionSeries.cs Line: 25
Stack Trace:
|
Version
Information: Microsoft .NET Framework Version:4.0.30319;
ASP.NET Version:4.0.30319.1
数据库升级至Microsoft SQL Server 2008 R2
64位之后,在此以前链接到SQL2000的主次出错了。的网上到一个解决措施:
On SQL 2000 side run the following script:
代码
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
这儿亟需在“服务器对象”——>“链接服务器”——>“访问接口”下,找到OraOLEDB.Oracle选项,单击右键拔取属性,然后在做客接口选项下勾选“允许进程内”(Allow
inprocess)。即可解决地方问题,有时候,可能需要再度新建此链接服务器。
鬼子给出了一个解决这一个题目的手续,分外详细严俊,几乎面面俱到。能够当做参考学习的好资料:
Ran into this issue where the linked
server would work for users who were local admins on the server, but not
for anyone else. After many hours of messing around, I managed to fix
the problem using the following steps:
1:Run “dcomcnfg.exe”. Navigate to
“Component Services -> Computers -> My Computer -> DCOM
Config”.
2:Open the properties page of
“MSDAINITIALIZE”.
3:Copy the “Application ID” on the
properties page.
4:Close out of “dcomcnfg”.
5:Run “regedit”. Navigate to
“HKEY_CLASSES_ROOT\AppID\{???}” with the ??? representing the
application ID you copied in step #3.
6:Right click the “{???}” folder and
select “Permissions”
7:Add the local administrators group
to the permissions, grant them full control.
8:Close out of “regedit”.
9:Reboot the server.
10:Run “dcomconfig”. Navigate to
“Component Services -> Computers -> My Computer -> DCOM
Config”.
11:Open the properties page of
“MSDAINITIALIZE”.
12:On the “Security” tab, select
“Customize” under “Launch and Activation Permissions”, then click the
“Edit” button.
13:Add “Authenticated Users” and
grant them all 4 launch and activation permissions.
14:Close out of “dcomcnfg”.
15:Find the Oracle install root
directory. “E:\Oracle” in my case.
16:Edit the security properties of
the Oracle root directory. Add “Authenticated Users” and grant them
“Read & Execute”, “List folder contents” and “Read” permissions. Apply
the new permissions.
17:Click the “Advanced Permissions”
button, then click “Change Permissions”. Select “Replace all child
object permissions with inheritable permissions from this object”. Apply
the new permissions.
18:Find the “OraOLEDB.Oracle”
provider in SQL Server. Make sure the “Allow Inprocess” parameter is
checked.
19:Reboot the server.