在SQL Server下數(shù)據(jù)庫鏈接的使用

1、在SQL Server下數(shù)據(jù)庫鏈接的使用收藏此頁 打印作者：天極論壇2009-02-05 內(nèi)容導(dǎo)航：在SQL Server下數(shù)據(jù)庫鏈接的使用 第1頁： 在SQL Server下數(shù)據(jù)庫鏈接的使用 文本Tag: SQL Server 數(shù)據(jù)庫 微軟 【IT168 技術(shù)文檔】某些情況下：我們希望在一個 SQL Server 下訪問另一個 sqlserver 數(shù)據(jù)庫上的數(shù)據(jù)，或者訪問其他 Oracle 數(shù)據(jù)庫上的數(shù)據(jù)，要想完成這些操作，我們首要的是創(chuàng)建數(shù)據(jù)庫鏈接。 數(shù)據(jù)庫鏈接能夠讓本地的一個 SQLserver 登錄用戶映射到遠(yuǎn)程的一個數(shù)據(jù)庫服務(wù)器上，并且像操作本地數(shù)據(jù)庫一樣。那么怎么創(chuàng)建數(shù)據(jù)庫鏈接呢？

2、我現(xiàn)在有兩種方法可以實現(xiàn)。 第一種：在 SQL Server 企業(yè)管理器中，建立，這個比較簡單的，首先在安全性-數(shù)據(jù)庫鏈接 上點右鍵，在出現(xiàn)的菜單中點“新建數(shù)據(jù)庫鏈接”，然后會彈出一個界面，需要我們填寫的有：鏈接服務(wù)器(這是一個名字，自己根據(jù)情況自行定義，以后就要通過他進(jìn)行遠(yuǎn)程訪問了)，提供程序名稱(這個就是選擇數(shù)據(jù)驅(qū)動， 根據(jù)數(shù)據(jù)庫類型來選擇，不能亂選，否則鏈接不上的)，數(shù)據(jù)源(對于 SQL Server 就是遠(yuǎn)程數(shù)據(jù)庫服務(wù)器的主機(jī)名或者IP,對于 oracle 就是在 Oracle net config 中配置的別名)，安全上下文用戶和口令(也就是遠(yuǎn)程服務(wù)器的用戶和口令)。 第二種：利用系

3、統(tǒng)存儲過程 創(chuàng)建一個 SQL Server 對 sqlserver 的數(shù)據(jù)庫鏈接： 以下為引用的內(nèi)容：exec sp_addlinkedserver link_northsnow, SQLOLEDB,遠(yuǎn)程服務(wù)器主機(jī)名或域名或ip地址 exec sp_addlinkedsrvlogin link_northsnow, false,null,用戶名,用戶口令 創(chuàng)建一個 SQL Server 對 Oracle 的數(shù)據(jù)庫鏈接： 以下為引用的內(nèi)容：exec sp_addlinkedserver link_ora, Oracle, MSDAORA, Oracle 數(shù)據(jù)庫服務(wù)器別名 exec sp_addl

4、inkedsrvlogin link_ora, false, sa, 用戶名, 用戶口令 有了數(shù)據(jù)庫鏈接我們就可以使用了。對于 sqlserver 和 Oracle 中的使用方法是有區(qū)別的。 對于 SQL Server： 以下為引用的內(nèi)容：create view v_lhsy_user as select * from link_northsnow.lhsy.dbo.sys_user select * from v_lhsy_user 其中 lhsy 為遠(yuǎn)程的數(shù)據(jù)庫名 sys_user 為表名 對于 Oracle： 以下為引用的內(nèi)容：create view vvv as select * fr

5、om link_ora.NORTHSNOW.SYS_USER select * from vvv; 其中 northsnow 為遠(yuǎn)程 Oracle 數(shù)據(jù)庫服務(wù)器的一個用戶名，SYS_USER 為該用戶在該服務(wù)器上的一個表，要非常注意的是：數(shù)據(jù)庫鏈接(link_ora)后面有兩個點(.)，再往后面必須全部大寫，查詢的對象一般為表格或者 視圖，不能查詢同義詞。 要想刪除數(shù)據(jù)庫鏈接，也有兩種方法，一種方法是在企業(yè)管理器中操作。另一種是用系統(tǒng)存儲過程： exec sp_dropserver 數(shù)據(jù)庫鏈接名稱,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 如何設(shè)置最 Sybase 數(shù)據(jù)庫服務(wù)器鏈接的服務(wù)器，并解決可能出現(xiàn)的問題概要本文分步介紹了如何設(shè)置運行 Microsoft SQL Server 的計算機(jī)上的 Sybase 數(shù)據(jù)庫服務(wù)器鏈接服務(wù)器。

52、此外，本文包含設(shè)置 Sybase 數(shù).本文分步介紹了如何設(shè)置運行 Microsoft SQL Server 的計算機(jī)上的 Sybase 數(shù)據(jù)庫服務(wù)器鏈接服務(wù)器。 此外，本文包含設(shè)置 Sybase 數(shù)據(jù)庫服務(wù)器鏈接服務(wù)器時可能出現(xiàn)的問題的基本故障排除步驟?；氐巾敹嗽O(shè)置要 Sybase 數(shù)據(jù)庫服務(wù)器的鏈接服務(wù)器若要設(shè)置鏈接服務(wù)器 Sybase 數(shù)據(jù)庫服務(wù)器，請按照下列步驟操作：1. 登錄到運行 SQL Server 在計算機(jī)上。2. 安裝在 Sybase 客戶端軟件在計算機(jī)上的。3. 若要連接 Sybase 數(shù)據(jù)庫服務(wù)器，請在該計算機(jī)上安裝適當(dāng)?shù)尿?qū)動程序。 可以使用 Microsoft OLE D

53、B Provider for ODBC 連接到 Sybase 數(shù)據(jù)庫服務(wù)器。請注意 您還可以使用第三方驅(qū)動程序或提供程序連接到 Sybase 數(shù)據(jù)庫服務(wù)器。 如果您使用第三方驅(qū)動程序或提供程序，必須與第三方供應(yīng)商聯(lián)系的任何問題發(fā)生與驅(qū)動程序或提供程序。4. 您安裝 Sybase 客戶端軟件之后，重新啟動計算機(jī)。5. 確保您可以成功連接到 Sybase 數(shù)據(jù)庫服務(wù)器通過在步驟 2 中安裝的 Sybase 客戶端軟件。6. 啟動 SQL 查詢分析器，然后運行以下 Transact-SQL 語句設(shè)置到 Sybase 數(shù)據(jù)庫服務(wù)器的鏈接服務(wù)器。請注意 此腳本使用 Microsoft OLE DB Provider for ODBC Sybase 連接到 ODBC 驅(qū)動程序。 替換相應(yīng)鏈接的服務(wù)器名稱、 系統(tǒng) 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