SQL Server Convert or Cast function

I am trying to convert a decimal into a hexadecimal string in a table.  I can run the select query just fine (Select convert(varbinary(8), account) from accountTemp) but cannot run either an update on the table or a new insert to store the value permanently. (update accountTemp set account = convert(varbinary(8), account) -- OR -- Insert into account select convert(varbinary(8),account) from accountTbl

Any suggestions as to how I can have this be converted and stored into the table?
PhillipsPlasticsAsked:
Who is Participating?
 
PhillipsPlasticsAuthor Commented:
It seemed to do the same thing as convert but what I am actually ending up doing is setting the column account to be varbinary(8) and when I read the data into the table it automatically converts the decimal to hex.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Try doing it through a variable?


declare @tmp VarBinary(8)
set @tmp = (Select convert(varbinary(8), account) from accountTemp)
update accountTemp set account @tmp
where
-- ...  fill in where ... --
0
 
DcpKingCommented:
Look at the definition of Convert. You're changing the type from whatever Account is into a varbinary type. I'll bet you Account is not a varbinary type of field!

Take a look at http://rextang.net/blogs/work/archive/2008/01/13/5855.aspx for info on converting your varbinary out into hex.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
Please try:

master.dbo.fn_varbintohexstr

For example:

SELECT master.dbo.fn_varbintohexstr(convert(varbinary(8), account))
from accountTemp
0
 
PhillipsPlasticsAuthor Commented:
Well this is what I ended up doing since nothing else was working...

declare @tmp varbinary(8);
set @tmp = (CONVERT(varbinary(8),66082));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66082;
set @tmp = (CONVERT(varbinary(8),514));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 514;
set @tmp = (CONVERT(varbinary(8),544));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 544;
set @tmp = (CONVERT(varbinary(8),512));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 512;
set @tmp = (CONVERT(varbinary(8),66080));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66080;
set @tmp = (CONVERT(varbinary(8),66048));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66048;
set @tmp = (CONVERT(varbinary(8),546));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 546;
set @tmp = (CONVERT(varbinary(8),66050));
update Phonesearch_DB.dbo.accountTemp set account = @tmp where accountDisabled = 66050;
select * from phonesearch_db.dbo.accountTemp;

Open in new window

0
 
DcpKingCommented:
Did fn_varbintohexstr not work for you?
0
 
DcpKingCommented:
Clever! And there we all were assuming that you needed to keep the original value. Thanks for wrapping it up.
0
 
PhillipsPlasticsAuthor Commented:
None of the other options listed worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.