SQL Server Insert into Unix Informix DB

Posted in 2009

WILLIAM STOPA — Thu, 19 Feb 2009 15:42:53 — source: IIUG Forums & Mailing Lists

I am trying to do an insert to an Informix DB from SQL Query Analyzer. The insert that I tried: insert into INFDB.cars.wstopa.moodlusr (id, sess, yr, add_date, stud_teach) values (192571, 'FA', 2009, '2/9/2009', 'T') The error I get is: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. [OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.]. The insert works when I try it on the Unix machine. I can do selects & updates on the Informix DB from SQL Analyzer. So the connection works. The table in Informix was created with the following SQL: create table "wstopa".moodlusr ( id integer not null , sess char(4) not null , yr smallint not null , add_date date not null , stud_teach char(1) not null ); The error seems to say there is some difference(?) in the syntax. I have tried different ways to do the syntax, but I get the same error. IBM Informix Dynamic Server Version 10.00.HC5 SQL Query Analyzer Version SQL 8.00.2039 Any hints would be appreciated. thanks.

↪ replying to WILLIAM STOPA

Giri Raju — Thu, 19 Feb 2009 16:01:11 — source: IIUG Forums & Mailing Lists

are you using linked server or ODBC to connect informix? -Giri On Thu, Feb 19, 2009 at 10:42 AM, WILLIAM STOPA <wstopa@comcast.net> wrote: > I am trying to do an insert to an Informix DB from SQL Query Analyzer. The > insert that I tried: > > insert into INFDB.cars.wstopa.moodlusr (id, sess, yr, add_date, stud_teach) > values (192571, 'FA', 2009, '2/9/2009', 'T')> > The error I get is: > > OLE DB provider 'MSDASQL' reported an error. The provider reported an > unexpected catastrophic failure. > [OLE/DB provider returned message: Query cannot be updated because the FROM > clause is not a single simple table name.] > OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow > returned 0x8000ffff: The provider reported an unexpected catastrophic > failure.]. > > The insert works when I try it on the Unix machine. I can do selects & > updates > on the Informix DB from SQL Analyzer. So the connection works. > > The table in Informix was created with the following SQL: > > create table "wstopa".moodlusr > ( > > id integer not null , > > sess char(4) not null , > > yr smallint not null , > > add_date date not null , > > stud_teach char(1) not null > ); > > The error seems to say there is some difference(?) in the syntax. I have > tried > different ways to do the syntax, but I get the same error. > > IBM Informix Dynamic Server Version 10.00.HC5 > SQL Query Analyzer Version SQL 8.00.2039 > > Any hints would be appreciated. > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Giri Raju --000325575a36eb17ba046347a4b8

↪ replying to Giri Raju

WILLIAM STOPA — Thu, 19 Feb 2009 16:35:40 — source: IIUG Forums & Mailing Lists

linked server

↪ replying to WILLIAM STOPA

Sheshnarayan — Fri, 20 Feb 2009 05:22:18 — source: IIUG Forums & Mailing Lists

What's the CSDK version? I hope you already run the coledbp.sql (counter part to drop, doledbp.sql), which will be located at %INFORMIXDIR%\\\\etc directory on your CSDK installation, against "sysmaster" database on server side as admin. -Shesh "WILLIAM STOPA" <wstopa@comcast.net> Sent by: ids-bounces@iiug.org 19/02/2009 21:12 Please respond to ids@iiug.org To ids@iiug.org cc Subject SQL Server Insert into Unix Informix DB [14946] I am trying to do an insert to an Informix DB from SQL Query Analyzer. The insert that I tried: insert into INFDB.cars.wstopa.moodlusr (id, sess, yr, add_date, stud_teach) values (192571, 'FA', 2009, '2/9/2009', 'T') The error I get is: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. [OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.]. The insert works when I try it on the Unix machine. I can do selects & updates on the Informix DB from SQL Analyzer. So the connection works. The table in Informix was created with the following SQL: create table "wstopa".moodlusr ( id integer not null , sess char(4) not null , yr smallint not null , add_date date not null , stud_teach char(1) not null ); The error seems to say there is some difference(?) in the syntax. I have tried different ways to do the syntax, but I get the same error. IBM Informix Dynamic Server Version 10.00.HC5 SQL Query Analyzer Version SQL 8.00.2039 Any hints would be appreciated. thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.