I am trying to get some SQL to execute but am getting the following error
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 4 in SELECT statement.
But cannot seem to figure out where the problem is??? Any help would be very much appreciated.
SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField,
ISNULL(CASE WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Integer') THEN CASE WHEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) IS NOT NULL THEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) ELSE CONVERT(NVARCHAR,
MEMBDATA.[dataInt]) END ELSE NULL END, '') AS MemberData
FROM (SELECT id, text
FROM dbo.umbracoNode
WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID LEFT OUTER JOIN
(SELECT nodeId, contentType
FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id LEFT OUTER JOIN
dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType LEFT OUTER JOIN
dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND
MEMBDATA.propertytypeid = MEMBTYPES.id LEFT OUTER JOIN
dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBLST.nodeId IS NOT NULL)
My SQL skills are fairly basic, so hope someone can help
~~~~~~~~~~~~ WORKING CODE ~~~~~~~~~~~~~
Managed to get it working and here is the code
SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField, MEMBTYPES.Alias AS MemberFieldAlias, MEMB.LoginName,
ISNULL(CASE
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar]
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext]
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate])
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN CONVERT(NVARCHAR, MEMBDATA.[dataInt])
ELSE NULL END, NULL)
AS MemberData
FROM
(SELECT id, text FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID
LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id
LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType
LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id
LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBLST.nodeId IS NOT NULL)