the collation of the value is unresolved due to a collation conflict

Hi all, 

this sql statement work great on fresh-installed test server 

DECLARE @strQry1 varchar(max)
SET @strQry1 =(select STUFF((SELECT TOP 100 PERCENT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value from '+s.name +'.'+ o.name as qry
FROM 
sys.all_columns c 
LEFT JOIN sys.all_objects o on c.object_id = o.object_id 
left join sys.schemas s on s.schema_id = o.schema_id 
where o.type='u' and s.name = 'dbo' --scheme name
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry) 
PRINT (@strQry1)
EXEC (@strQry1)

But it returns error on production server: 

Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xfallback_dbid' as column_name, Convert(varchar,xfallback_dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'version' as column_name, Convert(varchar,version) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_low' as column_name, Convert(varchar,xfallback_low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_drive' as column_name, Convert(varchar,xfallback_drive) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'low' as column_name, Convert(varchar,low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'high' as column_name, Convert(varchar,high) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'phyname' as column_name, Convert(varchar,phyname) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xfallback_vstart' as column_name, Convert(varchar,xfallback_vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'segmap' as column_name, Convert(varchar,segmap) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'lstart' as column_name, Convert(varchar,lstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'sizepg' as column_name, Convert(varchar,sizepg) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'vstart' as column_name, Convert(varchar,vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Id' as column_name, Convert(varchar,Id) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Name' as column_name, Convert(varchar,Name) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Status' as column_name, Convert(varchar,Status) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'GroupId' as column_name, Convert(varchar,GroupId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'OrderId' as column_name, Convert(varchar,OrderId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'CreatorId' as column_name, Convert(varchar,CreatorId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Created' as column_name, Convert(varchar,Created) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'ChangerId' as column_name, Convert(varchar,ChangerId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Changed' as column_name, Convert(varchar,Changed) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'b2_passporttypeid' as column_name, Convert(varchar,b2_passporttypeid) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'EVENT_ID' as column_name, Convert(varchar,EVENT_ID) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_NAME' as column_name, Convert(varchar,RULE_NAME) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_INFORMATION' as column_name, Convert(varchar,RULE_INFORMATION) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'lastrun' as column_name, Convert(varchar,lastrun) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'cpu_busy' as column_name, Convert(varchar,cpu_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'io_busy' as column_name, Convert(varchar,io_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'idle' as column_name, Convert(varchar,idle) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_received' as column_name, Convert(varchar,pack_received) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_sent' as column_name, Convert(varchar,pack_sent) as column_value from dbo.spt_monitor Union All Select 'dbo' a
Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Honestly, I googled it, but couldn't found advises for my case. 

How do I resolve it? 

February 6th, 2014 4:39am

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

Change over everything to explicit conversion and use the COLLATE clause if needed:

http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

First check in the database where is the collation difference.

Occasionally system views may have different collation which triggers this message.

To debug it, start with a small segment of the query, and increase it until you hit the collation message.

Related forum thread: http://stackoverflow.com/questions/12087721/implicit-conversion-of-varchar-value-to-varchar-collation-conflict

Free Windows Admin Tool Kit Click here and download it now
February 6th, 2014 4:48am

So what you are trying to achieve? Since the query produced is a single
line, it is not legible.

I tried it in two databases on my server, and it failed in both:

Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' as column_name, Convert(varchar,ny'.
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'trams'.
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mer'.
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

In tempdb the error was:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#A0F46E30'.

You got a third error. It seems that whatever you are doing it is brittle.

February 6th, 2014 5:00am

Erland Sommarskog


It returns: 

scheme_name, table_name, column_name, column_value. 

Try to perform it at fresh-installed server and you'll see. 

Free Windows Admin Tool Kit Click here and download it now
February 6th, 2014 5:04am

Well, I am at work, and don't really have the time to install new SQL Server instance only to assist someone who has a forum question.

So I iterate: what you are trying to achieve and why?

February 6th, 2014 6:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics