ms sql queries against informix database[resolved]

[thebloke]

Guys

I'm having a problem running some queries in sqlserver against an informix database. The database is set up as a linked server through enterprise manager and I can run some basic queries using a system DSN named electra:

select * from openquery
(electra,'select * from agent')

fine, however if I try anything more than that, eg:

select * from openquery
(electra, 'select top 10 * from agent')

I get nasty errors:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: ODBC Driver cannot be used because it does not support one or more of the following APIs: SQLTables, SQLColumns, or SQLGetTypeInfo.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].

This isn't necessarily the query I want to run, there will be a few, it's just that anything I try other than a basic select returns this error.

Any ideas?!

Cheers

[si_the_geek]

Does the Informix database support TOP? I know it never used to, and this is probably why it isn't working.

I would recommend writing the query directly against Informix first, then once it is working copy it into your call to OpenQuery.

Note that you can do the TOP part outside the OpenQuery if you need to, it just won't be as efficient (as all the data in the table will have to move between the servers, rather than just 10 records).

[thebloke]

Does the Informix database support TOP? I know it never used to, and this is probably why it isn't working.

I would recommend writing the query directly against Informix first, then once it is working copy it into your call to OpenQuery.

Note that you can do the TOP part outside the OpenQuery if you need to, it just won't be as efficient (as all the data in the table will have to move between the servers, rather than just 10 records).

Eh?!

Sorry, I don't know owt about Informix databases. I was hoping, probably naively, that I could just use standard sql queries. I guess not! What is TOP?

[si_the_geek]

Unfortunately there isn't really "standard" SQL beyond the most basic Select/Insert/Delete etc. There are two defined sets of standards (SQL-89 and SQL-92), but each DBMS tends to have its own interpretation of these, as well as its own extra functions.

I'm afraid you need to check the syntax and functions for each new database system you use, as very few are exactly compatible (but the majority are pretty close). The biggest issue tends to be joins - even MS Access and MS SQL Server aren't quite the same on this!

To the best of my knowledge Informix doesnt support the Top clause for select statements, but I could be wrong (over 2 years since I last used it!).

[thebloke]

Eh?!

Sorry, I don't know owt about Informix databases. I was hoping, probably naively, that I could just use standard sql queries. I guess not! What is TOP?

Sorry, what a dunce! I've re-read what I've written! Doh!

Does anyone know of a good resource for Informix syntax/functions?

[si_the_geek]

The company has been bought out a couple of times over the years, so I'm not sure whether or not this will be accurate for the version you are using. IBM have reference material for Informix here:

http://publib.boulder.ibm.com/infoce...help/index.jsp

The equivalent of the manual I used to get my SQL info from is under the following section of the navigation:
Developing - Developing SQL Statements - Guide to SQL: Syntax - SQL Statements

[thebloke]

Thanx

[Luciano Lezama]

la sintáxis correcta en informix es:

'select first 10 * from agent'

esto a mi me anduvo.

[thebloke]

la sintáxis correcta en informix es:

'select first 10 * from agent'

esto a mi me anduvo.

Gracias!

[Luciano Lezama]

Gracias!

de nada

[thebloke]