Conversion failed when converting the ****** value '******' to data type ******

Question

I'm getting this error from SQL Server on SSMS 17 while running a giant query:

Conversion failed when converting the ****** value '******' to data type ******

I have never seen this with ****'s before, and google searching seems to come up with nothing. Is there a known cause for why SQL Server would provide this message with asterisks?

Answer 1

Are you getting data from a view? If so, you might be having issues with the view you're using. For example, in my case, I was pulling from a view and when I tried to view it in SSMS it showed me more details about what the error was.

Answer 2

My error was also: Conversion failed when converting the ****** value '******' to data type ******.

My issue ended up being in my join, which was joining a varchar(50) field on an int field.

SELECT TOP (10) Product.Name, ProductDetails.Price
FROM Product
--the ProductDetails.Product field is a varchar field that contains 
--a string of the ProductID which is an int in the Product table
FULL JOIN Product.ProductID = ProductDetails.Product 

I corrected this by casting the int field to a varchar

FULL JOIN CAST(Product.ProductID as varchar(50)) = ProductDetails.Product

P.S. I went back and later changed to a Left outer join which made more since for our business logic. Also I did not test this code, it is modified for illustration purposes.

Details on reproducing: Sometimes the query would work fine. I found I personally had issues when I had TWO string comparisons in my WHERE clause e.g.

WHERE field like '%' + @var + '%'
OR field2 like '%' + @var2 + '%'

If I had just one comparison, it seemed to work fine.

Answer 3

I had the same error when trying to combine an nvarchar and int into a single string. Usually it tells me the data type where I screwed up but occasionally its the error you got.

I went from

SELECT ';' + [myNumber]

to

SELECT ';' + CAST([myNumber] as nvarchar(100))

which solved it for me.

So as the others have suggested, I guess it is something similar and you need to CAST or CONVERT one of your values to match the rest.

Answer 4

The asterisks appear when you do not have enough permissions. It seems they appear only in some scenarios (in my case, when using user-defined functions).

I don't know why, but it seems that if you GRANT UNMASK, you will see the real values.

Answer 5

For me the error was caused by an unusual value being introduced to a column I was using in a case statement. Adding a clause to the statement to cater for the new value resolved the issue.

If you can't spot where the problem is you need to run any sub-queries in isolation to see if they run (my statement was in a sub-query) and then start commenting out joins/selected columns until the query runs and you've pinpointed the issue.