| Author |
Topic |
|
davidmgray
Starting Member
5 Posts |
 Posted - 2007-12-18 : 07:57:39
|
| Hi all, SQL Server 2005 SP2Windows 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 1Cannot 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 1Cannot 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 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-12-18 : 14:42:35
|
Have you tried logging in with SQLPLUS using the same credentials from the server?
Future guru in the making. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-18 : 21:37:35
|
| Is provider correct? Tried creating linked server with gui in ssms? |
|
|
|
davidmgray
Starting Member
5 Posts |
Posted - 2007-12-19 : 03:46:46
|
quote:
Originally posted by Zoroaster
Have you tried logging in with SQLPLUS using the same credentials from the server?
Future guru in the making.
Hi, SQL*Plus is not available as the database is Oracle Rdb (not 8i, 10g etc).I know the credentials are correct as they are tied to the OS login, also these credentials are used in the VB6 program which runs from my PC and connects to the server & database without any problems. Dave |
|
|
|
davidmgray
Starting Member
5 Posts |
Posted - 2007-12-19 : 04:34:50
|
quote:
Originally posted by rmiao
Is provider correct? Tried creating linked server with gui in ssms?
Hi, Yes I've tried seeting up the "linked server" via the GUI and the result is still the same. Dave |
|
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2007-12-19 : 12:49:41
|
| "select * from ..."Try replacing * with the column names.Donn Policarpio |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-19 : 15:04:37
|
| Tried with other provider? |
|
|
|
davidmgray
Starting Member
5 Posts |
Posted - 2007-12-20 : 05:46:46
|
quote:
Originally posted by donpolix
"select * from ..."Try replacing * with the column names.Donn Policarpio
That does not work either. |
|
|
|
davidmgray
Starting Member
5 Posts |
Posted - 2007-12-20 : 05:47:44
|
quote:
Originally posted by rmiao
Tried with other provider?
As far as I know the only provider is Oracle unless abyone knows different. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-20 : 15:50:19
|
| Microsoft has provider for Oracle, can also use odbc provider. |
|
|
|
|
Why can't T-SQL access Oracle DB when VB6 can?