在SQL Server下数据库链接的使用

1、在SQL Server下数据库链接的使用收藏此页 打印作者：天极论坛2009-02-05 内容导航：在SQL Server下数据库链接的使用 第1页： 在SQL Server下数据库链接的使用 文本Tag: SQL Server 数据库 微软 【IT168 技术文档】某些情况下：我们希望在一个 SQL Server 下访问另一个 sqlserver 数据库上的数据，或者访问其他 Oracle 数据库上的数据，要想完成这些操作，我们首要的是创建数据库链接。 数据库链接能够让本地的一个 SQLserver 登录用户映射到远程的一个数据库服务器上，并且像操作本地数据库一样。那么怎么创建数据库链接呢？

2、我现在有两种方法可以实现。 第一种：在 SQL Server 企业管理器中，建立，这个比较简单的，首先在安全性-数据库链接 上点右键，在出现的菜单中点“新建数据库链接”，然后会弹出一个界面，需要我们填写的有：链接服务器(这是一个名字，自己根据情况自行定义，以后就要通过他进行远程访问了)，提供程序名称(这个就是选择数据驱动， 根据数据库类型来选择，不能乱选，否则链接不上的)，数据源(对于 SQL Server 就是远程数据库服务器的主机名或者IP,对于 oracle 就是在 Oracle net config 中配置的别名)，安全上下文用户和口令(也就是远程服务器的用户和口令)。 第二种：利用系

3、统存储过程 创建一个 SQL Server 对 sqlserver 的数据库链接： 以下为引用的内容：exec sp_addlinkedserver link_northsnow, SQLOLEDB,远程服务器主机名或域名或ip地址 exec sp_addlinkedsrvlogin link_northsnow, false,null,用户名,用户口令 创建一个 SQL Server 对 Oracle 的数据库链接： 以下为引用的内容：exec sp_addlinkedserver link_ora, Oracle, MSDAORA, Oracle 数据库服务器别名 exec sp_addl

4、inkedsrvlogin link_ora, false, sa, 用户名, 用户口令 有了数据库链接我们就可以使用了。对于 sqlserver 和 Oracle 中的使用方法是有区别的。 对于 SQL Server： 以下为引用的内容：create view v_lhsy_user as select * from link_northsnow.lhsy.dbo.sys_user select * from v_lhsy_user 其中 lhsy 为远程的数据库名 sys_user 为表名 对于 Oracle： 以下为引用的内容：create view vvv as select * fr

5、om link_ora.NORTHSNOW.SYS_USER select * from vvv; 其中 northsnow 为远程 Oracle 数据库服务器的一个用户名，SYS_USER 为该用户在该服务器上的一个表，要非常注意的是：数据库链接(link_ora)后面有两个点(.)，再往后面必须全部大写，查询的对象一般为表格或者 视图，不能查询同义词。 要想删除数据库链接，也有两种方法，一种方法是在企业管理器中操作。另一种是用系统存储过程： exec sp_dropserver 数据库链接名称,droplogins 。This step-by-step article describes

6、how to set up a linked server from a computer that is running Microsoft SQL Server to an Oracle database and also provides basic troubleshooting steps for common errors you may experience when you set up a linked server to Oracle.Back to the topSteps to set up a linked server to OracleBack to the to

7、p1. You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.2. Install the driver you want on the computer that is running SQL Server. Microsoft only supports Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle. I

8、f you use a third-party provider or a third-party driver to connect to Oracle, you must contact the respective vendor for any problems that you may experience by using their provider or driver.3. If you use Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle, consider the follo

9、wing:o Both the OLE DB provider and the ODBC driver that are included with Microsoft Data Access Components (MDAC) require SQL*Net 2.3.x, or a later version. You must install the Oracle 7.3.x client software, or a later version, on the client computer. The client computer is the computer that is run

10、ning SQL Server.o Make sure that you have MDAC 2.5, or a later version, installed on the computer that is running SQL Server. With MDAC 2.1, or with an earlier version, you cannot connect to databases that use Oracle 8.x or a later version.o To enable MDAC 2.5, or later versions, to work with Oracle

