ole db problems while linking sql server and informix

We have an informix 9.40.hc5 system running on hpux and we need to use oledb to connect to it from sql server. Personally, I'd rather shoot the sql server and move everything to informix but, the software vendor doesn't like that idea and we don't have to write our own version of this particular app. So, when querying tables in the informix database which have blob type fields of any kind in them, we get his error message: select * from carstrainole.carstrain.informix.ctc_blob Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'Ifxoledbc' supplied inconsistent metadata for a column. Metadata information was changed at execution time. OLE DB error trace [Non-interface error: Column 'txt' (compile-time ordinal 2) of object '"carstrain":"informix"."ctc_blob"' was reported to have a DBCOLUMNFLAGS_ISLONG of 0 at compile time and 128 at run time]. We're using driver version 2.90.tc6x2 on the sql server to deal with a similar issue on money columns. Any clues? I'm hoping this is a known issue which probably has a solution in a latter release of the driver or some such. -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University 903-233-3537

I also had this problem with certain data types in version 2.90.TC4. Work-arounds include using an ODBC linked server rather than OLEDB and/or "openquery" rather than 4-part naming. Did you run "Client-SDK\\etc\\coledbp.sql"? -- Regards, Doug Lawry www.douglawry.webhop.org "Chris Salch" <ChrisSalch@letu.edu> wrote in message news:mailman.262.1172182909.10648.informix-list@iiug.org... > We have an informix 9.40.hc5 system running on hpux and we need to use > oledb to connect to it from sql server. Personally, I'd rather shoot > the sql server and move everything to informix but, the software vendor > doesn't like that idea and we don't have to write our own version of > this particular app. So, when querying tables in the informix database > which have blob type fields of any kind in them, we get his error > message: > > select * from carstrainole.carstrain.informix.ctc_blob> > Server: Msg 7356, Level 16, State 1, Line 1 > > OLE DB provider 'Ifxoledbc' supplied inconsistent metadata for a column. > Metadata information was changed at execution time. > > OLE DB error trace [Non-interface error: Column 'txt' (compile-time > ordinal 2) of object '"carstrain":"informix"."ctc_blob"' was reported to > have a DBCOLUMNFLAGS_ISLONG of 0 at compile time and 128 at run time]. > > We're using driver version 2.90.tc6x2 on the sql server to deal with a > similar issue on money columns. Any clues? I'm hoping this is a known > issue which probably has a solution in a latter release of the driver or > some such. > -- > -------------------------------------------- > Chris Salch > Programmer/Analyst > LeTourneau University > 903-233-3537

Yes, that was run on the server side. Unfortunately, openquery is not a viable option for what we are doing. From what I understand, openquery requires a string constant for its sql which doesn't give us the flexibility we need. Is there a way to use for part naming or something similar with an odbc linked server? On Fri, 2007-02-23 at 10:49 +0000, Doug Lawry wrote: > I also had this problem with certain data types in version 2.90.TC4. > Work-arounds include using an ODBC linked server rather than OLEDB and/or > "openquery" rather than 4-part naming. Did you run "Client-SDK\\etc\\coledbp.sql"? > -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University 903-233-3537

4-part naming does not work with ODBC. Although "openquery" is more awkward, you can execute a string in SQL Server, so you can therefore indirectly introduce variables into the "openquery" statement. Chris Salch wrote:> Yes, that was run on the server side. Unfortunately, openquery is not a> viable option for what we are doing. From what I understand, openquery> requires a string constant for its sql which doesn't give us the> flexibility we need. Is there a way to use for part naming or something> similar with an odbc linked server?>> On Fri, 2007-02-23 at 10:49 +0000, Doug Lawry wrote:>> I also had this problem with certain data types in version 2.90.TC4.>> Work-arounds include using an ODBC linked server rather than OLEDB and/or>> "openquery" rather than 4-part naming. Did you run >> "Client-SDK\\etc\\coledbp.sql"?