Implicit conversion of varchar value to varchar cannot be performed
-
Hello Community,
When I execute the attached code I get the following error:
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 "Latin1_General_100_CI_AS_SC_UTF8" in add operator.
Can someone let me know the cause of the error, and any possible fix?
USE [DataverseEnriched] --Specify the name of the database in which GetOptionSetLabel function will be created
--=================================================================================================
-- Create function in datbase
--=================================================================================================
DECLARE
@OptionSetLabelFunctionDatabase sysname, --It will be automatically be set with the name of the database that has GetOptionSetLabel function
@OptionSetLabelFunctionDatabaseSchema sysname, --Specify the name of the database in which GetOptionSetLabel function will be created
@SynapseLinkDatabase sysname --Specify the name of the database corresponding to your Synapse Link for Dataverse
SET @OptionSetLabelFunctionDatabase = QUOTENAME(DB_NAME())
SET @OptionSetLabelFunctionDatabaseSchema = 'dbo'
SET @SynapseLinkDatabase = 'dataverse_xxxxx_xxxxx'
DECLARE @FunctionScript nvarchar(max) = 'IF object_id(N''dbo.GetOptionSetLabel'') is not null DROP FUNCTION [dbo].[GetOptionSetLabel]'
EXEC sp_executesql @FunctionScript
SET @FunctionScript = '
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
'
EXEC sp_executesql @FunctionScript
SET @FunctionScript = '
CREATE FUNCTION [dbo].[GetOptionSetLabel]
(
@EntityName nvarchar(max), @ColumnName nvarchar(max), @Value nvarchar(max), @LanguageCode int
)
RETURNS
nvarchar(max)
AS
BEGIN
declare @Values table (
value nvarchar(max)
);
insert into @Values
SELECT Value
FROM STRING_SPLIT(@Value, '';'')
declare @Labels table(
AttributeValue int,
LabelText nvarchar(max)
);
insert into @Labels
select distinct *
from (
select gosm.[Option], gosm.LocalizedLabel Value
from '+@SynapseLinkDatabase+'.'+@OptionSetLabelFunctionDatabaseSchema+'.GlobalOptionsetMetadata gosm
inner join @Values v on cast(v.[Value] as int) = gosm.[Option]
where gosm.OptionSetName = @ColumnName
and gosm.LocalizedLabelLanguageCode = @LanguageCode
and TRY_CAST(v.[Value] as int) is not null
union
select osm.[Option], osm.LocalizedLabel Value
from '+@SynapseLinkDatabase+'.'+@OptionSetLabelFunctionDatabaseSchema+'.OptionsetMetadata osm
inner join @Values v on cast(v.[Value] as int) = osm.[Option]
where osm.OptionSetName = @ColumnName
and osm.EntityName = @EntityName
and osm.LocalizedLabelLanguageCode = @LanguageCode
and TRY_CAST(v.[Value] as int) is not null
) t
order by [Option] asc
declare @optionsetLabel nvarchar(max)
set @optionsetLabel = isnull(
(
select string_agg(LabelText, '' '')
from @Labels
),
@Value)
RETURN @optionsetLabel
END'
PRINT 'Beginning function creation'
--PRINT @FunctionScript
EXEC sp_executesql @FunctionScript
PRINT 'Completed function creation'
USE [dataverse_xxxxx_xxxxx] --Specify the name of the database corresponding to your Synapse Link for Dataverse to be used from here onward
--=================================================================================================
--PROVIDE INPUT PARAMETERS:
--=================================================================================================
DECLARE
@EnrichedViewDatabase sysname, --Specify the name of the database in which views with enriched entities will be created
@EnrichedViewSchema sysname, --Specify the name of the database schema in which views with enriched entities will be created
@EnrichedColumnSuffix varchar(50), --Specify the suffix for columns enriched with human-readable descriptions. For example, the suffix of "label" will change a statecode column in the base table to a statelabel column in the enriched view.
@LanguageCode varchar(10), --Specify the language code for localized labels. For example, English - United States is 1033 (https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a)
@BaseTableSuffix varchar(50), --If applicable, specify the suffix in the names of the base tables or views (e.g., '_partitiond'). The default is an empty string.
@PreviewOnly bit --Indicate whether to preview the SQL Script (without creating the views) = 1 ; Create views = 0;
SET @EnrichedViewDatabase = 'MyEnrichedDatabase'
SET @EnrichedViewSchema = 'dbo'
SET @EnrichedColumnSuffix = 'label'
SET @LanguageCode = 1033
SET @BaseTableSuffix = ''
SET @PreviewOnly = 0
--=================================================================================================
-- Do not edit the script below this point
--=================================================================================================
--Get column metadata from the Lake Database managed by Synapse Link for Dataverse
--The column metadata will be stored as a JSON document in a scalar variable
--This is needed as a workaround for the limitation of not allowing system objects to be used in distributed queries
DECLARE @ColumnMetadata nvarchar(MAX), @ColumnMetadataSQL nvarchar(MAX)
--Define the SQL statement to retrieve column metadata from the Lake Database managed by Synapse Link for Dataverse
--Results will be stored as a JSON document in a variable
SET @ColumnMetadataSQL = 'SET @ColumnMetadataOUT = (
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo''
AND TABLE_NAME NOT IN (''OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'')
AND TABLE_NAME LIKE ''%' + @BaseTableSuffix + '''
FOR JSON AUTO)'
DECLARE @ParmDefinition NVARCHAR(MAX);
SET @ParmDefinition = N'@ColumnMetadataOUT NVARCHAR(MAX) OUTPUT';
EXECUTE sp_executesql @ColumnMetadataSQL, @ParmDefinition, @ColumnMetadataOUT=@ColumnMetadata OUTPUT;
--Declare a variable to store a SQL statement for creating enriched views
DECLARE @SQL nvarchar(MAX) = ''
; WITH CM AS (
--Parse column metadata variable and construct a table based on its content
SELECT JSON_VALUE(CM.value, '$.TABLE_SCHEMA') AS TableSchema,
JSON_VALUE(CM.value, '$.TABLE_NAME') AS TableName,
LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,
JSON_VALUE(CM.value, '$.COLUMN_NAME') AS ColumnName,
CAST(JSON_VALUE(CM.value, '$.ORDINAL_POSITION') AS INT) AS OrdinalPosition,
JSON_VALUE(CM.value, '$.DATA_TYPE') AS DataType
FROM OPENJSON (@ColumnMetadata) AS CM
)
, OSM AS (
--Get Option Set Metadata
SELECT DISTINCT
EntityName,
OptionSetName,
QUOTENAME(EntityName + '_' + OptionSetName) AS Alias
FROM dbo.[OptionsetMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)
, GOSM AS (
--Get Global Option Set Metadata
SELECT DISTINCT
OptionSetName,
QUOTENAME('Global_' + OptionSetName) AS Alias
FROM dbo.[GlobalOptionsetMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)
, GOSMM AS (
--Get Global Option Set Metadata
SELECT DISTINCT
OptionSetName,
QUOTENAME('Global_Multiselect_' + OptionSetName) AS Alias
FROM dbo.[GlobalOptionsetMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)
, StateM AS (
--Get State Metadata
SELECT DISTINCT
EntityName,
QUOTENAME(EntityName + '_State') AS Alias
FROM dbo.[StateMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)
, StatusM AS (
--Get Status Metadata
SELECT DISTINCT
EntityName,
QUOTENAME(EntityName + '_Status') AS Alias
FROM dbo.[StatusMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)
, SQLStatement AS (
--Enumerate all lines in the source table and replace codes with labels where applicable
SELECT CM.EntityName,
--Before the first column of each table, construct a CREATE OR ALTER VIEW statement
CASE WHEN CM.OrdinalPosition = 1
THEN 'CREATE OR ALTER VIEW ' + QUOTENAME(@EnrichedViewSchema) + '.' + CM.EntityName + '
AS
SELECT '
ELSE ','
END
--For each column, check if it needs to be replaced with a suitable localized label
+ CASE
WHEN OSM.OptionSetName IS NOT NULL THEN OSM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
WHEN GOSM.OptionSetName IS NOT NULL THEN GOSM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
WHEN GOSMM.OptionSetName IS NOT NULL THEN '[Base].' + QUOTENAME(CM.ColumnName) + ' as ' + CM.ColumnName + '_value, ' + (@OptionSetLabelFunctionDatabase + '.' + @OptionSetLabelFunctionDatabaseSchema + '.GetOptionSetLabel(''' + CM.TableName + ''', ''' + CM.ColumnName + ''', '+ '[Base].' + QUOTENAME(CM.ColumnName) + ', ' + @LanguageCode + ') as ' + CM.ColumnName)
WHEN StateM.EntityName IS NOT NULL THEN StateM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
WHEN StatusM.EntityName IS NOT NULL THEN StatusM.Alias + '.[LocalizedLabel] AS ' + REPLACE(QUOTENAME(CM.ColumnName), 'code]', @EnrichedColumnSuffix + ']')
ELSE '[Base].' + QUOTENAME(CM.ColumnName)
END AS [SQLLine],
CM.OrdinalPosition
FROM CM
LEFT JOIN OSM
ON CM.EntityName = OSM.EntityName
AND CM.ColumnName = OSM.OptionSetName
AND CM.DataType LIKE '%int' --Only include columns with integer data type
LEFT JOIN GOSM
ON CM.ColumnName = GOSM.OptionSetName
AND CM.DataType LIKE '%int' --Only include columns with integer data type
LEFT JOIN GOSMM
ON CM.ColumnName = GOSMM.OptionSetName
AND CM.DataType LIKE '%varchar%' --Only include columns with varchar data type that can potentially have multiselect values
LEFT JOIN StateM
ON CM.EntityName = StateM.EntityName
AND CM.ColumnName = 'statecode'
AND CM.DataType LIKE '%int' --Only include columns with integer data type
LEFT JOIN StatusM
ON CM.EntityName = StatusM.EntityName
AND CM.ColumnName = 'statuscode'
AND CM.DataType LIKE '%int' --Only include columns with integer data type
UNION ALL
--Construct the first line of the FROM clause, referencing external tables created by Synapse Link for Dataverse
SELECT DISTINCT
CM.EntityName,
'FROM ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(CM.TableSchema) + '.' + QUOTENAME(CM.TableName) + ' AS Base' AS SQLLine,
10000 AS OrdinalPosition
FROM CM
UNION ALL
--Construct LEFT JOIN statements for each relevant OptionSetMetadata field
SELECT DISTINCT OSM.EntityName AS EntityName,
'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[OptionSetMetadata] AS ' + OSM.Alias + '
ON ' + OSM.Alias + '.EntityName = ''' + OSM.EntityName + '''
AND ' + OSM.Alias + '.OptionSetName = ''' + OSM.OptionSetName + '''
AND [Base].' + QUOTENAME(OSM.OptionSetName) + ' = ' + OSM.Alias + '.[Option]
AND ' + OSM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
20000 AS OrdinalPosition
FROM OSM
JOIN CM
ON CM.EntityName = OSM.EntityName
AND CM.ColumnName = OSM.OptionSetName
WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types
UNION ALL
--Construct LEFT JOIN statements for each relevant GlobalOptionSetMetadata field
SELECT DISTINCT CM.EntityName AS EntityName,
'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[GlobalOptionSetMetadata] AS ' + Alias + '
ON ' + Alias + '.OptionSetName = ''' + OptionSetName + '''
AND [Base].' + QUOTENAME(OptionSetName) + ' = ' + Alias + '.[Option]
AND ' + Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
30000 AS OrdinalPosition
FROM GOSM
JOIN CM
ON CM.ColumnName = GOSM.OptionSetName
WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types
UNION ALL
--Construct LEFT JOIN statements for each relevant State Metadata field
SELECT DISTINCT CM.EntityName AS EntityName,
'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[StateMetadata] AS ' + StateM.Alias + '
ON ' + StateM.Alias + '.EntityName = ''' + StateM.EntityName + '''
AND [Base].statecode' + ' = ' + StateM.Alias + '.[State]
AND ' + StateM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
40000 AS OrdinalPosition
FROM StateM
JOIN CM
ON CM.EntityName = StateM.EntityName
AND CM.ColumnName = 'statecode'
WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types
UNION ALL
--Construct LEFT JOIN statements for each relevant Status Metadata field
SELECT DISTINCT CM.EntityName AS EntityName,
'LEFT JOIN ' + QUOTENAME(DB_NAME()) + '.[dbo].[StatusMetadata] AS ' + StatusM.Alias + '
ON ' + StatusM.Alias + '.EntityName = ''' + StatusM.EntityName + '''
AND [Base].statuscode' + ' = ' + StatusM.Alias + '.[Status]
AND ' + StatusM.Alias + '.LocalizedLabelLanguageCode = ' + @LanguageCode + '' AS SQLLine,
40000 AS OrdinalPosition
FROM StatusM
JOIN CM
ON CM.EntityName = StatusM.EntityName
AND CM.ColumnName = 'statuscode'
WHERE CM.DataType LIKE '%int' --Only capture columns with Integer Data Types
UNION ALL
--Add statement terminator
SELECT DISTINCT
EntityName,
'; ' + CHAR(10) AS SQLLine,
100000 AS OrdinalPosition
FROM CM
)
--Construct individual statements to create views (1 view per row)
--Since CREATE VIEW statement must be the first statement in a batch, assign each view definition to a variable
--and use the EXEC(@variable) command to create view as part of its own, separate batch.
, ViewDefinitions AS (
SELECT 'DECLARE @' + EntityName + ' NVARCHAR(MAX) = ''
' + REPLACE(STRING_AGG(CAST(SQLLine as varchar(MAX)), CHAR(10)) WITHIN GROUP (ORDER BY EntityName, OrdinalPosition, SQLLine), '''', '''''') + ''' ' + CHAR(10) + 'EXEC [' + @EnrichedViewDatabase + '].dbo.sp_executesql @' + EntityName + CHAR(10) AS ViewDefinition
FROM SQLStatement
GROUP BY EntityName
)
--Construct a comprehensive SQL statement to create all views
SELECT @SQL = STRING_AGG(ViewDefinition, ';' + CHAR(10) + CHAR(10))
FROM ViewDefinitions
--Return a preview of the SQL Script to be generated or go ahead and create the views.
IF @PreviewOnly = 1
BEGIN
--Return the final SQL statement
SELECT '--================================================================================================='+ CHAR(10) +' ' + CHAR(10) AS [--SQL Statement]
UNION ALL
SELECT '-- A preview of the script to generate enriched views is provided below.' AS [--SQL Statement]
UNION ALL
SELECT '-- No database objects have been created.' AS [--SQL Statement]
UNION ALL
SELECT '-- Re-run this script with the @PreviewOnly parameter set to 0 to actually create the views.' AS [--SQL Statement]
UNION ALL
SELECT '--================================================================================================='+ CHAR(10) +' ' + CHAR(10) AS [--SQL Statement]
UNION ALL
SELECT VALUE AS [--SQL Statement] FROM STRING_SPLIT(@SQL, CHAR(10))
END
ELSE
BEGIN
--Execute the SQL statement
PRINT 'Beginning views creation'
--PRINT @SQL
EXEC sp_executesql @SQL
PRINT 'Completed views creation'
ENDThanks
- This topic was modified 2 years, 3 months ago by carlton 84646. Reason: update question
-
I don't have synapse to test with.
Collation confict can be resolved by forcing a specfic collation in your query
It could be that the collation of tempdb ( as you use @tables) is different from the other databases
- carlton 84646 wrote:
Hello Community,
When I execute the attached code I get the following error:
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 "Latin1_General_100_CI_AS_SC_UTF8" in add operator.
Can someone let me know the cause of the error, and any possible fix?
Thanks
Out of that 349 lines of code, did the error happen to mention a line number?
If not, I see nothing in the code that specifically deals with collation of any kind. You need to check the tables involved for a different collation. It might even just be one column in one table.
One quick way to quickly isolate the problem might be found using the following code, which checks each column that can have a collation for the offending collation.
SELECT SchemaName = OBJECT_SCHEMA_NAME(object_id)
,ObjectName = OBJECT_SCHEMA_NAME(object_id)
,*
FROM sys.columns
WHERE collation_name = 'Latin1_General_100_CI_AS_SC_UTF8'
;Of course, then you'd have to compare the possible offenders produced above with the code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for reaching out.
Unfortunately, Synapse SQL Serverless Pool didn't give a line number of where the error occurs:
However, I think I'm getting the error here:
LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,
SELECT JSON_VALUE(CM.value, '$.TABLE_SCHEMA') AS TableSchema,
JSON_VALUE(CM.value, '$.TABLE_NAME') AS TableName,
LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,
JSON_VALUE(CM.value, '$.COLUMN_NAME') AS ColumnName,
CAST(JSON_VALUE(CM.value, '$.ORDINAL_POSITION') AS INT) AS OrdinalPosition,
JSON_VALUE(CM.value, '$.DATA_TYPE') AS DataType
FROM OPENJSON (@ColumnMetadata) AS CM
)
-
If that's true, then you might have to apply the COLLATE clause. See the following search for more information.
https://duckduckgo.com/?q=COLLATE+(Transact-SQL)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for getting in touch.
I will take a look at the link, however I think I'm going to struggle as to where to apply the COLLATE clause
- carlton 84646 wrote:
Hi Jeff,
Thanks for getting in touch.
I will take a look at the link, however I think I'm going to struggle as to where to apply the COLLATE clause
I can't help on the latter. I don't have your data, I can't see any meta data, etc, etc... but you can. I don't know for sure but I suspect the UTF8 collation needs to take precedence and your literals will like need to be modified with the COLLATE clause.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Check if tempdb collation is different to your database(s) the last time time I had this problem that was the cause and as already stated you have use COLLATE on (n)varchar columns
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
The person tha crafted the script checked out the tempdb, and it different to the database. However, when he changed COLLATE to match the database it still failed.
We are to resume troubleshooting today.
Thanks for getting in touch though
-
I tried this but I got the following err
FUNCTION 'OBJECT_SCHEMA_NAME' is not supported.
- carlton 84646 wrote:
I tried this but I got the following err
FUNCTION 'OBJECT_SCHEMA_NAME' is not supported.
You basically said that you were using "Synapse SQL Serverless Pool". There are a lot of things that work in SQL Server that won't work there. This might be one of them. I don't know what the workaround for that would be. Apparently, Synapse doesn't have those particular brain cells. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just figured out what line I was getting the error on:
Msg 468, Level 16, State 9, Line 244 Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
FROM CM LEFT JOIN OSM ON CM.EntityName = OSM.EntityName
Not sure if the above will help ....
-
Hi All,
I have also enlisted the help of the good people at Stack Overflow for help... I hope you're not affended.
- carlton 84646 wrote:
I just figured out what line I was getting the error on:
Msg 468, Level 16, State 9, Line 244 Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
FROM CM LEFT JOIN OSM ON CM.EntityName = OSM.EntityName
Not sure if the above will help ....
It doesn't because you've not identified which column uses which collation. But, you shouldn't need our help now. Add the COLLATE clause with the proper collation name to one side or the other. Then figure out the reasons why something that looks like it should match, doesn't. That's the problem with such collation mismatch "fixes".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply