The easiest way to change the collation of a database including all fields goes over merge replication:
- Take a server with the target collation (server collation prop)
- Create a merge publication on the old server
- Add all products of the source database to the publication
- Run the snapsoht agent and wait it is completed
- Add a push subscription to your publication targeting the server with the nice collation
- Initialize Subscription
- Check in replication monitor and wait until agent ready
- Delete Subsciption
- Delete Publication
The following sql scipt creates a merge publication for your user tables
The other steps I do in Managemet Studio, as also I create the script objects like stored procedures, views etc in a subsequents step with a separate scirpt.
ALTER PROCEDURE [dbo].[CreateMergePublication]
@PublicationName nvarchar(max) = N'Pubi'
AS BEGIN
SET NOCOUNT ON
BEGIN TRY
-- *** BEGIN BLL ***
declare @DBName nvarchar(max)
select top 1 @DBName = TABLE_CATALOG from INFORMATION_SCHEMA.TABLES
exec sp_replicationdboption @dbname = @DBName, @optname = N'merge publish', @value = N'true'
-- Mergeveröffentlichung wird hinzugefügt
declare @desc nvarchar(max) = N'Mergeveröffentlichung der ' + @dbname + '-Datenbank von Verleger ' + @@SERVERNAME
exec sp_addmergepublication
@publication = @PublicationName,
@description = @desc ,
@sync_mode = N'native',
@retention = 14,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_subdirectory = N'ftp',
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@dynamic_filters = N'false',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_synctoalternate = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@use_partition_groups = null,
@publication_compatibility_level = N'100RTM',
@replicate_ddl = 1,
@allow_subscriber_initiated_snapshot = N'false',
@allow_web_synchronization = N'false',
@allow_partition_realignment = N'true',
@retention_period_unit = N'days',
@conflict_logging = N'both',
@automatic_reinitialization_policy = 0
exec sp_addpublication_snapshot
@publication = @PublicationName,
@frequency_type = 4,
@frequency_interval = 14,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 1,
@frequency_subday_interval = 5,
@active_start_time_of_day = 500,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1
declare @schema nvarchar(max), @table nvarchar(max), @uniquename nvarchar(max)
declare cr cursor for
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'sys%' and TABLE_NAME not like 'ms%' and TABLE_NAME not like 'dtprop%'
order by TABLE_NAME
open cr
WHILE 1=1 BEGIN
FETCH cr INTO @schema, @table
IF @@FETCH_STATUS <> 0 BREAK
set @uniquename = @schema + @table
print @schema + '.' + @table + ' (' + @uniquename + ')'
exec sp_addmergearticle
@publication = @PublicationName,
@article = @uniquename,
@source_owner = @schema,
@source_object = @table,
@type = N'table',
@description = N'',
@creation_script = null,
@pre_creation_cmd = N'none',
@schema_option = 0x000000010C034FD1,
@identityrangemanagementoption = N'manual',
@destination_owner = @schema,
@force_reinit_subscription = 1,
@column_tracking = N'false',
@subset_filterclause = N'',
@vertical_partition = N'false',
@verify_resolver_signature = 1,
@allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true',
@check_permissions = 0,
@subscriber_upload_options = 0,
@delete_tracking = N'true',
@compensate_for_errors = N'false',
@stream_blob_columns = N'false',
@partition_options = 0
END
close cr
deallocate cr
-- *** END BLL ***
END TRY
BEGIN CATCH
IF CURSOR_STATUS('global','cr') >= 0
BEGIN
close cr
deallocate cr
END
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT, @ErrorState INT;
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()
RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)
END CATCH;
END
