Convert text block to nvarchar from Firebird to SQL Server

Question

I use SQL Serverand have a database Firebirdas a linked server on my SQL Server.

I am trying to do OPENQUERYto import data from Firebirddb;

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

and I get 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". 

Data type field NOTESin Firebirddb blob. If I omit the field NOTES, the query is executed and the results are imported correctly.

But I need the information in the field NOTES, and I'm looking for a way to type castblob the field up nvarchar. I believe that this should be done in OPENQUERY(i.e., in the Firebird side of the request).

Can anyone suggest a clean solution SQLto convert blobto nvarcharto FirebirdPlease?

Answer 1

Microsoft KB OLE DB

. < 32767 ? 32767 - Firebird - varchar

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

varchar - , , , , blob

Answer 2

, Firebird blob - image MS SQL, varbinary varchar . ?

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

, .

Answer 3

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

You can apply blob to varchar with the following expression:

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

Then you can use this in an OPENQUERY-Statement