• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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?
0
PhillipsPlastics
Asked:
PhillipsPlastics
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now