Heterogeneous query error with linked server

Question

Heterogeneous query error with linked server

Paul Cresham

Hall of Fame

Points: 3623
More actions
November 11, 2004 at 3:55 am

#60647

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.

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

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

philcart SSC-Forever Points: 47796 More actions · Answer 3

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

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

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.

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 5

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?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

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

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 7

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?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

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

smv929 SSCrazy Points: 2889 More actions · Answer 9

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

Sheckster SSC-Addicted Points: 401 More actions · Answer 10

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

Anyone have any further thoughts on this?

thanks

Sheckster SSC-Addicted Points: 401 More actions · Answer 11

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 12

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

Sheckster SSC-Addicted Points: 401 More actions · Answer 13

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

Alex Kirwan Old Hand Points: 332 More actions · Answer 14

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