Informix and SQLServer [resolved]

[thebloke]

Guys

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

Cheers


Peter

[PilgrimPete]

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.

[thebloke]

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

[PilgrimPete]

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.

[thebloke]

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

[thebloke]

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!

[PilgrimPete]

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...

[thebloke]

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:

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!

[thebloke]

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

Thanks again!