Informix and SQLServer [resolved]

Guys

Is it possible to directly query an informix database from SQLServer, ie from Query Analyser or Enterprise Manager?

Cheers


Peter

I don't see why not.
If you have an OLEDB Driver for Informix on the SQLServer box, then you should be able to create a linked server and query it.
Or you could investigate the OPENROWSET T-SQL function.

Linked server. Right. Ok, how do I do this. I have a system dsn set up.....

Hmmm. Not sure how/if you can use your DSN, but Linked Servers can be set up in a similar way.
In Enterprise Manager drill down to the Security node (something like SQL Server Group > local > Security > Linked Servers) and it has a nice little wizard (not very intuitive, but it gets you there).
You can also use a range of system stored procedures, for example sp_addlinkedserver.

Yeah, I found the wizard but I'm a bit foxed with some of the fields. Sorry to sound like such a dunce!

Oh! It's ok! I just put in the name of the DSN and Bob's your uncle, as it were.

Thanks! You pointed me in the right direction!

:thumb:

Cool. Glad it worked.
While you were trying that I found this on books online, which is for DB2, but it should be possible to bend it to do Informix if you wish...

Thanks Pete

I can now see the tables but can't query them! I've tried

SELECT *
FROM OPENQUERY(mylinkedserver, 'SELECT * FROM mydatabase.mytable.myfield')

but it aint working. I get this error:

Code:

---

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Esker][liv-electra:TUNODBC200.ifx][electra] Syntax error or access violation]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

---

I think possibly the problem may be that I don't know what the database name is! I see the linked server name and the tables and views so I've used the same name for the linked server and the database. This is probably wrong!

No, really. I've sorted it this time!

Thanks again!

:thumb: