Heterogeneous query error with linked server

  • I am transferring data from Progress to SQL Server by setting the Progress DB up as a linked server in SQL Server, and using a stored proc SQL Server side to do an INSERT.

    Progress is a little funny with its data types (anyone's guess as to whether or not it enforces them), and as a result one of the tables generates a "string or binary data would be truncated" error.

    Since I don't mind if the data is truncated, I rebuilt the proc with SET ANSI_WARNINGS OFF but then got this error:

    Server: Msg 7405, Level 16, State 1, Procedure sp_DW_SA_OA_Load, Line 17

    Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

    Seems to be a bit of a Catch 22.  Has anyone got any workarounds or ideas?

    Unfortunately, I can't change anything Progress side because the database is part of a third party application.

  • If you don't mind truncation, how about casting the offending column(s) to char/varchar with a length that will fit into the destination column(s) ?

    Alternatively stuff it into staging table first, then take it from there.

    /Kenneth 

  • I've implemented the casting option and it's worked - thanks!

  • Progress doesn't have any limits on character data when the field is accessed using Progress 4GL. When you access the same field using SQL-92 ODBC driver it uses a property called SQL-WIDTH.

    Personally I'd go with turning the ANSI settings on so the truncation doesn't occur. You might not be worried about truncation now, but what about later.

    --------------------
    Colt 45 - the original point and click interface

  • Thanks!  I'll review the SQL Server side against the field widths in Progress and change SQL side as necessary.

    Having said that I want to limit time spent working with Progress as there are too many strange goings on!  May be compatibility issues, may be my complete lack of knowledge of Progress.  Maybe both. 

    I've just been getting overflow errors on datetime fields, even though dates were 1995-2004 and the SQL field type was datetime, but now I've CAST them into datetime on the INSERT and it works.

  • It doesn't look like I've solved the date issue.  I am getting the following error back from the load procedure:

    Server: Msg 7341, Level 16, State 2, Procedure sp_DW_SA_OA_Load, Line 65

    Could not get the current row value of column '[OPENACC]..[PUB].[oa_nltrans].docdate' from the OLE DB provider 'MSDASQL'. Conversion failed because the data value overflowed the data type used by the provider.

    [OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e21:  Data status returned from the provider: [COLUMN_NAME=docdate STATUS=DBSTATUS_E_DATAOVERFLOW]].

    All dates are well within SQL Server's range (1995-2004 roughly); some are nulls but nulls are allowed in the destination table.  I therefore can't understand why this is (supposedly) overflowing.

    As you can see, the rogue column is [OPENACC]..[PUB].[oa_nltrans].docdate.  If I do:

    SELECT CAST(docdate AS datetime) FROM OPENACC..PUB.[oa_nltrans]

    that does not return any errors (it returns valid datetime values and nulls).

    Has anyone come across this previously and can help?

  • Haven't run into this exact issue, but what about changing the destination datetime column to a char or varchar and see if the error prevails?

    /Kenneth

  • Good idea, I've tried it but unfortunately it's not working.  Same error message.

    That means it must be an overflow with the OLE DB provider and not SQL Server?

  • Hmmm.. it seems like it I think..

    Is this a linked server btw? If so, perhaps try to drop and recreate the linked server may help..?

    /Kenneth

  • I'm having the same problem. I've tried all suggestions (cast as varchar(100)and recreating the linked server. Any further suggestions? thanks.


    smv929

  • hate to say it, but I'm in the same situation.

    Anyone have any further thoughts on this?

    thanks

     

  • ok, I got a solution of some sort.

    I did not inlcude those fields that's giving a convesrion error in the insert statement. I just run a separte Update query and it worked. Don't know why though.

  • This thread is almost two years old

    Perhaps you'd better start a new thread with your specific problems, you environment, what errors you get and so on, so we may start fresh on your troubles.

    /Kenneth

  • i was just digging around as per usual, didn't really pay attention to the date of the thread.

    but it's cool. i've managed a work around for the errors i was getting

  • I have re-opend this post as I to am having this same problem between PROGRESS and SQL 2005

     

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=300832

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply