SQL Server – Transact SQL – Error – Cannot resolve collation conflict for column

Background

On our development database received a warning that shows that we are not performing transaction backup, even though they have their recovery setting is set to FULL.

The specific message is “Full Recovery Mode w/o Log Backups“.

 

Code

Identify databases


select 
	  [sql] = 'alter database '
		   + quotename(name)
		   + ' set recovery simple'
 
from   sys.databases tblSD

where  tblSD.database_id >= 5

and    tblSD.recovery_model_desc != N'SIMPLE'			


Output:

We received a nice tally

Identify Databases, indicate current status

 


select 
	  [sql] = 'alter database '
		   + quotename(name)
		   + ' set recovery simple'
                   + ' -- current collation is ' 
                   + cast(recovery_model_desc as nvarchar(30))
 
from   sys.databases tblSD

where  tblSD.database_id >= 5

and    tblSD.recovery_model_desc != N'SIMPLE'			


Error Message:
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.
Error Image:
Query1


 

Identify Databases, indicate current status convert to non-unicode

Don’t need unicode anyway, let us change to non-unicode


select 
		  [sql] = 'alter database '
					 + cast(quotename(name) as varchar(80))
					 + ' set recovery simple'
					 + ' -- current collation is ' 
					 + cast(recovery_model_desc  as varchar(30))

from   sys.databases tblSD

where  tblSD.database_id >= 5

and    tblSD.recovery_model_desc != N'SIMPLE'
			

Error Message:

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.

Error Image:
ImplicitConversionOfVarCharValueToValueDueToACollationConflict



Identify Databases, convert to unicode

 


select 
	  [sql] = N'alter database '
		   + cast(quotename(name) as nvarchar(80))
		   + N' set recovery simple'
		   + N' -- current collation is ' 
		   + cast(recovery_model_desc  as nvarchar(30))

from   sys.databases tblSD

where  tblSD.database_id >= 5

and    tblSD.recovery_model_desc != N'SIMPLE'
			

 

Output:


Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.

CannotResolveCollationConflictForColumnN_1 (nvarchar)

 

 

Identify Databases, using “collate database_default”


select 
	  [sql] = N'alter database '
		   + cast(quotename(name) as nvarchar(80))
		   + N' set recovery simple'
		   + N' -- current collation is ' 
		   + cast(recovery_model_desc  as nvarchar(30))
			COLLATE DATABASE_DEFAULT

from   sys.databases tblSD

where  tblSD.database_id >= 5

and    tblSD.recovery_model_desc != N'SIMPLE'
			

 

We are good!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s