How to make uniform all the collations of table columns for all databases

Clearly, it is of paramount importance to standardise the collations for all databases in a SQL Server instance in order to avoid dealing with unforeseen conflicts of page code compatibility. We may find ourselves in complicated situations because databases with different collations were migrated from other environments and the new consolidated environment was not prepared to host those new databases, but in one way or another we may need to consolidate them in only one server. It may be compounded by the fact that there may be many character columns of a database using different collations and another collation at database level. What’s more, the tempdb database may be using another different collation.  So, it may turn out to be not only a complex issue but also time-consuming.
To give you just an example, we can start finding out what character columns are using different collations from SQL_Latin1_General_CP1_CI_AS that we need to change in order to make everything uniform. I am going to display a useful script to do it. In this example I am assuming that we want to use SQL_Latin1_General_CP1_CI_AS for all objects in the database server.

EXEC sp_MSforeachdb '
USE [?]
select db_name(),c.name
from sys.columns c
inner join sys.types t on t.user_type_id= c.user_type_id
inner join sys.tables tb on  c.object_id=tb.object_id
where c.collation_name is not null 
and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams''
and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS''
order by tb.name, c.column_id'

After that,we can make the decision of changing all character columns for all databases and use SQL_Latin1_General_CP1_CI_AS, and then make that change at database level. Be cautious and make sure as well you have tempdb’s collation set SQL_Latin1_General_CP1_CI_AS.

EXEC sp_MSforeachdb '
USE [?]
if db_name() not in (''master'',''tempdb'',''msdb'',''model'')
begin
    select replace( REPLACE( ''ALTER TABLE '' + QUOTENAME(SCHEMA_NAME(tb.schema_id)) + ''.'' 
    + QUOTENAME(tb.name) + '' ALTER COLUMN '' + QUOTENAME(c.name) +  '' '' 
    + QUOTENAME(t.name) + ''('' + CAST( case when T.NAME=''NVARCHAR'' THEN  c.max_length/2 
      WHEN  T.NAME=''NCHAR'' THEN  c.max_length/2 ELSE c.max_length  END  AS VARCHAR(10)) +'')''  
    + '' COLLATE SQL_Latin1_General_CP1_CI_AS'' + CASE WHEN c.is_nullable =1 THEN '' NULL '' 
     else '' NOT NULL ;'' END, ''-1'', ''MAX'' ), ''[text](16)'', ''[varchar](max)'') as cmd
    INTO #TblTMP
    from sys.columns c
    inner join sys.types t on t.user_type_id= c.user_type_id
    inner join sys.tables tb on  c.object_id=tb.object_id
    where c.collation_name is not null 
    and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>''sysdiagrams''
    and c.collation_name<>''SQL_Latin1_General_CP1_CI_AS''
    order by tb.name, c.column_id
    
  declare @cmd varchar(max)
  declare c_cmd cursor for 
     select cmd from  #TblTMP
  open c_cmd
  fetch next from c_cmd into @cmd
  while (@@fetch_status=0)
  begin    
    exec( @cmd)
    fetch next from c_cmd into @cmd
  end
  close c_cmd
  deallocate c_cmd
  drop table #TblTMP
end'

It is worth noting that while running the script above some errors may arise because of some indexes might be using one of the columns we are trying to alter. So, in this likely event, it is recommendable to drop those indexes and then run the script again. That is all for the time being. Let me know any remarks you may have.

MSSQL_ENG003165: An error was encountered while replication was being restored/removed. The database has been left offline

While restoring a replicated database without KEEP_REPLICATION option, SQL Server will remove replication settings by executing sp_restoredbreplication at the end of the process. The 'sp_restoredbreplication' system stored procedure will delete all replication metadata, that is, deletion of 'tr_MStran_alterschemaonly', 'tr_MStran_altertable', 'tr_MStran_altertrigger' and 'tr_MStran_alterview' tiggers (which were created to validate alterations on the replication of tables, triggers, views), disable user tables for replication, and deletion of subscription/publications/articles. Nevertheless, there might be some cases where 'sp_restoredbreplication' cannot be executed successfully and ends up leaving the database OFFLINE. I personally experienced that case and the error was something like this:

Msg 3165, Level 16, State 1, Line 1
Database ‘MyDB’ was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database ‘MyDB’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Looking into this case, I could see that the cause was a DDL database trigger which existed inside the database. Let me expand on what I am saying. The database had that trigger to audit some schema changes which were supposed to save into an auditing table. Unfortunately, that auditing table did not exist in the server where the database was being restored, and the deletion of objects of replication settings were not completed, which means that 'sp_restoredbreplication' was not executed correctly. Consequently, the restoration was stopped and SQL Server decided to leave the database OFFLINE.

In order to restore a copy of this database, we need to disable all DDL database triggers before taking its backup. Only then will the database be restored successfully. The other method to deal with this issue is to change the status to ONLINE manually after the restoration finishes unsuccessfully and also execute 'sp_restoredbreplication'.

To sum up, we need to proceed with more cautiousness while working with databases linked to replication. That is all for now. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Table-valued user-defined functions and the database collation

When it comes to altering database collations we may face some problems that tend to slow us down at the beginning. Nevertheless, digging into the message errors we might not spot the causes easily. For instance, while executing the following script to change the collation at database level an error may arise informing that some objects depends on it and it is not possible to make that change. Here is the script.

ALTER DATABASE MyDBUser SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE MyDBUser COLLATE SQL_Latin1_General_CP1_CI_AS;  
ALTER DATABASE MyDBUser SET MULTI_USER WITH ROLLBACK IMMEDIATE;

As far as we know collations are heavily linked to character columns, and it includes columns of table-valued user-defined functions as they may have character columns on its definition. When theses functions are created they inherit the database collation by design for their columns. Here the error message:

Msg 5075, Level 16, State 1, Line 1
The object 'TVFUserTable' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'MyDBUser' cannot be set to SQL_Latin1_General_CP1_CI_AS.

What we just need to do to be able to change the collation at database level is firstly drop every schema-bound objects, then make the change and finally create the objects again. That is all for now. Let me know any remarks you may have.