Convert text blob to nvarchar from Firebird to SQL Server
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?
Answer
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