11、 client software, the registry must be modified on the client computer that is running SQL Server as indicated in the following table. Microsoft Windows NT,Oracle Microsoft Windows 95,Client Windows 98, and Windows 98 SE Microsoft Windows 2000 -7.x HKEY_LOCAL_MACHINESOFTWARE HKEY_LOCAL_MACHINESOFTWA

12、RE MicrosoftTransactionServer MicrosoftMSDTCMTxOCI Local ComputerMy Computer OracleXaLib=xa73.dll OracleXaLib=xa73.dll OracleSqlLib=SQLLib18.dll OracleSqlLib=SQLLib18.dll OracleOciLib=ociw32.dll OracleOciLib=ociw32.dll 8.0 HKEY_LOCAL_MACHINESOFTWARE HKEY_LOCAL_MACHINESOFTWARE MicrosoftTransaction Se

13、rver MicrosoftMSDTCMTxOCI Local ComputerMy Computer OracleXaLib=xa80.dll OracleXaLib=xa80.dll OracleSqlLib=sqllib80.dll OracleSqlLib=sqllib80.dll OracleOciLib=oci.dll OracleOciLib=oci.dll 8.1 HKEY_LOCAL_MACHINESOFTWARE HKEY_LOCAL_MACHINESOFTWARE MicrosoftTransaction Server MicrosoftMSDTCMTxOCI Local

14、 ComputerMy Computer OracleXaLib=oraclient8.dll OracleXaLib=oraclient8.dll OracleSqlLib=orasql8.dll OracleSqlLib=orasql8.dll OracleOciLib=oci.dll OracleOciLib=oci.dll4. Restart the computer that is running SQL Server after you install the Oracle client software.5. On the computer that is running SQL

15、 Server, set up a linked server by using the following script.- Adding linked server (from SQL Server Books Online):/* sp_addlinkedserver server = server , srvproduct = product_name , provider = provider_name , datasrc = data_source , location = location , provstr = provider_string , catalog = catal

16、og*/ EXEC sp_addlinkedserver Ora817Link, Oracle, MSDAORA, oracle817- Adding linked server login:/* sp_addlinkedsrvlogin rmtsrvname = rmtsrvname ,useself = useself ,locallogin = locallogin ,rmtuser = rmtuser ,rmtpassword = rmtpassword*/ EXEC sp_addlinkedsrvlogin Ora817Link, FALSE,NULL, scott, tiger-

17、Help on the linked server:EXEC sp_linkedserversEXEC sp_helpserverselect * from sysserversNote If you use Microsoft ODBC Driver for Oracle, you can use the datasrc parameter to specify a DSN name. For a DSN-less connection, the provider string is supplied through the provstr parameter. With Microsoft

18、 OLE DB Provider for Oracle, use the Oracle server alias that is configured in the TNSNames.Ora file for the datasrc parameter. For more information, see the sp_addlinkedserver topic in SQL Server Books Online.Back to the topCommon error messages and how to troubleshoot themImportant This section, m

19、ethod, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the

20、 registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base: 322756 ( ) How to back up and restore the registry in WindowsYou can use either of the following two methods to r

21、etrieve extended information about any error that you experience when you execute a distributed query. Method 1In Query Analyzer, run the following code to turn on trace flag 7300.DBCC Traceon(7300) Method 2Capture the OLEDB Errors event that is located in the Errors and Warnings event category in S

22、QL Profiler. The error message format is the following:Interface:Method failed with hex-error code.You can look up hex-error code in the Oledberr.h file that is included with the MDAC Software Development Kit (SDK).The following is a list of ten common error messages that may occur, together with in

23、formation about how to troubleshoot the error message.Note If you are using SQL Server 2005, these error messages may be slightly different. However, the error IDs of these error messages are same as them in SQL Server 2000. Therefore, you can identify them by the error IDs.Note For performance-rela

24、ted issues, search SQL Server Books Online for the Optimizing Distributed Queries topic. Message 1Error 7399: OLE DB provider %ls reported an error. %lsTurn on trace flag 7300 or use SQL Profiler to capture the OLEDB Errors event to retrieve extended OLEDB error information. Message 2aORA-12154: TNS

