Last updated on July 31st, 2023 at 11:22 am
I ran across this error when I tried passing a hexadecimal values (0x1) from one SSRS report to another SSRS report. And this was seriously frustrating as we all “know” the 0x01 is hex for 1. So why doesn’t SQL Server Report Services (SSRS) see that 0x1 as a integer or number? In this article we will talk about what happened and how to fix it. The secret solution is to convert the value to a Integer, with a mid step of converting the string to a binary value.
SSRS Hexadecimal Values Error
This is the exact error you will see within Report Designer.
Conversion failed when converting the varchar value ‘0x1’ to data type int. (Microsoft SQL Server, Error: 245)
How to Solve the Hex Value problem?
This took a lot of digging but in the end it turns out that SQL Server Reporting Services (SSRS) will treat the hex value as a string and not an integer or a number! It also didn’t help that I was mixing both hex and integers in the same query. That is a story for another day.
What did I do to fix this problem? Fortunately there was an easy solution. Within the SQL query I simply converted my hex values over to varbinary then to integers. You will end up with something like the example below.
Declare @i as varchar(25) = '0x65'
Select convert( int, convert( varbinary, @i,1)) as 'int'Once I converted determine what the problem was It only took me a few minutes to resolve, but now everything is good. Now my SSRS report can pass a hexadecimal values from one report to another report and more importantly not create an error when doing do.
If you have any questions about hexadecimal values in SSRS reports, please leave a note in the comment section below. Or, you can contact me on Twitter @GarthMJ.