[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Openquery w/Informix

Posted on 2004-11-29
8
Medium Priority
?
1,150 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Glass
  • 5
  • 3
8 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12705004
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
 

Author Comment

by:Glass
ID: 12709556
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12715681
did you use the same driver for the dts package ?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Glass
ID: 12716025
The same drivers were used...

Nothing changed with regard to drivers.

Glass
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12716044
Im clutching @ straws here but....

Have you got the latest SQL Service pack installed?

Are you using the latest MDAC ?

0
 

Author Comment

by:Glass
ID: 12716722
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 1000 total points
ID: 12716858
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12717722
did this solve it ?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question