Blob as text

Hi,

I have a conversion application which convertts an access database to an sql server(different versions). I'm using stored procedures. The thins is that I export the OLe Object form access to SQL varbinary. what I do is to convert the binary data from the OLE Object to string using ToBase64String. The thing is that when I execute the SQL statement I get the following error:

Error:Operand type clash: text is incompatible with varbinary.

Can anyone tell me what I do wrong and how can I fix this? Thanks.

Can you post the SQL Statement that is being sent? At the very least print the text out and post it here. In the statement the value should look something like:

'010101'

and not like:

0x010101

If not, you might want to go to the language specific forum to ask there by posting the code you are trying to use.

|||

the value is something like:

'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='

|||

That is not binary, binary would be something like this 0x53516C20536572766572

what you posted is some (.NET) encrypted value that is stored in a column

The person/program who encrypted that is the person who will be able to decrypt that value also

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||well if you'd read the hole post you 'd notice that I've applied the ToBase64String function to the binary data. |||

That value looks okay enough. What about the query that is doing the entering? Can you capture that with profiler?
If this is a varchar or text column, that value should work just fine. For example:

CREATE TABLE testText
(
textValue text --use varchar(max) if this is SQL Server 2005
)
go
INSERT INTO testText
SELECT 'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='
go
SELECT *
FROM testText

textValue
-
DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==

So there is something else going on...

|||

Well as I've said I'm using SQL statements stored in a file. The statement is something like:

CREATE TABLE testBin
(
textValue varbinary

)
go
INSERT INTO testBin('DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg==')
go

|||

Try this

CREATE TABLE testBin
(
textValue varbinary(5000)

)
go
INSERT INTO testBin VALUES(convert(varbinary(5000),'DQpbUmVmZXJlbmNlXQ0KRmllbGQxPUFrdGVuVlouQU5SDQpGaWVsZDI9QWt0ZW5WWi5Qcm9qTlINCkZpZWxkMz1Ba3RlblZaLlJFRjENCkZpZWxkND1Ba3RlblZaLlJFRjINCkZpZWxkNT1Ba3RlblZaLkFDQVVTQQ0KRmllbGQ2PUtsaWVudFZaLlNob3J0Tm0NCkZpZWxkNz1LbGllbnRWWi5CT3J0DQpGaWVsZDg9R2VnbmVyVlouU2hvcnRObQ0KRmllbGQ5PUdlZ25lclZaLkJPcnQNCkZpZWxkMTA9QWt0ZW5WWi5BSU5SDQpGaWVsZFNlcD1UQUINCg=='))
go

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You are trying to convert a string literal that is not in hexadecimal format to varbinary implicitly and this will not work. You need to use either text/ntext in SQL Server 2000 or varchar(max)/nvarchar(max) in SQL Server 2005 for the destination column. Or you can cast the value from one type to another which hurts performance. See BOL topic below on how to specify values for various data types:

http://msdn2.microsoft.com/en-us/ms179899(SQL.90).aspx

And also the CAST topic that has a table showing various conversions possible (implicit/explicit).

http://msdn2.microsoft.com/en-us/ms187928(SQL.90).aspx

|||well do you have a better solution for importing blob data using stored sql statements? And this should work with sql 2000 and 2005 also.|||Would this help.

create table tmp(i int identity primary key,img image default '0x0')
insert tmp(img) values(default)

go
create proc usp
@.i int,
@.img image
as
declare @.ptr binary(16)
select @.ptr=textptr(img)
from tmp
where i=@.i

writetext tmp.img @.ptr @.img
go
declare @.b varbinary(8000)
set @.b=0x0000007B

exec usp 1,@.b

select *,convert(int,@.b) ,convert(int,convert(binary(4),img)) [img]
from tmp
go
drop proc usp
drop table tmp|||

Thanks for your reply! But it doesn't help; because I want to do everything with SQL statements. Meaning I want to export the Access database to a file which contains SQL statements, which can be run on using an SQL command interpreter like osql.exe or my own SQL command interpreter. So the blob has to be in the SQL statement stored in the SQL file.

Any idea?