Conversion failed because the data value overflowe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conversion failed because the data value overflowe

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-07-24 : 05:31:04

Hi all,
I use sql server 2005 with a linked server to Btrieve.
My Problem is that there is a date field in BTrieve that can contain the value 00/00/0000.

When I run and open query sql runs ok until it hots one of these dates.The I get the error "Conversion failed because the data value overflowed the data type used by the provider."

I know this is because SQL is saying what the hell is this 00/00/0000?
But I need all these columns returned..

The only way so far that I can get all columns back is by using convert to varchar but this takes along time to run..
Any other ideas?

eg:Below takes 8Mins to run but I get 00-00-0000 returned.
Need to speed things up

SELECT *
FROM OPENQUERY(cellular, 'SELECT refnumber,Convert(InWorkShopDate, SQL_VARCHAR)as InWorkShopDate,FROM JOBSE ')

Ray

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:17:04

[code]SELECT *
FROM OPENQUERY(cellular, 'SELECT RefNumber, NULLIF(InWorkShopDate, ''00/00/0000'') AS InWorkShopDate FROM JobSE')[/code]

E 12°55'05.25"
N 56°04'39.16"

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-07-24 : 06:53:27

Hiya and thx for replying.
I have had a look at NULLIF before.
I tried your code and got the following....any more help?

OLE DB provider "PervasiveOLEDB" for linked server "cellular" returned message "[LNA][Pervasive][ODBC Engine Interface]Error in row.".
OLE DB provider "PervasiveOLEDB" for linked server "cellular" returned message "[LNA][Pervasive][ODBC Engine Interface]Invalid date, time or timestamp value.".
OLE DB provider "PervasiveOLEDB" for linked server "cellular" returned message "[LNA][Pervasive][ODBC Engine Interface]Expression evaluation error.".
Msg 7330, Level 16, State 2, Line 16
Cannot fetch a row from OLE DB provider "PervasiveOLEDB" for linked server "cellular".

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 07:00:55

is NULLIF valid in Pervasive?

Em

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-07-24 : 07:07:19

I think NullIF is valid in pervasive.
The query starts to run but still fails on invalid date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 07:10:59

CASE must work?

SELECT	*
FROM	OPENQUERY(cellular, 'SELECT RefNumber, CASE WHEN InWorkShopDate = ''00/00/0000'' THEN NULL ELSE InWorkShopDate END AS InWorkShopDate FROM JobSE')

E 12°55'05.25"
N 56°04'39.16"

rwaldron
Posting Yak Master

131 Posts

Posted - 2008-07-24 : 10:23:44

Still Faile usung case with
OLE DB provider "PervasiveOLEDB" for linked server "cellular3" returned message "One or more errors occurred during processing of command.".
OLE DB provider "PervasiveOLEDB" for linked server "cellular3" returned message "[LNA][Pervasive][ODBC Engine Interface]Invalid date, time or timestamp value.".
OLE DB provider "PervasiveOLEDB" for linked server "cellular3" returned message "[LNA][Pervasive][ODBC Engine Interface]Error in predicate: InWorkShopDate = '00/00/0000' ".
Msg 7321, Level 16, State 2, Line 19
An error occurred while preparing the query "SELECT RefNumber, CASE WHEN InWorkShopDate = '00/00/0000' THEN NULL ELSE InWorkShopDate END AS InWorkShopDate FROM JobSE" for execution against OLE DB provider "PervasiveOLEDB" for linked server "cellular3".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 11:29:24

You have to check to Pervasive db engine how to do CASES or NULLIF's.
But with the code above, you can see the logic behind converting '00/00/0000' dates to null.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources