February 14, 2002 at 10:10 am
hi guys,
I have SQL server 7.0 installed at my work. Also, i have a SQL*NET client configured for an Oracle 8.1 database. Is there any help on the web where i could create a linked server to my SQL server? Everytime i try to do this setup with SQL server help, it gives me an error. Let me know if you need more details on this.
February 14, 2002 at 10:20 am
So let me make sure, you want to have a link on your SQL Server to the Oracle DB, not the other way around?
February 14, 2002 at 10:36 am
I have never got a link server to work with anything but the 7.3 client. here is a little tech article
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q277002
Wes
February 14, 2002 at 10:49 am
I am doing fine with the 8+clients but I have had several datatype issues I had to handle by doing a to_char or to_num when pulling.
February 14, 2002 at 11:15 am
Yes Antre, i wish to have Oracle database as a linked server in my SQL Server. How do i go about that?
February 19, 2002 at 8:14 am
No one knows how to go about this? i have SQL*NET configured to the Oracle database on my PC. I want to know how to create the linked server?
February 19, 2002 at 8:42 am
Use sp_addlinkedserver to create the linked server, then sp_addlinkedsrvlogin to add the login details.
EXEC sp_addlinkedserver
@server = '???',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = '???'
Then you may need to change a registry key.
See 'ole db provider for oracle' in Books online for more detail
Steven
February 19, 2002 at 9:05 am
steven, i was able to create the linked server using sp_addlinkedserver...however, what's the syntax for sp_addlinkedsrvlogin? i do not seem to get the syntax anywhere...also what registry key do i have to modify inorder for my linked server to work?
February 19, 2002 at 9:09 am
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
eg
exec sp_addlinkedsrvlogin 'BUTCOPY', false, 'sqlaccount', 'oracleaccount', 'password'
From Books Online
-----------------
Registry Entries
To enable the OLE DB Provider for Oracle to work with your Oracle client software, the client's registry must be modified by running a registry file from a command line. Multiple instances of the client software should not run concurrently. These files are listed in the following table and are located within the same directory structure that contains your Microsoft Data Access Component (MDAC) installation, which typically is in C:\Program Files\Common Files\System Files\OLE DB.
Oracle client Windows NT or 9x Windows 2000
7.x mtxoci7x_winnt.reg mtxoci7x_win2k.reg
8.0 mtxoci80x_winnt.reg mtxoci80x_win2k.reg
8.1 mtxoci81x_winnt.reg mtxoci81x_win2k.reg
Steven
February 19, 2002 at 9:16 am
Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
If you plan to connect all local logins to the linked server using a specified user and password
EXEC sp_addlinkedsrvlogin 'rmtsrvname', 'false', NULL, 'rmtuser', 'rmtpassword'
But this means any connection to your SQL server can pass thru the link. I prefer to limit to one account using a specified user.
EXEC sp_addlinkedsrvlogin 'rmtsrvname', 'false', 'locallogin', 'rmtuser', 'rmtpassword'
For instance you have a web site that uses a SQL Account to connect of 'web' with password 'bland' and you want it to have access to 'OrcRem' your remote link and connect to Oracle as 'AccessAcct' with password 'Limited'.
EXEC sp_addlinkedsrvlogin 'OrcRem', 'false', 'web', 'AccessAcct', 'Limited'
All this is found in SQL Books Online with other possibilites.
Hope this helps.
February 19, 2002 at 9:24 am
guys, i tried all the options....i did use the addlinkedserver and addlinkedsrvlogin stored procedures...still no luck...i am getting the same error...please advise...
February 19, 2002 at 10:06 am
Sorry, I did not see a post about the error. What is the error message you are getting?
February 19, 2002 at 10:08 am
this is the error message i am getting..
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation.
You will be unable to use this provider until these components have been installed.]
February 19, 2002 at 10:18 am
Ok let's back up, I got to reading here. What is the value you input for Host String in SQL*Plus, and is this an ODBC entry on your machine or an actual TNS entry?
February 19, 2002 at 10:34 am
Also try this if you don't know for sure.
EXEC sp_addlinkedserver
@server = 'YourNameForLinkServerHere',
@srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'WhatYouEnterForHostStringInSQL*Plus'
This will use the native Oracle Driver if everything else is right, and the add login I gave should be fine.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply