SQL Server 2008 Creating an Oracle Linked server (experience)

If the operating system is 32-bit, there was not much time to create a linked server to Oracle. The main points are:

1. Install the Oracle thin client. At that time I was using a lower version of the "Oracle9i310-client Lite", the installation of the corresponding directory (my is C:\Oracle\ora90\network\ADMIN) under the Tnsnames.ora file. This step is done, and basically the rest of the work is to create a linked server in SQL Server 2008.

2. Create a new linked server. At that time, my Oracle database was 64-bit 11g, although the installation of the "Oracle9i310-client Lite", and eventually successfully created the linked server. "Server Type" is selected "other data sources", Access interface selected "Microsoft OLE DB Provider for Oracle", "Product name" randomly lost a bit of character, no effect; "Data source" Is the use of Tnsnames.ora inside the name of their own set, for example, I set the name is "JSB", copied over to fill this place. "Access interface string" This place is delayed me for a while, at first I put here empty, the link server was also created successfully, but the actual execution of the simplest query is always in the execution, did not see the error and did not see the results. Later, the connection string is written here only to return to the final result! This is the location I set: Data source=jsb;user=jsb123;password=jsb123; Persist Security info=true. The Oracle user name and password are based on your actual situation.

This step is not over, but also to switch the selection page to security, select Use this security context to establish a connection (M):, and then enter the Oracle user name and password. The last point "OK" button, to this end, to Oracle's linked server created successfully! By the way, the Access interface selected "Oracle Provider for OLE DB", I have never created a successful, first recorded, if there is time to ponder. Complete as follows:

------------------------------------------------even a split line-------------------------------------------------------------------- ---------------------------

In the blink of an eye, 64-bit operating system and 64-bit SQL Server 2008 are encountered, and the same 64-bit 11goracle database is still being linked. I thought it was the same as before, but the Access interface drop-down option doesn't have "Microsoft OLE DB Provider for Oracle" at all, so the 32-bit success experience is completely useless. Baidu Search after the decision to use the ODBC method, and then have encountered the following problems:

Problem one, open the ODBC source configuration interface and the System DSN cannot find the driver for Oracle. Only later to figure out if the 64-bit ODBC driver uses "C:\Windows\System32\odbcad32.exe", if it is 32 ODBC driver then to the "C:\Windows\SysWOW64\odbcad32.exe" setting. I don't know, I said no, it's the way I remember it.

Problem two, finally found the "oracle9i310-Client Lite" installed ODBC driver, the second problem followed, the creation of a System DSN in the test connection always failed. The information found on the Internet is that there is a problem with this low-version connection 11g database. Finally gave up this client version to find another method. In fact, the 32-bit work very well, and finally did not implement clearly whether there is no injustice to it.

Problem three, find the appropriate 64-bit client and ODBC driver. It's weird. "Oracle9i310-client Lite" is doing well, after installing everything you want to have, now to solve the problem three downloaded a number of compressed packages are not used successfully, this problem took two days of free time, such as the download of "Odac1120320x64copy.zip", Some people say good, I am a little useless on, give up. Finally, the two compressed packages, "Instantclient-basic-windows.x64-11.2.0.3.0.zip" and "Instantclient-odbc-windows.x64-11.2.0.3.0.zip", are working. We recommend that you download the Oracle website after registering your account. Okay, the third question is OK. Installing a full Oracle client or even a full Oracle database may not be a long time to find software. However, just to create an Oracle linked server on a SQL Server 2008 database server is not a good solution. This is also an important reason to spend time looking for a lite version.

Issue four, driver has, next add System DSN to encounter problem, the TNS Service name drop-down box has no option. This is of course related to Tnsnames.ora, only to discover that the previous step did not involve dealing with Tnsnames.ora at all. Later is the copy of the "oracle9i310-client Lite" used by the Tnsnames.ora to: "instantclient-basic-windows.x64-11.2.0.3.0" of the installation directory, the environment variable inside added "Tns_ ADMIN ", the value is set to:" instantclient-basic-windows.x64-11.2.0.3.0 "The installation directory (I set is: E:\instantclient_11_2). The drop-down option is finally able to see the name you set in Tnsnames.ora! Test connection success, Victory is in sight haha! Related to the following (the Data Source name is set by itself, I set the oracle111. To use this name for a linked server):

Issue five, the linked server is always created unsuccessfully, error: the OLE DB provider "MSDASQL" error on the linked server. The authentication failed; Ora-12154:tns: The specified connection identifier could not be resolved; Microsoft SQL Server, error: 7399. Almost spent the night searching for a solution to the problem. The final correct answer is from CSDN's old Post found, the original discussion address: http://bbs.csdn.net/topics/70308769. The failure is that the "Access interface string (V)" is not set: msdasql! Set it all OK later. Complete the following:

The 64-bit environment of the linked server is finally configured, can be normal query data. The solution took a total of two days ' spare time. Simple summary below, if the knowledge surface is not frequent Baidu and testing, so spend a lot of time, such as @provider= ' Msdasql ', on this point of no concept, can Baidu come out really have luck ingredient! After all, it took time to finally solve the problem, but also left the question did not fully understand, or self-encouragement.

SQL Server 2008 Creating an Oracle Linked server (experience)