I'm stuck on the following scenario. I have a database with a table with customer data and a table where I put records for monitoring what is happening on our B2B site.
The customer table is as follow:
The monitoring table:
In PARAMETER1 are customer guids as wel as other data types stored.
Now the question came to order our customers according their last visit date, the most recent visited customers must come on the top of a grid.
I'm using Entity Framework and I had problems of comparing the string and the guid type, so I decided to make a view on top of my monitoring table:
SELECT ID, CONVERT(uniqueidentifier, parameter2) AS customerguid, USERguid, CreationDate FROM MONITORING WHERE (dbo.isuniqueidentifier(parameter2) = 1) AND (parameter1 LIKE 'Customers_%' OR parameter1 LIKE 'Customer_%')
I imported the view in EF and made my Linq query. It returned nothing, so I extracted the generated SQL query. When testing the query in SQL Management Studio I got the following error: Conversion failed when converting from a character string to uniqueidentifier.
The problem lies in the following snippet (simplified for this question, but also gives an error:
SELECT *, ( SELECT [v_LastViewDateCustomer].[customerguid] AS [customerguid] FROM [dbo].[v_LastViewDateCustomer] AS [v_LastViewDateCustomer] WHERE c.GUID = [v_LastViewDateCustomer].[customerguid] ) FROM CM_CUSTOMER c
But when I do a join, I get my results:
SELECT * FROM CM_CUSTOMER c LEFT JOIN [v_LastViewDateCustomer] v on c.GUID = v.customerguid
I tried to make a SQL fiddle, but it is working on that site. http://sqlfiddle.com/#!3/66d68/3
Anyone who can point me in the right direction?
TRY_CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid
CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid
Views are inlined into the query and the
CONVERT can run before the
For some additional discussion see SQL Server should not raise illogical errors