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?
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.
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.
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.
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.
I had a similar issue. The cause was the multiple subqueries within a UNION statement. Somehow, one of the subqueries was out of order in the first part of the UNION and that caused the statement to attempt to UNION the two fields that were different data types.
There was several hundred lines of code already in the statement so, it made it a bit challenging to find where the subqueries were out of order. Once the offender was located, it was placed in the correct order so that the portions of the UNION statement that it was attempting to union weren't trying to UNION two different data types.
in my case the problem was that in the time parameter, someone use -1 for nulls value.
so when I wrote
where TripStartTime = -1
i got the error massage
Conversion failed when converting the ****** value '******' to data type *****
to solve it i add ''
where TripStartTime ='-1'
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.
