How to add a linked server (informix) without preset ODBC Connection

Hello Experts,

I successfully created a linked server connection to an Informix Server. However, I did not really liked the configuration steps and I suspect (hope) those steps can be made easier. Here are my steps:

1. Install Informix SDK on my SQL Server VM.
2. Create ODBC Connection for my target Informix server and test the connection
3. Run this statement:
     EXEC sp_addlinkedserver
     @Server = '<name for my Linked In Server that I choose>'
     @provider = 'MSDASQL'
     @datasrc = '<Name of my created ODBC Connection>'
     @srvproduct = 'Ifxoledbc'

...All works fine - means I have no fundamental issue with drivers, the host, target, etc.

However, I want to provide several of those entries not from my ODBC connection but via (perhaps?) @provstr. (Omit step 2 altogether)

I think I can use most (all) entry requirements using this @provstr (Host, Server, Service, Protocol, Database, UID, PWD) As you may have guessed, when I omit the @datasrc and add @provstr, it generates error and says 'Data Source Name not found and no default driver specified'

Is what I am trying possible? How do I do that?

Thank you in advance
Who is Participating?
Vitor MontalvãoMSSQL Senior EngineerCommented:
bozer, do you still need help with this question?
bozerAuthor Commented:
Thank you,

I wanted to post my full solution (that's why I accepted the solution late) but I am unable to access my lab.

Basically, I had use what's been guided and also had to add client and DB locale's to avoid mismatch issues.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.