25、:could not resolve service nameMessage 2bThe Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installationFor more information about how to resolve Oracle connectivity

26、 issues, click the following article number to view the article in the Microsoft Knowledge Base: 259959 ( ) Techniques to debug connectivity issues to an Oracle server using the ODBC driver and OLE DB provider Message 3Error 7302: Could not create an instance of OLE DB provider MSDAORAMake sure that

27、 the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle. If the registration fails, reinstall Microsoft Data Access Components (MDAC). For more information about MDAC, vi

28、sit the following Microsoft Developer Network (MSDN) Web site: ( Note If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following met

29、hods.o Method 1Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name:HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerProvidersProviderNameo Method 2Set the Allow InProcess option directly t

30、hrough SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box. Message 4Error 7303: Could not initialize data source object of OLE DB provider MSDAORA. OLE/DB provider returned message: ORA-01017: invalid usernam

31、e/password; logon denied OLE DB error trace OLE/DB Provider MSDAORA IDBInitialize:Initialize returned 0x80040e4d.This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly.

32、 Also, verify that you have specified the correct parameters for the linked server configuration. Message 5Error 7306: Could not open table %ls from OLE DB provider MSDAORA. The specified table does not exist. OLE/DB provider returned message: Table does not exist.OLE/DB provider returned message: O

33、RA-00942: table or view does not exist OLE DB error trace OLE/DB Provider MSDAORA IOpenRowset:OpenRowset returned 0x80040e37: The specified table does not exist.Error 7312: Invalid use of schema and/or catalog for OLE DB provider %ls. A four-part name was supplied, but the provider does not expose t

34、he necessary interfaces to use a catalog and/or schema.Error 7313: Invalid schema or catalog specified for provider %ls.Err 7314: OLE DB provider %ls does not contain table %lsIf you receive these error messages, a table may be missing in the Oracle schema or you may not have permissions on that tab

35、le. Verify that the schema name has been typed by using uppercase. The alphabetical case of the table and of the columns should be as specified in the Oracle system tables.On the Oracle side, a table or a column that is created without double quotation marks is stored in uppercase. If the table or t

36、he column is enclosed in double quotation marks, the table or the column is stored as is.The following call shows if the table exists in the Oracle schema. This call also shows the exact table name.sp_tables_ex table_server=Ora817Link, table_schema=your_schema_nameFor more information about error me

37、ssage 7306, click the following article number to view the article in the Microsoft Knowledge Base: 240340 ( ) SQL distributed query with Oracle causes Could not open table error Message 6Error 7413: Could not perform a Windows NT authenticated login because delegation is not available.Msg 18456, Le

38、vel 14, State 1, Line 1 Login failed for user .The following information is from SQL Server Books Online: This error message indicates that a distributed query is being attempted for a Microsoft Windows authenticated login without an explicit login mapping. In an operating-system environment in whic

39、h security delegation is not supported, Windows NT authenticated logins need an explicit mapping to a remote login and password created using sp_addlinkedsrvlogin. Message 7Error 7354: OLE DB provider MSDAORA supplied invalid metadata for column %ls. The data type is not supported.If you receive thi

40、s error message, you may be experiencing the bug that is described in the following Microsoft Knowledge Base article: 243027 ( ) FIX: Numeric column in Oracle causes error 7354 Message 8Error 7356: OLE DB provider MSDAORA supplied inconsistent metadata for a column. Metadata information was changed

41、at execution time.If your linked server query uses an Oracle view, you may be experiencing the problem that is described in the following Microsoft Knowledge Base article:251238 ( ) Distributed queries return error 7356 with MSDAORA Message 9Error 7391: The operation could not be performed because t

42、he OLE DB provider MSDAORA does not support distributed transactions. OLE DB error trace OLE/DB Provider MSDAORA ITransactionJoin:JoinTransaction returned 0x8004d01bVerify that the OCI versions are registered correctly as described earlier in this article. Note If the registry entries are all correc

43、t, the MtxOCI.dll file is loaded. If the MtxOCI.dll file is not loaded, you cannot perform distributed transactions against Oracle by using Microsoft OLE DB Provider for Oracle or by using Microsoft ODBC Driver for Oracle. If you are using a third-party provider and you receive Error 7391, verify th

44、at the OLE DB provider that you are using supports distributed transactions. If the OLE DB provider does support distributed transactions, verify that the Microsoft Distributed Transaction Coordinator (MSDTC) is running. Message 10Error 7392: Could not start a transaction for OLE DB provider MSDAORA

45、. OLE DB error trace OLE/DB Provider MSDAORA ITransactionLocal:StartTransaction returned 0x8004d013: ISOLEVEL=4096.The following information is from SQL Server Books Online:The OLE DB provider returned error 7392 because only one transaction can be active for this session. This error indicates that

46、a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects

47、 of the data modification statement while continuing with the transaction.If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB provider. Therefore, execute SET XACT_ABORT ON before you execute data modification statements against remote tables in an implici

48、t or explicit transaction. Do this in case the OLE DB provider that you are using does not support nested transactions.Back to the topREFERENCESFor more information, click the following article numbers to view the articles.For more information, click the following article numbers to view the article

49、s in the Microsoft Knowledge Base: 244661 ( ) Limitations of Microsoft Oracle ODBC driver and OLE DB provider 259959 ( ) Techniques to debug connectivity issues to an Oracle server using the ODBC driver and OLE DB provider 239719 ( ) Supportability of the Microsoft ODBC driver/OLE DB provider for Or

50、acle w.r.t Oracle 8.x 193893 ( ) Information about using Oracle with Microsoft Transaction Server and COM+ components 191168 ( ) Error -2147168246 (8004d00a) failed to enlist on calling objects transaction For more information about how to use a linked server together with DB2, click the following a

51、rticle numbers to view the articles in the Microsoft Knowledge Base: 218590 ( ) Configuring data sources for the Microsoft OLE DB provider for DB2 216428 ( ) Configuring Microsoft ODBC driver for DB2 如何设置最 Sybase 数据库服务器链接的服务器，并解决可能出现的问题概要本文分步介绍了如何设置运行 Microsoft SQL Server 的计算机上的 Sybase 数据库服务器链接服务器。

52、此外，本文包含设置 Sybase 数.本文分步介绍了如何设置运行 Microsoft SQL Server 的计算机上的 Sybase 数据库服务器链接服务器。 此外，本文包含设置 Sybase 数据库服务器链接服务器时可能出现的问题的基本故障排除步骤。回到顶端设置要 Sybase 数据库服务器的链接服务器若要设置链接服务器 Sybase 数据库服务器，请按照下列步骤操作：1. 登录到运行 SQL Server 在计算机上。2. 安装在 Sybase 客户端软件在计算机上的。3. 若要连接 Sybase 数据库服务器，请在该计算机上安装适当的驱动程序。 可以使用 Microsoft OLE D

53、B Provider for ODBC 连接到 Sybase 数据库服务器。请注意 您还可以使用第三方驱动程序或提供程序连接到 Sybase 数据库服务器。 如果您使用第三方驱动程序或提供程序，必须与第三方供应商联系的任何问题发生与驱动程序或提供程序。4. 您安装 Sybase 客户端软件之后，重新启动计算机。5. 确保您可以成功连接到 Sybase 数据库服务器通过在步骤 2 中安装的 Sybase 客户端软件。6. 启动 SQL 查询分析器，然后运行以下 Transact-SQL 语句设置到 Sybase 数据库服务器的链接服务器。请注意 此脚本使用 Microsoft OLE DB Provider for ODBC Sybase 连接到 ODBC 驱动程序。 替换相应链接的服务器名称、 系统 DSN、 用户名称和密码的 Sybase Linked Server Name、 Sybase System DSN、 User Name，和 Strong Password。 7. - Adding linked server:8. exec sp_addlinkedserver server = Sybase Linked Server Name ,9. srvproduct = Sybase,10. provider = MSDASQL,11. da