How do I create an ODBC connection to OracleXE using sp_addlinkedserver in MSSQL using a DSN-LESS connection string?

How do I create an ODBC connection using sp_addlinkedserver in SQL using a DSN-LESS connection string?

I assume I am doing something wrong with syntax, here is how I set up the connection:

EXEC master.dbo.sp_addlinkedserver @server = N'SERVER_NAME', @srvproduct=N'Oracle', @provider=N'MSDASQL', @provstr=N'DataSource=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER_NAME)(PORT=INSTANCE_POR开发者_运维知识库T)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))' GO

Any ideas?

Thanks in advance.

---

From what i can see here you should be using @provider=N'MSDAORA' You might also need to use sp_addlinkedsrvlogin in order to successfully login to your ORA server.

Have you checked this out?

By the way, you can check the connection like it is shown here, but first of all make sure you do have a linked server after you run sp_addlinkedserver using this select: SELECT * FROM sys.syservers

Make sure you delete the linked server after you're done with it.

Are you linking the Oracle server in the same query with the select? Because this might not work successfully.