SQL Server 2008 Connection Oracle operation steps verbose logging

SQL Server Management Studio Express (SSMS)
SQL Server Configuration Manager: SQL Server Config Manager (abbreviated SSCM)
First, open the SQL Server 2008 Remote Connection---if it is turned on, you can skip
1. Open SSMs, after connecting to the database with Windows, right click on the database engine, select "Properties"
2. Select "Security" on the left, select "SQL Server and Windows Authentication Mode" on the right to enable hybrid login mode
3. Select "Connect" on the left, check "Allow remote connection to this server", then click "OK"
4. Open SSCM, select SQL Server service on the left, and make sure that SQL Server on the right and SQL Server Browser are running
5. Select "MSSQLSERVER protocol" under "SQL Server Network Configuration" on the left, and TCP/IP on the right is "Disabled", double-click or right-click on "Properties", modify it to "Enabled" in the "Protocol" tab and "IP Address tab, set the port "1433"
6. Select "Client protocol" under "SQL Native Client 10.0 Configuration" on the left, and change "TCP/IP" to "Enabled"

7. Restart SQL Server 2008

8. You should be able to use it at this time, but still have to confirm the firewall. Add SQLServr.exe (C:\Program Files\Microsoft SQL Server\mssql10.sqlexpress\mssql\binn\sqlservr.exe) to the Allowed list
II. installation of Oracle client software
can be downloaded to Oracle website, such as "10201_client_win32"
After the installation is successful, the/network/admin/tnsnames.ora configuration in the Oracle Client installation directory is information about the server to which you are connecting, such as:
TEST =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.11.90) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)
If the admin directory does not have "Tnsnames.ora", create a new text file, renamed "Tnsnames.ora"
Iii. adding ODBC data sources for Windows
1. Starting with management tools, data source (ODBC)

Some of the Start menu does not have an administrative tools connection, you can find management tools by first going to Control Panel, then selecting Performance and maintenance.
2. Under the User DSN tab or the System DSN tab, click Add, select Microsoft ODBC for Oracle, and then click Finish

3. Fill in the relevant information

The data source name, server name and the same as configured in "Tnsnames.ora", the other two can be ignored
When you are finished, click "OK"
Iv. creating a linked server in SSMs
1. In SSMs, expand a database engine that is already connected, select the server object, then the linked server, right-click on the linked server and select new linked server

2. On the General tab, fill in the following:

"Linked server", pick a name.
"Provider" and "Product name" as shown, do not make any changes, including case
The data source is the same as configured in Tnsnames.ora, which is the same as in the "Add Windows ODBC Data source" step
"Access interface string", according to the format on the diagram to fill, or "id=username; PASSWORD=USERPASSWD "Also, here the username and userpasswd are the user name and password used to log in to the Oracle database to which you want to connect; be sure to fill in the Security tab, though
3. Switch to the "Security" tab and make the following configuration

Fill in the user name and password consistent with the username and password that you filled in the previous step
Click "OK" to complete
4. View data in Oracle
At this point, under linked servers, the linked server that you just created appears, expands, and you can see all the tables and views, but you can only see the table names, not the fields
To query can right click on a table, select: script to->select to new Query Editor window

There are two ways of writing SQL

A) use T-SQL syntax:

SELECT * from test: Username. Table name

Note: In this way, if you declare a table in Oracle with quotation marks, it must be consistent with Oracle, otherwise all uppercase, or you will not be prompted to find the table. (There's a waste of time here ...) ）

b) Use Plsql syntax:
SELECT * FROM OPENQUERY (test, ' SELECT * from test. Username. table name ')

c) The second type of access is about 50% faster than the first, and the second access is comparable to the speed of direct-to-oracle;

D) If a data type with no precision is used in the column that needs to be accessed, both queries can be error-free, which is an Oracle bug that cannot be corrected and can only be circumvented by special handling of query statements:
e) In SQL Server have the ' msdaora ' and ' oraoledb.oracle ' providers, right-click Property changes under the Access interface option, "only 0 levels" before the √ removed.

SQL Server 2008 Connection Oracle operation steps verbose logging