Dynamic sql and varbinary data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic sql and varbinary data

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2008-12-03 : 11:49:28

Hi,
I'm trying to use dynamic sql to create an insert script to provide to a client. Essentially I would like to provide a sql script to load some data into thier database. In the past, I have used dynamic sql to script out insert statements.

Here is a very simple example:


select 'insert into table (field1,field2) 
values (' + field1 + ',' + field2 + ')' from table

Anyway, one of the tables has a varbinary column. I'm running into problems converting it into a hexstring (or a readable text value).

If I query the field I get a hexstring back.


select 
	vector 
	,convert(varchar(100), vector) As VectorVar
	,convert(nvarchar(100), vector) As VectorNVar
from 
	table

Vector
-----------------------------------
0x8204F9E6E062BCE08DFE0A4E045BDC0E

VectorVar
-----------------------------------
fÕiÚHf“š©Î©0 

VectorNVar
-----------------------------------
????????

I can't just add the vector field to my dynamic sql because I get this error.
"Invalid operator for data type. Operator equals add, type equals varchar."

So I need to convert it into a string first (varchar,nvarchar??) but when I do the value gets messed up. Does anyone know how to return the actual
0x8204F9E6E062BCE08DFE0A4E045BDC0E value in a string format?

Thanks,
Nic

Nic

nic
Posting Yak Master

209 Posts

Posted - 2008-12-03 : 12:16:50

Hi,
I found the solution:
master.dbo.fn_varbintohexstr()

so the select statement (to dynamically build an insert statement) will be like this

select select 'insert into table (field1,field2)
values (' + master.dbo.fn_varbintohexstr(vector) + ',' + field2 + ')' from table

Thanks,
Nic

Nic

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 12:22:29

master.dbo.fn_varbintohexstr(<varbinvalue>) should do the trick for you, if I understand your problem correctly.

___________________________
Geek At Large

nic
Posting Yak Master

209 Posts

Posted - 2008-12-03 : 12:27:07

Note: the statement will be a little more complex because I need to covert the hexstring back into varbinary (so it will be something like this):

select select 'insert into table (field1,field2)
values (convert(varbinary(100),' + master.dbo.fn_varbintohexstr(vector) + '),' + field2 + ')' from table

In the end this solution is nice because all the data is provided in a single script that the client can run.

Thanks,
Nic

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources