|
|||||||||||||
|
|
You can define your own SID for a gateway, but it is easier to accept the default SID Oracle creates for you: dg4msql. The benefit of using dg4msql is that you get a pre-configured initialization parameter file. If you have multiple connections, you’ll need multiple init files. The contents of the default file are shown below. # This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[WIN2003]/WIN2003\SQL2008/AdventureWorks HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER I added several other examples in my file, and wound up using the comma-port format: #HS_FDS_CONNECT_INFO=[WIN2003]/WIN2003\SQL2008/AdventureWorks #HS_FDS_CONNECT_INFO=win2003:1433//AdventureWorks #HS_FDS_CONNECT_INFO= The next step is to configure the listener. No doubt, you already have a listener.ora file on hand, so all that is necessary here is to add an entry to the SID list, using the format below:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=gateway_sid)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=dg4msql)
)
)
As a bonus, Oracle creates a sample listener.ora (and tnsnames.ora) file for you in the dg4msql\admin folder, so all you need to do is cut and paste the list entry into your main listener.ora file (and then reload the listener). The status output in lsnrctl should show the new entry: Service "dg4msql" has 1 instance(s). Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service... You have to edit the tnsnames.ora file manually, but again, you can use the sample file in the dg4msql\admin folder. Just add the sample entry (adjusting for port number if necessary) into the main tnsnames.ora file:
dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
Create a database link, using double-quoted identifiers for the username and password, and single quotes for the tnsnames value. CREATE PUBLIC DATABASE LINK dg4 CONNECT TO "Oracle" IDENTIFIED BY "oracle" USING 'dg4msql'; Skipping the two-phase commit part, the next step includes running a script that creates Oracle-like data dictionary views in MSSQL. Open a New Query in SQL Server Management Studio (while connected as sa or as an Administrator), open the file (which pastes it into the query window) and run it. There will be several error messages as the script contains drop statements against objects that don’t exist (yet). At this point, you should be able to query against SQL Server. The output of select * from all_users@dg4 is shown below. USERNAME USER_ID CREATED -------------------- ---------- --------- public 0 08-APR-03 dbo 1 08-APR-03 guest 2 08-APR-03 INFORMATION_SCHEMA 3 14-OCT-05 sys 4 14-OCT-05 Oracle 5 19-OCT-10 db_owner 16384 08-APR-03 db_accessadmin 16385 08-APR-03 db_securityadmin 16386 08-APR-03 db_ddladmin 16387 08-APR-03 db_backupoperator 16389 08-APR-03 db_datareader 16390 08-APR-03 db_datawriter 16391 08-APR-03 db_denydatareader 16392 08-APR-03 db_denydatawriter 16393 08-APR-03 A query against a table in the AdventureWorks database: select * from "AWBuildVersion"@dg4;
SystemInformationID Database Version VersionDa ModifiedD
------------------- ---------------- --------- ---------
1 9.06.04.26.00 26-APR-06 26-APR-06
Note that these two statements are not the same: select * from "AWBuildVersion"@dg4; select * from "awbuildversion"@dg4; The double-quoted identifier is important as the case matters when selecting from Oracle into SQL Server. If the table is in a namespace, such as HumanResources.Department, then double-quote the namespace and table. For example: select * from “HumanResources”.”Department”@dg4; If selecting columns by name, double-quote the column names as well.
SQL> select "DepartmentID", "Name", "GroupName"
2 from "HumanResources"."Department"@dg4;
DepartmentID Name GroupName
------------ ------------------------------ -------------------------------------
1 Engineering Research and Development
2 Tool Design Research and Development
3 Sales Sales and Marketing
4 Marketing Sales and Marketing
5 Purchasing Inventory Management
6 Research and Development Research and Development
7 Production Manufacturing
8 Production Control Manufacturing
9 Human Resources Executive General and Administration
10 Finance Executive General and Administration
11 Information Services Executive General and Administration
12 Document Control Quality Assurance
13 Quality Assurance Quality Assurance
14 Facilities and Maintenance Executive General and Administration
15 Shipping and Receiving Inventory Management
16 Executive Executive General and Administration
The other configuration options can be tested in your environment (encryption and multiple databases) as needed. Now you have a complete, from start to finish, example of how to access SQL Server from Oracle. Related Articles
Oracle Database to SQL Server Comparisons
|
||||||||||||||||||||||||||||||||||||
 |
