SQL Server Insert into Unix Informix DB RRS feed

  • Question

  • 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.

    Any hints would be appreciated.

    Wednesday, February 18, 2009 11:54 PM

All replies

  • Did you ever get an answer to this issue? I have the exact same environment and getting the same error message letter for letter. If it's any help, if you use ODBC via Access, it works, takes a while but works well.  We are using server links in SQL 2000


    Thursday, August 13, 2009 3:38 PM