Can you connect directly to the SQL*Server database from the Windows ODBC Administrator screen using the System DSN SQLSRVRDBLNK ?
In the create database ink make sure you surround the user and password in double quotes to preserve the case so it is the same as on SQL*Server -
create database link sodbl connect to "user_name" identified by "passowrd" using 'tns_names_entry' ;
The user_name and password should be in the same case as defined on SQL*Server.
Hi mike Thanks,
I dont know how to connect directly to the SQL*Server database from the Windows ODBC Administrator screen using the System DSN SQLSRVRDBLNK ?
please help me out .
apart from that I have created the username and passwords in "" only
create public database link SODBL connect to "sa" identified by "sa" using 'SQLSRVDLNK';
To check that the DSN can connect do the follwing -
- Control panel
- Administrative tools
- Data Sources (ODBC)
- click on the 'Sytem DSN' tab
- click on the DSN used by the gateway, in your case SQLSRVRDBLNK
- click on 'Configure' and go through the steps.
- make sure you choose 'SQL*Server authentication'
- enter a user and password as requested
- choose the database to which you want to connect on the next screen
- after you click on 'Finish' then click on the 'Test Data Source' tab and it should connect and say 'Tests Completed Successfully'
If this doesn't work then make sure you have all the correct details and have entered them correctly. If this can't connect then the gateway won't be able to connect either.
Why are you connecting from Oracle to SQL Server? Is it for user interacted queries? Or is it for transferring data on a schedule or monitoring the systems?
If your answer is transferring or monitoring then you should consider going in the opposite direction.
Because you have the database on the same server you don't even need the driver. You may not even need to install the client (perhaps it will use the ORACLE_HOME's tnsnames (I'm not sure, you could try). You can try now quite easily to see.
1. Open SQL Server Management Studio (SSMS) and go Server Objects -> Linked Servers -> Providers....do you see "OraOLEDB.Oracle"? If so your driver is ready. If not then you have to reboot if you haven't since installing the Oracle DB. If it still isn't there then it means you must install the Oracle client on this server.
2. Add a tnsnames entry in your ORACLE_HOME tnsnames.ora...this will point to your Oracle DB and look like any normal tnsnames entry (no need for this HS stuff).
3. Right click Linked Servers & click New. Give it a name & choose OLE DB for Oracle provider. The product name must be "Oracle" and the Data source will be the name from your tnsnames entry.
4. Under the Security tab sort out your relevent logins (who you want the mapping to be and how to handle exceptions). When sorted click OK to create the link.
5. Right click your new link and click Test Connection. If this succeeds then great, otherwise look at the error (SQL Server is case sensitive so if you type system it will try to login as "system" instead of "SYSTEM").
6. enjoy. To query Oracle from SQL Server the syntax is like this:
SELECT 1 FROM LINKEDNAME..DUAL -- where LINKEDNAME is the one you set up...not the double period before DUAL... -- again tables get passed case sensitive, dont search for "dual"
After this you can just schedule jobs to run however frequently you want (weekly / minutely)
Hope this helps for a quick solution.