Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

• elham_azizi_62

  SSCertifiable

  Points: 5095

  More actions

  January 31, 2015 at 1:06 am

  #297301

  hello all.

  I have below view :

  /****** Object: View [dbo].[icanVW_GetLetters] Script Date: 01/29/2015 10:47:15 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  create VIEW [dbo].[icanVW_GetLetters]

  AS

  SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS Rownumber, *

  FROM (SELECT tblSCTInternalLetter.ID, '' AS ReceiverOrg, '' AS SenderOrg, tblSCTInternalLetter.Subject, E2.FirstName + ' ' + E2.LastName AS SenderName,

  P2.Name AS SenderRole, E1.FirstName + ' ' + E1.LastName AS ReceiverName, P1.Name AS ReceiverRole, tblSCTInternalLetter.PVSCDate AS SendDate,

  tblSCTInternalLetter.Text AS Comments, '' AS SendMethod, '' AS SecurityLevel, '' AS LetterType, 'نامه داخلی' AS Type, tblSCTInternalLetter.AssignCode,

  '1' + cast(tblSCTInternalLetter.ID AS nvarchar) AS UniqueID

  FROM tblSCTInternalLetter INNER JOIN

  tblPosition P1 ON tblSCTInternalLetter.ReceiverPositionID = P1.ID INNER JOIN

  tblPosition P2 ON tblSCTInternalLetter.SenderPositionID = P2.ID INNER JOIN

  tblEmployee E1 ON tblSCTInternalLetter.ReceiverEmployeeID = E1.ID INNER JOIN

  tblEmployee E2 ON tblSCTInternalLetter.SenderEmployeeID = E2.ID

  UNION ALL

  SELECT tblSCTInputLetter.ID, '' AS ReceiverOrg, tblOrganizations.Name AS SenderOrg, tblSCTInputLetter.Subject, tblSCTInputLetter.SenderOrgDesc AS SenderName,

  '' AS SenderRole, tblEmployee.FirstName + ' ' + tblEmployee.LastName AS ReceiverName, tblPosition.Name AS ReceiverRole,

  tblSCTInputLetter.PVSentDate AS SendDate, tblSCTInputLetter.Text AS Comments, tblSCTLetterSendMethodL.Description AS SendMethod,

  '' AS SecurityLevel, tblSCTInputLetterTypeL.Description AS LetterType, 'نامه وارده' AS Type,

  tblSCTInputLetter.AssignCode, '2' + cast(tblSCTInputLetter.ID AS nvarchar) AS UniqueID

  FROM tblSCTInputLetter INNER JOIN

  tblPosition ON tblSCTInputLetter.ReceiverPositionID = tblPosition.ID INNER JOIN

  tblEmployee ON tblSCTInputLetter.ReceiverEmployeeID = tblEmployee.ID INNER JOIN

  tblOrganizations ON tblSCTInputLetter.SenderOrgID = tblOrganizations.ID LEFT OUTER JOIN

  tblSCTLetterSendMethodL ON tblSCTInputLetter.ReceiveMethodID = tblSCTLetterSendMethodL.ID LEFT OUTER JOIN

  tblSCTInputLetterTypeL ON tblSCTInputLetter.InputLetterTypeID = tblSCTInputLetterTypeL.ID

  UNION ALL

  SELECT tblSCTOutputLetter.ID, tblOrganizations.Name AS ReceiverOrg, '' AS SenderOrg, tblSCTOutputLetter.Subject,

  tblEmployee.FirstName + ' ' + tblEmployee.LastName AS SenderName, tblPosition.Name AS SenderRole,

  tblSCTOutputLetter.ReceiverOrgDesc AS ReceiverName, tblSCTOutputLetter.ReceiverPosDesc AS ReceiverRole, tblSCTOutputLetter.PVSCDate AS SendDate,

  tblSCTOutputLetter.Text AS Comments, tblSCTLetterSendMethodL.Description AS SendMethod, '' AS SecurityLevel,

  '' AS LetterType, 'نامه صادره' AS Type, tblSCTOutputLetter.AssignCode, '3' + cast(tblSCTOutputLetter.ID AS nvarchar) AS UniqueID

  FROM tblSCTOutputLetter INNER JOIN

  tblPosition ON tblSCTOutputLetter.SenderPositionID = tblPosition.ID INNER JOIN

  tblEmployee ON tblSCTOutputLetter.SenderEmployeeID = tblEmployee.ID INNER JOIN

  tblOrganizations ON tblSCTOutputLetter.ReceiverOrgID = tblOrganizations.ID LEFT OUTER JOIN

  tblSCTLetterSendMethodL ON tblSCTOutputLetter.SendMethodID = tblSCTLetterSendMethodL.ID ) b

  GO

  but when I want to create it on my database returns below error:

  Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

  I changed my code like this:

  /****** Object: View [dbo].[icanVW_GetLetters] Script Date: 01/24/2015 14:42:20 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  create VIEW [dbo].[icanVW_GetLetters]

  AS

  --

  SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) COLLATE SQL_Latin1_General_CP1_CI_AS AS Rownumber,*

  FROM (SELECT tblSCTInternalLetter.ID, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverOrg, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderOrg, tblSCTInternalLetter.Subject , E2.FirstName + ' ' + E2.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderName,

  P2.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderRole, E1.FirstName + ' ' + E1.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverName, P1.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverRole, tblSCTInternalLetter.PVSCDate COLLATE SQL_Latin1_General_CP1_CI_AS AS SendDate,

  tblSCTInternalLetter.Text COLLATE SQL_Latin1_General_CP1_CI_AS AS Comments, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SendMethod, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SecurityLevel, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS LetterType, 'نامه داخلی' COLLATE SQL_Latin1_General_CP1_CI_AS AS Type, tblSCTInternalLetter.AssignCode,

  '1' + cast(tblSCTInternalLetter.ID AS nvarchar) COLLATE SQL_Latin1_General_CP1_CI_AS AS UniqueID

  ,'' COLLATE SQL_Latin1_General_CP1_CI_AS as SenderCode

  FROM tblSCTInternalLetter INNER JOIN

  tblPosition P1 ON tblSCTInternalLetter.ReceiverPositionID = P1.ID INNER JOIN

  tblPosition P2 ON tblSCTInternalLetter.SenderPositionID = P2.ID INNER JOIN

  tblEmployee E1 ON tblSCTInternalLetter.ReceiverEmployeeID = E1.ID INNER JOIN

  tblEmployee E2 ON tblSCTInternalLetter.SenderEmployeeID = E2.ID

  UNION ALL

  SELECT tblSCTInputLetter.ID, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverOrg, tblOrganizations.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderOrg, tblSCTInputLetter.Subject , tblSCTInputLetter.SenderOrgDesc COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderName,

  '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderRole, tblEmployee.FirstName + ' ' + tblEmployee.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverName, tblPosition.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverRole,

  tblSCTInputLetter.PVSentDate COLLATE SQL_Latin1_General_CP1_CI_AS AS SendDate, tblSCTInputLetter.Text COLLATE SQL_Latin1_General_CP1_CI_AS AS Comments, tblSCTLetterSendMethodL.Description COLLATE SQL_Latin1_General_CP1_CI_AS AS SendMethod,

  '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SecurityLevel, tblSCTInputLetterTypeL.Description COLLATE SQL_Latin1_General_CP1_CI_AS AS LetterType, 'نامه وارده' COLLATE SQL_Latin1_General_CP1_CI_AS AS Type,

  tblSCTInputLetter.AssignCode , '2' + cast(tblSCTInputLetter.ID AS nvarchar) COLLATE SQL_Latin1_General_CP1_CI_AS AS UniqueID

  ,tblSCTInputLetter.SenderCode COLLATE SQL_Latin1_General_CP1_CI_AS as SenderCode

  FROM tblSCTInputLetter INNER JOIN

  tblPosition ON tblSCTInputLetter.ReceiverPositionID = tblPosition.ID INNER JOIN

  tblEmployee ON tblSCTInputLetter.ReceiverEmployeeID = tblEmployee.ID INNER JOIN

  tblOrganizations ON tblSCTInputLetter.SenderOrgID = tblOrganizations.ID LEFT OUTER JOIN

  tblSCTLetterSendMethodL ON tblSCTInputLetter.ReceiveMethodID = tblSCTLetterSendMethodL.ID LEFT OUTER JOIN

  tblSCTInputLetterTypeL ON tblSCTInputLetter.InputLetterTypeID = tblSCTInputLetterTypeL.ID

  UNION ALL

  SELECT tblSCTOutputLetter.ID, tblOrganizations.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverOrg, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderOrg, tblSCTOutputLetter.Subject,

  tblEmployee.FirstName + ' ' + tblEmployee.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderName, tblPosition.Name COLLATE SQL_Latin1_General_CP1_CI_AS AS SenderRole,

  tblSCTOutputLetter.ReceiverOrgDesc COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverName, tblSCTOutputLetter.ReceiverPosDesc COLLATE SQL_Latin1_General_CP1_CI_AS AS ReceiverRole, tblSCTOutputLetter.PVSCDate COLLATE SQL_Latin1_General_CP1_CI_AS AS SendDate,

  tblSCTOutputLetter.Text COLLATE SQL_Latin1_General_CP1_CI_AS AS Comments, tblSCTLetterSendMethodL.Description COLLATE SQL_Latin1_General_CP1_CI_AS AS SendMethod, '' COLLATE SQL_Latin1_General_CP1_CI_AS AS SecurityLevel,

  '' COLLATE SQL_Latin1_General_CP1_CI_AS AS LetterType, 'نامه صادره' COLLATE SQL_Latin1_General_CP1_CI_AS AS Type, tblSCTOutputLetter.AssignCode , '3' + cast(tblSCTOutputLetter.ID AS nvarchar) COLLATE SQL_Latin1_General_CP1_CI_AS AS UniqueID

  ,'' COLLATE SQL_Latin1_General_CP1_CI_AS as SenderCode

  FROM tblSCTOutputLetter INNER JOIN

  tblPosition ON tblSCTOutputLetter.SenderPositionID = tblPosition.ID INNER JOIN

  tblEmployee ON tblSCTOutputLetter.SenderEmployeeID = tblEmployee.ID INNER JOIN

  tblOrganizations ON tblSCTOutputLetter.ReceiverOrgID = tblOrganizations.ID LEFT OUTER JOIN

  tblSCTLetterSendMethodL ON tblSCTOutputLetter.SendMethodID = tblSCTLetterSendMethodL.ID

  ) b

  GO

  because my server collation is SQL_Latin1_General_CP1_CI_AS .but still returns the same error.how do I solve this error?please guide me.

  thanks