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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Scott PletcherSenior DBACommented:
Please try:

master.dbo.fn_varbintohexstr

For example:

SELECT master.dbo.fn_varbintohexstr(convert(varbinary(8), account))
from accountTemp
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.