Creating temporary tables via ODBC
Dear Andy,
This is happening because Informix cannot handle 3 SQL queries in one statement -
MS SQL Server can.
The reason why it is working from one instance of WinSQL is probably because the value
for "Query Terminator" is set to a semi-colon. A Query Terminator in WinSQL breaks
your scripts into multiple chunks and submits one query at a time.
To understand this better, try submitting the following to an Informix database.
select count(*) from systables
select count(*) from systables
You will get a syntax error. Now try submitting a similar statement to MS SQL Server.
select
count(*) from sysobjects
select count(*) from sysobjects
MS SQL Server will return 2 result sets.
Typically, a query terminator in MS SQL Server is "GO", which is NOT changeable. Although
"GO" is also the default query terminator in WinSQL, it is changeable to any other
value. Try re-writing your query like:
create temp table temptab(
col1 char(60)
) with no log
go
insert into temptab select tabname from systables
go
select count(*) from temptab
and see if this works. I am pretty sure this query will work in WinSQL Lite as well
as Professional as long as the query terminator is set to "GO".
Best regards,
Synametrics Support.
>I would like to be able to use Microsoft Reporting Services (part of SQL Server)
to
>extract data from an informix instance via ODBC but the extraction process fails
when
>I incorporate temporary tables.
>
>I have reproduced the problem using SQL similar to:
>
>------------------------------------
>create temp table temptab
>(
>col1 char(60)
>) with no log;
>
>insert into temptab select tabname from systables;
>
>select count(*) from temptab;
>------------------------------------
>
>When running all three of these statements at once with the Microsoft tool it returns
>an
informix error 206 which means that it cannot find the temporary table. If I run
>each of the above one at a time within the same session it all works fine.
>
>Whenever I have problems with ODBC I always rely on Winsql to do any troubleshooting.
>I
have noticed that I can reproduce the above symptoms when using winsql lite but
>that with winsql pro it works as I'd expect (i.e. I can submit all 3 statements at
>once
and it will complete successfully).
>
>As I use the same DSN and driver version in each I'm assuming the difference is with
>the
version of winsql. I would be very grateful if you have any idea of what this
>difference may be as I am hoping that it will shed some light on my original problem
>with
Microsoft Reporting Services.
>
>I know this isn't a problem with winsql but would really appreciate any help you
can
>give.
>
>Kind Regards,
>Andy
