Convert text blob to nvarchar from Firebird to SQL Server

4
votes

I am using SQL Server and have got a Firebird database as a linked server in my SQL Server.

I am trying to do an OPENQUERY to import data from the Firebird db;

SELECT * FROM OPENQUERY(Workbench, 'SELECT * FROM table_name')

and am getting the following error.

OLE DB provider "MSDASQL" for linked server "Workbench" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 4
Cannot get the current row value of column "[MSDASQL].NOTES" from OLE DB provider "MSDASQL" for linked server "Workbench".

The data type NOTES field in the Firebird db is blob. If I omit the field NOTES the query executes and the results are imported correct.

But I need the information in the NOTES field and am looking for a way to type cast the blob field to nvarchar. I believe that this should be done within the Openquery (i.e. in the Firebird side of the query).

Could somebody suggest a pure SQL solution to convert blob to nvarchar in Firebird Please?

3
sqlsql-servertype-conversionfirebirdopenquery
What is the character set of the text blob, and what is your connection character set? - Mark Rotteveel
@MarkRotteveel: Thank you for your reply Mark.. Sorry but how could I check that character set? Could you tell me please?? - mithilatw
For the blob character set: look at the DDL of the table, for the connection character set: look at the connection string of your ODBC datasource. - Mark Rotteveel

3 Answers

1
votes

look at Microsoft KB OLE DB conversion errors may occur

if this not work for you than is your field NOTES big text or reletive small < 32767 chars? 32767 i got from here - limitations in Firebird If it is small - try to cast it to varchar 

SELECT CAST(NOTES AS VARCHAR(32767)), other fields FROM TABLE_NAME

First try with smallest notes and smaller varchar cast - and look if it work If yes than maybe - problem is with blob at all

1
votes

Late answer, but maybe it can help someone...

You can cast the blob to a varchar by this statement:

SELECT CAST(SUBSTRING(BlobColumn FROM 1 FOR 32000) AS VARCHAR(32000)) AS NewBlobColumn
FROM TABLE_NAME

Then you can use this in the OPENQUERY-Statement

0
votes

I believe Firebird blob is image data type in MS SQL, so casting to varbinary and then to varchar should help. Have you tried this?

select cast(cast(NOTES as varbinary(max)) as varchar(max)) from openquery(Workbench,'select * from table_name')

Hope that helps.