data type conversion error - SQL to Pervasive.sql linked

[rwaldron]

Hi all,
First off.. I was onto pervasive who claim that this is not a pervasive problem but a data conversion problem..
I think the problem is due to the fact that pervasive.sql v7.9 deals with Nulls as 0... Please help..

I have a linked server from Sql to pervasive.sql
The query falls over as soon as it hits the first record of type 'date' in pervasive that contains a Null( I assume it is Null but the data itself is shown as 0).

Pervasive told me to try and cast the field as char.
i did this but it still falls over when it hits the first "Null" record.


If I run

select top 55 ref_number,cast(date_despatched as varchar(50)) as mycolumn
FROM OPENQUERY(sbase_local, 'select date_despatched from jobs')

I get the following so the cast is working to a point

Mar 14 2002 12:00AM
Mar 25 2002 12:00AM
Feb 27 2002 12:00AM
May 13 2003 12:00AM

If I go one more than this
select top 55 ref_number,cast(date_despatched as varchar(50)) as mycolumn
FROM OPENQUERY(sbase_local, 'select ref_number ,date_despatched from jobs')

I get
Error converting data type DBTYPE_DBDATE to datetime.

This is because this is the first record where date_despatched is null.The data from within pervasive shows the record as 0(Zero) which I assume means Null which is ok as a record may not have a date_despatched date yet.

I have tried all different types of cast and conversion but sql seems to be having a problem dealing these null date fields.

Please help

Ray

 

[mirtheil]

First, PSQL can treat a number of different values as null. It depends on the data type.
Second, what is the result of the query "select ref_number ,date_despatched from jobs" through the Pervasiv COntrol Center (in both Grid and Text mode)? Do you get data? If so, then the problem is in how SQL Server is trying to interact with it.
What happens if instead of Casting at the SQL Server level, you Convert at the PSQL level? It would look something like:
select top 55 ref_number,date_despatched as mycolumn
FROM OPENQUERY(sbase_local, 'select ref_number ,Convert(date_despatched, SQL_VARCHAR) from jobs')

The problem may not even be Pervasive or SQL Server, it may be the way the Btrieve Application that's populating the data. Some applications used 00/00/0000 as a "Null" or empty date. This causes problems for ODBC (the ODBC spec defines a date as being a month of 1 to 12, day of 1 to 31 dependant on the month and a year of 0000 to 9999).

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com