-
May 23rd, 2017, 05:56 PM
#1
Thread Starter
PowerPoster
[RESOLVED] SQL Error and not sure why?
I'm getting the following error message on Line 1 of this SQL script:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in add operator.
I don't understand why this is an issue. Both columns [SerialNO] and [StationName] are defined as Varchar fields in the Table.
Here is the script:
Code:
1. SELECT [StationID]
2. ,CONVERT(VARCHAR, [SerialNO]) + ' (' + CONVERT(VARCHAR, [StationName]) + ')' AS StationName
3. FROM [Lookup].[WorkStation]
4. WHERE [IsActive] <> 0
5. ORDER BY [StationID]
I've attached a screenshot...hopefully is readable.
Thanks,
-
May 23rd, 2017, 06:57 PM
#2
Re: SQL Error and not sure why?
Give a size for the varchar in your convert ( varchar (20), fieldname)
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
May 24th, 2017, 08:38 AM
#3
Thread Starter
PowerPoster
Re: SQL Error and not sure why?
Gary,
Unfortnately, specifying a size didn't help. It still produced the same error.
Code:
SELECT [StationID],
CONVERT(VARCHAR(50), [SerialNO]) + ' (' + CONVERT(VARCHAR(50), [StationName]) + ')' AS StationName
FROM [Lookup].[WorkStation]
WHERE [IsActive] <> 0
ORDER BY [StationID]
-
May 24th, 2017, 08:47 AM
#4
Re: SQL Error and not sure why?
It looks like the columns have different collation orders as well, one has CS (Case Sensitive) and the other has CI (Case Insensitive) in the collation name. Is this intentional?
You can use the CAST function to deal with this https://docs.microsoft.com/en-us/sql...tput-collation if you really need to have the columns with different collation orders.
-
May 24th, 2017, 09:31 AM
#5
Thread Starter
PowerPoster
Re: SQL Error and not sure why?
PlausiblyDamp,
I changed the collation on one of the columns to match the other and that fixed the issue.
Thanks for your help!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|