August 10, 2006 at 5:13 am
Hi all,
This is a follow up to a previous post from aprox 2 years ago.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=145803
I have stored procedures set up to import tables from a PROGRESS DB to SQL 2005 via linked server.
The import process works fine Id say 50% of the time. Usually works fine after business hours when all users are logged out of system. However I do get this error message and it fails about half the time I run the script. I noticed this problem was raised in the link above so any help adive would be greatly apreciated!
I presumed it was some kind of dat type conversion issue??.... PROGRESS stores date as a datatype DATE - dd/mm/year I have the comolmn in my SQL set up as DATETIME. Could this be a problem?? I dont think this is the problem, as I said the script does work 50% of the time. Also if I import the date column alone then it seems to work no problem.
My thinking is that it is to do with the data on the PROGRESS side is forever updating (as thats our LIVE DB) so when the data is copying over from PROGRESS to SQL it can fail due to an increase in data from PROGRESS?? - could that be possible reason to fail??
Here is the message in full:
OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7341, Level 16, State 2, Procedure ImportButlersVAT_LIVE, Line 4
Cannot get the current row value of column "[MSDASQL].fdate" from OLE DB provider "MSDASQL" for linked server "PRO_LIVE_SQL". Conversion failed because the data value overflowed the data type used by the provider.
As always - Thanks in advance!
August 14, 2006 at 8:00 am
This was removed by the editor as SPAM
June 18, 2007 at 7:49 am
I am having the exact same problem. Something funky is going on behind the scenes with the OLE DB engine and what it sees in the field. I know the data type for my column s/b numeric(12,2) yet the error keeps coming back:
Cannot get the current row value of column "[MAJESTI]..[root].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "MAJESTI". Conversion failed because the data value overflowed the data type used by the provider.
I tried casting as varchar(max) and that wouldn't work.
I know you can't trim numbers(or maybe you can) but I'm pretty sure that wouldn't work b/c it's a data overflow issue.
I get the same error even if I do a insert with case when <=9999 else '0'
*************************************************************
What I see with a link to this table through Access instead of SQl Server:
I can see the number with Access 1997:
| 3.53E+11 |
, or 353,223,500,185.92
And I only see this number with Access 97. In Access 2003 I can NOT see the number because it throws the following error message and puts #Error in the field.
"The decimal fields precision is to small to accept the numeric you attempted to add."
*************************************************************
Now that I actually know what the number is that's causing the error message in Sql Server I tried casting as numeric(36,2) and I still get the error message:
Cannot get the current row value of column "[MAJESTI]..[root].[ORTIVH].IVH_DISC_AMT" from OLE DB provider "MSDASQL" for linked server "MAJESTI". Conversion failed because the data value overflowed the data type used by the provider.
Anyone have a clue what I could do to get around/fix this problem with eliminating the column? Any help would be greatly appreciated.
March 19, 2008 at 2:28 pm
This won't help much, but...from what I understand, Progress allows any size value to be inserted into a field even though the schema may have char length defined. Don't know why, but that's how it is. However, odbc drivers and the rest of the database world seems to care about char lengths and precision. They fail when they encounter such a value.
In some cases , you can get it to work:
1. for strings, you can use left(field,x) to limit the number of fields returned. However, in some cases, the overstuffed field maybe too big and even that doesn't work. I've had some success importing data into a staging table having field widths of 200. Again, even that sometimes does not work.
2. For numbers or dates that throw an "exceeds precision" error, you might try using a progress query...
select to_char(price) "price" from table
Then in the sql server query that handles that result set, use...
select price = cast(price as money) from...
Apparently, you're suppossed to be able to define in the Progress data dictionary data type limits for one or more fields, so that it resolves this situation. But I have not gotten a clear answer on it.
smv929
July 7, 2008 at 11:59 am
I'm having the same problems with a Pervasive database when linked to SQL Server. It seems to only be a problem with date fields.
July 7, 2008 at 3:08 pm
the DB doesn't actually 'store' the data like dd/mm/year. Usually it's a 32/64bit integer based from some starting date counting x units of time forward. It's possible that when you transfer a date to a datatime, it may be converting incorrectly. Are you dates correct for data that has transfered?
July 7, 2008 at 3:12 pm
I can't see any dates that get transfered. I only get an error message on my query. If I link the tables into Access, the dates come across correctly.
October 27, 2008 at 5:04 pm
Maybe will help to someone:
here is what i did,
select * from openquery
(LINK_LINUX, 'SELECT id, inf_date_open, inf_date_clos, inf_prob_type_no,
CAST(inf_prob_desc AS varchar(4000)) AS inf_prob_desc,
CAST(inf_vp_prob_desc AS varchar(4000)) AS inf_vp_prob_desc ,
inf_last_upda, inf_last_upda_by, inf_nickname,
inf_inpu_type_no, inf_prob_cate_no FROM public.Info')
MK.
March 6, 2009 at 8:51 am
I was having the same problem. I would get this errors for date fields. I used Linked server and converted all the dates to character by using TO_CHAR. Apparantly it worked. I still have problems making it work through DTS though. Its interesting that it doesnt like the CHAR fields.
May 11, 2009 at 9:59 am
I have come across the same type of problems linking to EnterpriseDB. My first impression is that the combination of MSDASQL and the ODBC driver I am using are not a matched pair. I have [experimented]eliminated columns that do not provide the result of a query and recreated the linking to see how far I can go. The above observation is based on this experiment.
September 17, 2009 at 5:29 pm
Hi All,
I am also gettitg error like below any help me out
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'SQLOLEDB'. 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 'SQLOLEDB' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=bigintColumn STATUS=DBSTATUS_E_DATAOVERFLOW]].
am trying to use below select stmt
select bigintColumn
from OPENDATASOURCE('SQLOLEDB','Data Source=<>;User ID=uis;Password=pwd').mydb.dbo.myview as
Thanks
September 18, 2009 at 6:49 am
I have been successfully able to get around it by using to_char(numeric_field) as in
select
item,
cast(units as int)
from openquery(linkedserver,
'select item,to_char(sum(units)) units
from pub.tbl
where x=y
group by item')
The same goes with openrowdataset(). You'd have to spell out the fields instead of using select *.
November 18, 2009 at 4:28 am
hi friends,
I faced the same issue in a slightly different scenario. We were using a link server to connect to an SQL Analysis Services database and when we actually ran an MDX query against the lnked server we got the error "Conversion failed because the data value overflowed the data type used by the provider". On closer inspection of the query we found that the MDX query had a calculated member in the query and the denominator part was becoming zero in certain scenarios, in which case it will return a number with too many decimal points. we modified the query to eliminate division by zero and this error got solved. So I guess the large decimal precision returned by the SSAS engine was too much for the MSOLAP provider to handle in my case.
Hope this helps .
Regards
Hari
December 30, 2014 at 3:36 am
Try rounding the column in the where clause and select it as a datetime field. It worked for me. problem is there maybe a date which is further than year 9999 and you will be able to find the rows which are causing you problems. Copy and paste the number into excel and change the format in excel to date and itll show you the date that its trying to state and you may face a ########## which is the problem.
SELECT
CASE WHEN [Column]< 2958465
THEN cast([Column] as datetime) ELSE null END AS [Column]
FROM
OPENQUERY ([LinkedServer],'select round([Column],4) [Column]from [Table] ')
September 19, 2016 at 10:13 am
Mladen Krstonosic (10/27/2008)
Maybe will help to someone:here is what i did,
select * from openquery
(LINK_LINUX, 'SELECT id, inf_date_open, inf_date_clos, inf_prob_type_no,
CAST(inf_prob_desc AS varchar(4000)) AS inf_prob_desc,
CAST(inf_vp_prob_desc AS varchar(4000)) AS inf_vp_prob_desc ,
inf_last_upda, inf_last_upda_by, inf_nickname,
inf_inpu_type_no, inf_prob_cate_no FROM public.Info')
MK.
Marvelous Mladen!! After 2 years you posted your comment it just solved my problem today!! Thank you!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply