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]
CREATE TABLE [dbo].[LTbl] (
[c][int] Not NULL DEFAULT (-100)
) ON [PRIMARY]
RAISERROR('.....Table LTbl created.',0,1) WITH NOWAIT
INSERT INTO LTbl
SELECT * FROM OPENQUERY(CCC,'SELECT a_date,b,c FROM LTbl ')
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?