Adding a Linked Server

HI,

There is a requirement to transfer a table data from a oracle view to a SQL Server table. These both reside on the same box. I am trying to add the oracle instance as a linked server, like this:

EXEC sp_addlinkedserver @.server = @.SOURCE_SERVER_NAME, @.srvproduct = '', @.provider = @.PROVIDER_NAME, @.datasrc = @.DATA_SOURCE_NAME

The server gets added. But When I use this string as part of a stored procedure (or even not use it there), which does the work of transfering the data as required, I get this error:

Server: Msg 7399, Level 16, State 1, Procedure UBS_PULL_HR_DATA, Line 125
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: ].

I have added this Name in the System DSM also. I dont know how to go about it. Can any one please help?

Thanks a lot and let me know if the question is not clear.

Mannu.

When the server gets added, check the security tab. I'm guessing it's blank as you aren't adding the login information. Error 0x80004005 is access is denied. That looks like it's one of the problems anyway. If you want to script the login for the linked server, you would use sp_addlinkedsrvlogin.

-Sue

|||

Hi Sue, Thanks for the reply. I have overcome this problem. However, I am now getting this error:

The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].

Any ideas to solve this? Thanks a lot.

Mannu.