December 18, 2007 at 5:43 am
Hi all,
SQL Server 2005 SP2
Windows XP Pro SP2
Oracle 10g Client 10.2Oracle Data Access Components 10.2
VB6
Oracle Rdb 7.1 (not Oracle RDBMS).
I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=someusername;
Password=topsecret;Data Source=my_oledb"
I got this example VB appication off the net but have to admit I'm not sure of the difference between
OraOLEDB.Oracle.1 & OraOLEDB.Oracle.
The alias in TNSNAMES looks like this...
my_oledb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (Host = spike) (Port = 1527))
)
(CONNECT_DATA = (SERVICE_NAME = oci_oledb)
)
)
I can also TNSPING this alias without any probems.
My SQL server linked server is setup as follows.
exec sp_addlinkedserver
@server = 'MFP2',
@srvproduct='Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'my_oledb'
exec sp_addlinkedsrvlogin
@rmtsrvname='MFP2',
@useself='false',
@locallogin='sa',
@rmtuser='******',
@rmtpassword='******'
Using both openquery and four part naming (?) I get the following errors
select job_title from mfp2...jobs
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
There are no schemas in the Oracle database so I've just used three dots between
the linked server and the table name.
Again I know this syntax works becuase I have used it sucessfully with ODBC drivers.
select * from openquery(mfp2,'select * from jobs')
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
The passwrods are correct on the Oracle database as these are the ones used in the VB6 application.
Trying to expand the catalogue also causes management studio to hang.
Anyone spot the obvious mistake?
I did read on the net a while ago that registry entries might be rquired on the PC/server, is this still necessary?
Thanks in advance
Dave
December 18, 2007 at 7:03 am
Hi Dave,
Not sure about the @rmtuser='******' part, did you asterisk everything out?
Anyhow the way I'd test this is first logging remotely onto the SQL Server via a desktop and trying to connect to the Oracle box in question using SQL*Plus or the likes. Next I'd try another username and password combo inside your OLEDB connect string.
Hope it helps?
Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
December 18, 2007 at 9:21 am
Hi Michael,
Yes I just asterixed the password out for the post.
I can't use SQL*Plus as the remote database is Oracle Rdb which is not
the standard Oracle product (8i, 9i 10g etc).
I know the username/password are correct as they are they are
tied to the OS (OpenVMS).
Dave
December 19, 2007 at 1:15 am
Hi Dave,
Ok, I missed that bit. I am familiar with 7, 8i, 9i, 10g and even 11g. I'm not familiar with Oracle 9 Rdb running on an OpenVMS box.
Have you tried testing the OleDB connect from a client machine? Alternatively tried ODBC or another username? Other than that I'm flummoxed, sorry... :unsure:
Michael Gilchrist
Database Specialist
http://www.michael-gilchrist.com
December 19, 2007 at 2:07 am
Hi, I think I can help here.
1. Download the latest Oracle RDB ODBC driver from Oracle.
2. Create and configure your ODBC connection to the database in question.
3. Navigate to the providers option via, Server Objects -> Linked Servers -> Providers
4. Right-Click and select properties of MSDASQL
5. Check the box that says level zero only and click OK
6. Right-Click linked server and select new linked server
7. For this example set the linked server name to 'Fred'
8. Set the provider to 'Microsoft OLE DB Provider to ODBC Drivers.
9. Product name can be anything but must be set. (I use the ODBC source name)
10. Data Source is the ODBC Source name.
11. Click on the security option.
12. Click the 'be made using this security context' radio option.
13. Enter the user and password from the VMS box
14. Click ok.
If all is successful, you should now see your new linked server,you won't be able to expand it to see tables but there is a way to get table definitions if you are interested?.
To use the linked server is simple, remember you cannot overwrite the database or user so these remain blank in the syntax.
Example for ODBC name of CHK_RDB and table of PRODUCT
Select PRODUCT_ID From CHK_RDB...PRODUCT
...
hth
Mike
December 19, 2007 at 2:30 am
Mike Seddon (12/19/2007)
Hi, I think I can help here.1. Download the latest Oracle RDB ODBC driver from Oracle.
2. Create and configure your ODBC connection to the database in question.
3. Navigate to the providers option via, Server Objects -> Linked Servers -> Providers
4. Right-Click and select properties of MSDASQL
5. Check the box that says level zero only and click OK
6. Right-Click linked server and select new linked server
7. For this example set the linked server name to 'Fred'
8. Set the provider to 'Microsoft OLE DB Provider to ODBC Drivers.
9. Product name can be anything but must be set. (I use the ODBC source name)
10. Data Source is the ODBC Source name.
11. Click on the security option.
12. Click the 'be made using this security context' radio option.
13. Enter the user and password from the VMS box
14. Click ok.
If all is successful, you should now see your new linked server,you won't be able to expand it to see tables but there is a way to get table definitions if you are interested?.
To use the linked server is simple, remember you cannot overwrite the database or user so these remain blank in the syntax.
Example for ODBC name of CHK_RDB and table of PRODUCT
Select PRODUCT_ID From CHK_RDB...PRODUCT
...
hth
Mike
Hi Mike,
Sorry I neglected to say that although I'm testing this scenario on my 32bit PC it will ultimately live on a
64bit server. As far as I know "MS OLE DB provider for ODBC drivers" is not available for the x64 platform.
I'm going to investate further points 4 & 5 as we do not currently have this set for our existing ODBC to VMS
linked servers.
4. Right-Click and select properties of MSDASQL
5. Check the box that says level zero only and click OK
Back to the OLEDB connectivity problem though. What I cant understand is why VB6 can connect and SQL 2005 linked server cant even though both are using the same credentials.
I will have a further hunt around on the VMS box to see if SQL/Services created any invalid password logfiles or perhaps there is something of interest in the accounting utility.
Cheers
Dave
December 19, 2007 at 2:39 am
Dave, I believe that the 'MS OLE DB provider for ODBC drivers' 64-bit is available, its the Oracle ODBC RDB 64 bit drivers that are not yet available. Although, Oracle insist they are working on them and wil release them shortly.
Mike.
December 19, 2007 at 7:48 am
Mike Seddon (12/19/2007)
Dave, I believe that the 'MS OLE DB provider for ODBC drivers' 64-bit is available, its the Oracle ODBC RDB 64 bit drivers that are not yet available. Although, Oracle insist they are working on them and wil release them shortly.Mike.
Hi Mike,
Ok thanks for the info, I will keep looking out for the Rdb ODBC 64bit drivers.
ODBC on SQL2000 32bit was very slow, hope it will be better under SQL2005 on 64bit.
Dave
December 20, 2007 at 1:42 am
My Advice for performance would be to use RDB Views and Stored procedures, let RDB do the merging and filtering where possible.
Mike
December 20, 2007 at 6:29 am
Sorry I neglected to say that although I'm testing this scenario on my 32bit PC it will ultimately live on a
64bit server.
MS will not produce a 64 bit driver for Oracle. You will need to download the ODAC (Oracle Data Access) package from Oracle if you want linked server ability to an Oracle datbase. Also, ODAC doesn't support versions of Oracle before 8.1
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 20, 2007 at 7:15 am
MG (12/20/2007)
Sorry I neglected to say that although I'm testing this scenario on my 32bit PC it will ultimately live on a
64bit server.
MS will not produce a 64 bit driver for Oracle. You will need to download the ODAC (Oracle Data Access) package from Oracle if you want linked server ability to an Oracle datbase. Also, ODAC doesn't support versions of Oracle before 8.1
This is not Oracle RDBMS, i.e. its not Oracle 7, 8i, 9i, 10 or 11g. It's...
SQL> show version;
Current version of SQL is: Oracle Rdb SQL V7.1-221
Underlying versions are:
Database with filename SQL$DATABASE
Oracle Rdb V7.1-221
Rdb/Dispatch V7.1-221 (OpenVMS Alpha)
ODAC and this flavour of Oracle are compatible according to Oracle but how to go it working with SQL server is the problem...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply