Openquery w/Informix

Have a SQL Server 2002 database which needs to acquire information from an Informix Database.  Have created a linked server called CCC which is an informix database and the connection test succeeds.  When I execute the following code, I receive an error:

/* Deletes table if it exists and recreates as local table */
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[LTbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LTbl]
GO

CREATE TABLE [dbo].[LTbl] (      
     [a_date] [datetime],
     [b] [int],
     [c][int] Not NULL DEFAULT (-100)
) ON [PRIMARY]
GO


RAISERROR('.....Table LTbl created.',0,1) WITH NOWAIT  
GO

INSERT INTO LTbl
(
        a_date,
        b,
        c          
      
)
SELECT * FROM OPENQUERY(CCC,'SELECT a_date,b,c FROM LTbl ')
GO

ERROR:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'OpenLinkODBC' reported an error.  
[OLE/DB provider returned message: [OpenLink][ODBC][Informix Server]A syntax error has occurred. (-201)]
OLE DB error trace [OLE/DB Provider 'OpenLinkODBC' ICommandPrepare::Prepare returned 0x80004005:   ].

Have tried different variations of the query but still seems to error.  How can this one be submitted so that the data downloads?

Glass
GlassAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
The error message suggests that the syntax of SELECT a_date,b,c FROM LTbl  is incorrect    I dont know anything about informix but i would have thought that this is a valid command.   so you might want to try updating the drivers or using alternative drivers.
0
GlassAuthor Commented:
Have executed the same query on the Informix Database and it runs as expected.  Have generated a DTS package and the data downloads work fine.  Just can't seem to get the OPENQUERY option to work.  

Suggestions?

Glass
0
ShogunWadeCommented:
did you use the same driver for the dts package ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GlassAuthor Commented:
The same drivers were used...

Nothing changed with regard to drivers.

Glass
0
ShogunWadeCommented:
Im clutching @ straws here but....

Have you got the latest SQL Service pack installed?

Are you using the latest MDAC ?

0
GlassAuthor Commented:
So the DTS work while the Openquery fails when the missing the latest SQL Service pack or MDAC?  In any event, let me check.  I believe that they have the latest but could be wrong.


Glass
0
ShogunWadeCommented:
It is possible that DTS will behave differently to OPENQUERY due to mdac & service packs   (sounds strange) but the reason why is that interally they both employ slightly different methods of acheiving the same thing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShogunWadeCommented:
did this solve it ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.