Linked Server to Oracle - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Server to Oracle

Author

Topic

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-07-07 : 10:00:24

I have created a linked server though when querying a table from the linked server I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Here is the sql that I used for linking:

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = 'Oracle',
@provider = 'MSDASQL',
@provstr = 'DRIVER={Oracle ODBC Driver};DataSource =igs_tampa_a;UID=my_uid;PWD=my_pswd;'

or

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = 'Oracle',
@provider = 'MSDASQL',
@datasrc = 'igs_tampa_a'

Neither seems to work and both yields the exact error message.

Derrick

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-07-07 : 10:44:08

Sorry, I was using the wrong box when I was trying to connect.

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'igs_test1'

Here is the error..it makes since because there is no where that I am familiar with to add login info here...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01004: default username feature not supported; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Or here where I specify the login info:

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = 'Oracle',
@provider = 'MSDASQL',
@provstr = 'DRIVER={Oracle ODBC Driver};DataSource =igs_tampa_a;UID=my_uid;PWD=my_pswd;'

this yields the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Oracle][ODBC][Ora]General error.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].

Any ideas?

Derrick

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-07 : 11:00:06

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q197/4/59.asp&NoWebContent=1

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-07-07 : 11:32:11

quote:
Originally posted by RickD

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q197/4/59.asp&NoWebContent=1

I can connect via ASP channels, my problem is linking this server. Looking at the page that you sent me and the ASP that I had created, I ended up using the connection info from my asp page and it worked. What a difference:

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = 'Oracle',
@provider = 'MSDASQL',
@provstr = 'dsn=igs_tampa_a;UID=my_uid;PWD=my_pswd;'

Vice

EXEC sp_addlinkedserver
@server = 'Tampa_A',
@srvproduct = ''Oracle'',
@provider = 'MSDASQL',
@provstr = 'DRIVER={Oracle ODBC Driver};DataSource=igs_tampa_a;UID=my_uid;PWD=my_pswd;'

Thanks for your help!

Derrick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources