February 19, 2002 at 11:20 am
How do I call a remote Oracle database from SQL Server 2000? I already set up a system DSN using Oracle ODBC to get to my database, but I can't seem to get the syntax right.
February 19, 2002 at 11:40 am
What are you exactly trying to do and what method are you trying to use?
February 19, 2002 at 11:45 am
I have an order management system with it's own copy of an item product master table. I want to link to the Oracle table using SQL Server 2000 and update the SQL Server 2000 table using the prod_id as the unique key, and update the product_weights where the weights are not equal.
February 19, 2002 at 12:37 pm
Try using a linked server. Check BOL or here:
http://www.sqlservercentral.com/columnists/rsharma/linkedservers.asp
http://www.sqlservercentral.com/columnists/bknight/linkedserver.asp
Steve Jones
February 19, 2002 at 12:50 pm
There is another thread with this same need for an Oracle linked server but I have found in the past 6 version some major differences and issues to deal with when creating one. To do this I will need to know
1) What version of the server are you connecting to?
2) What version of the client is installed?
3) Do you use ODBC to connect or a named instance and what is the name?
4) Or do you prefer openrowset?
5) Column names and their Oracle datatypes would also be usefull.
February 19, 2002 at 2:31 pm
SQL Server 2000 v 8.00.194
Oracle Client 8.1.6
ODBC SystemDSN = I22p
Oracle Table is wmitf
Name Null? Type
----------------------- ----
ITEMNUMBER NOT NULL NUMBER(6)
ITLENGTH NOT NULL NUMBER(4,1)
ITWIDTH NOT NULL NUMBER(4,1)
ITHEIGHT NOT NULL NUMBER(4,1)
ITWEIGHT NOT NULL NUMBER(7,2)
February 19, 2002 at 3:04 pm
Sorry, I am not at work so I am having to think about these without testing.
Try each and see if any will return your data, if all do then the last is best method.
Also if you are going to use the datasource for other things or run often (twice or more and hour), let me know and based on which returns data I will help you with what to do next.
SELECT * FROM OPENROWSET ('MSDASQL','DSN=I22p;UID=OrcLoginName;PWD=OrcPass',wmitf) AS RemOrc
In these you will most likely need to change I22p to the server name you define in your DSN I22p.
SELECT * FROM OPENROWSET ('MSDASQL','Provider=OraOLEDB.Oracle.1;SERVER=I22p;UID=OrcLoginName;PWD=OrcPass',wmitf) AS RemOrc
Same thing with I22p but this also goes stright thru Oracle provider and is considered a little better as you negotiate one less level of connectivity.
SELECT * FROM OPENROWSET ('OraOLEDB.Oracle.1','I22p';'OrcLoginName';'OrcPass',wmitf) AS RemOrc
February 20, 2002 at 2:58 pm
I must be missing something. I tried both options, but I got this error:
select * from openrowset ('MSDASQL','Provider=OraOLEDB.ORACLE.1;SERVER=i22p;UID=myid;PWD=mypasswd',wmitf) AS RemOrc
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
February 20, 2002 at 3:09 pm
Here is the linked server sql I ran:
USE MASTER
GO
EXEC sp_addlinkedserver
@server = 'indy22a',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'i22p'
February 20, 2002 at 5:56 pm
Ok then try. Open Enterprise Manager and and drill down thru Security and Linked Servers then right click on indy22a ADN look at it's propeties. And on the security tab select the bottom item so you can input the remote Oracle username and Password. We can secure this later. Click ok and then right click Linked Servers in EM and choose refresh. After refresh expand the indy22a server and click on the tables node. You should either get an error message (please post) or you will see tables. If the later then try Query Analyzer:
SELECT * FROM OPENQUERY (indy22a,'SELECT * FROM wmitf')
February 21, 2002 at 6:55 am
It works! Now, how can I secure that id/passwd? What is the syntax to compare the local table (prod_master) to the remote table (wmitf) and update the local table where the keys match?
February 21, 2002 at 8:09 am
Before anyone says otherwise I have always found OPENQUERY against an Oracle server is more stable acting that the whole LSNAME.DBNAME.DBOWNER.TABLENAME method.
This should work for the update but test.
UPDATE
prod_master
SET
product_weights = wmitf.product_weights
FROM
OPENQUERY (indy22a,'SELECT * FROM wmitf') AS wmitf
INNER JOIN
prod_master
ON
wmitf.prod_id = prod_master.prod_id
WHERE
wmitf.product_weights != prod_master.product_weights
For security if you are running in a job this should work.
1) Open EM and drill down to your Linked Server.
2) Right click LS and select properties.
3) Click the security tab.
4) Where it states "Local server login to remote server login mappings:" Put "sa" under "Local Login", check Impersonate box, and enter the username and password for the remote oracle server in the other 2 fields.
5) Change bottom radio button list item to "Not Be Made" to keep anyone else out.
Note: you may also want to if you have a user account map yourself in the top for testing purposes.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply