servers

Hi,

Disclaimer: I am not a DBA so my attempts may not make sense. :)

I have a win 2003 server running sql server 2005. I am trying to create a 'linked server' with an oracle 8i database at a remote site. I have tried using the 'add linked server' option from sql server enterprise manager and I have also tried to create it using the sp_addlinkedserver command. i tried using a dsn as the datasource and I have also tried using a dsnless connection using a TNS entry as the provider string. The Oracle client is on the box as can be confirmed both by the dropdown in the 'add linked server' dialog in sql server enterprise manager and also by going into the oracle enterprise manager and actually sending oracle data. I don't really have a preference on how to create the 'Linked server', I just need to get it to work.

AAA = server
BBB = DSN
XXX = UID
YYY = pwd

DSNLess Attempt:
EXEC master.dbo.sp_addlinkedserver @.server = N'TEST1', @.srvproduct=N'OraOLEDB.Oracle', @.provider=N'OraOLEDB.Oracle', @.provstr=N'SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDR ESS=(PROTOCOL=TCP)(HOST=AAA)(PORT=1556))) (CONNECT_DATA=(SERVICE_NAME=LPPCPEDB)));uid=XXX;pw d=YYY;'

DSN Attempt:
EXEC master.dbo.sp_addlinkedserver @.server = N'TEST2', @.srvproduct=N'Oracle', @.provider=N'OraOLEDB.Oracle', @.datasrc=BBB'

Thanks in Advanceyou need to do

sp_addlinkedsrvlogin|||Thank you for your response. I did add that as well, but I forgot to put it in my post. I did not have any luck. Here is the statement I used:

sp_addlinkedsrvlogin
@.rmtsrvname = N'BBB'
, @.useself = false
, @.locallogin = NULL
, @.rmtuser = N'XXX'
, @.rmtpassword = N'YYY'|||sorry...for rmtservername i meant to indicate: TEST2 instead of BBB