Bad data in InterBase causes MS SQL error

Discuss the Linked Server tehcnology, MS SQL Server and Business Intelligence environment

Bad data in InterBase causes MS SQL error

Postby brettge » 07 Feb 2008, 01:19

We're using IBProvider V3 and MS SQL Express 2005 via linked server, Windows XP SP2.

Our application requires read-only access to an existing InterBase database. We've successfuly created the linked server and can select data. However, in cases where a row of data contains bad data, SQL returns an error. Here are the two cases we've seen:

In the first case, the date '1/15/0272' is stored in Interbase. We can view this value in IBConsole without error. When trying to do a select from the table containing this value from MS SQL via linked server, this causes SQL to give the error "Error converting data type DBTYPE_DBTIMESTAMP to datetime." Obviously, the year 0272 is outside of SQL's datetime range.

In the second case, a person't name contains an invalid, unprintable character. When viewed in IBConsole, this shows up as a square block in place of a character in the middle of the name "Michael" without an error. When we try to select from this table from MS SQL via linked server, this bogus character causes an error, "Cursor fetch row failed. Arithmetic exception, numeric overflow or string truncation. Cannot transliterate characters between character sets."

Here's the question: is there any way to identify these bogus values and filter them out with a smarter SQL query?

We're doing "select field from LinkedServer...TableName" syntax now. I've tried adding a where clause to strip out the bogus date like this:

"select baddate from linkedserver...tablename where baddate > '12.01.01 12:00:00' " but this gives the same error.

How do you recommend dealing with bad data like this when querying from SQL?
Brett G.
brettge
 
Posts: 5
Joined: 07 Feb 2008, 01:01

Postby brettge » 07 Feb 2008, 19:41

We've found a solution to half of the problem, but still need help with the other half. Here's what we found:

If we use openquery syntax in SQL to pass a query to InterBase that identifies rows with dates outside the SQL datatime range without returning these dates to SQL, then we can find bogus dates without generating an error and ask the customer to fix them in the source database. Here's our query to find bad dates:

select * from openquery(Mplus, 'select patient_id from patient where date_of_birth < ''1/1/1900'' or date_of_death < ''1/1/1900''')

This leaves only the problem of identifying bad text data. Can you suggest a way to identify rows containing bogus unicode values in text fields?
Brett G.
brettge
 
Posts: 5
Joined: 07 Feb 2008, 01:01

Bad unicode char solved

Postby brettge » 07 Feb 2008, 19:59

To prevent truncation errors when querying rows containing bad characters:

When initializing provider, set "ctype=none"
instead of "ctype=win125x" (the block char comes across as a comma.)

That solves these data conversion issues for us -- no need for a reply.

Thanks.
Brett G.
brettge
 
Posts: 5
Joined: 07 Feb 2008, 01:01

Feature request: conn string property to filter bad dates

Postby brettge » 08 Feb 2008, 03:58

MS Sql won't store dates older than 1753. I'd love to see a connectioin string property that, when turned on, causes the provider to set any dates to NULL if they are prior to 1753, so that these won't cause errors in SQL.
Brett G.
brettge
 
Posts: 5
Joined: 07 Feb 2008, 01:01

Re: Bad data in InterBase causes MS SQL error

Postby daspeac » 17 Nov 2009, 21:57

Hi Brett. Yes, I know a suitable solution for the repair sql 2005 bkp file, it quickly eliminates database errors. Hope it helps other users (I realize that the topic is too old) and not considered to be spam
daspeac
 
Posts: 1
Joined: 17 Nov 2009, 21:45


Return to Using IBProvider in MS SQL Server

Who is online

Users browsing this forum: No registered users and 0 guests

cron