November 11, 2004 at 3:55 am
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.
November 11, 2004 at 5:45 am
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
November 12, 2004 at 2:27 am
I've implemented the casting option and it's worked - thanks!
November 14, 2004 at 2:38 pm
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
November 15, 2004 at 8:29 am
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.
November 17, 2004 at 10:35 am
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?
November 18, 2004 at 2:42 am
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
November 18, 2004 at 3:38 am
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?
November 18, 2004 at 3:54 am
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
February 2, 2006 at 2:10 pm
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
May 11, 2006 at 6:00 pm
hate to say it, but I'm in the same situation.
Anyone have any further thoughts on this?
thanks
May 11, 2006 at 7:09 pm
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.
May 15, 2006 at 1:54 am
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
May 15, 2006 at 5:53 pm
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
August 10, 2006 at 5:14 am
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