September 29, 2017 at 12:13 am
Dear All,
I am trying to implement Log shipment monitoring mail with an automated process on Primary server with a stored procedure,
However I am facing a following error on it :
Msg 468, Level 16, State 9, Line 6
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
On Further Investigating, i found that collation of MSDB database on Secondary server is different from that of Primary server. The collation of MSDB database on Secondary Server is "Latin1_General_CI_AI"
The query is below :
SELECT p.primary_server,p.primary_database, s.secondary_server,
s.secondary_database, p.last_backup_file, p.backup_threshold,
p.last_backup_date, s.last_restored_file, s.restore_threshold, s.last_restored_date,
s.last_restored_latency
FROM [WIN-54LA5NP2I65\PRIMARYSQL].msdb.dbo.log_shipping_monitor_primary p INNER JOIN [WIN02\SECONDARYSQL].msdb.dbo.log_shipping_monitor_secondary s
ON p.primary_server = s.primary_server and p.primary_database = s.primary_database
Note : WIN-54LA5NP2I65\PRIMARYSQL is Primary Server and WIN02\SECONDARYSQL is Secondary Server.
Any help on resolving this issue would be appreciated.
Regards,
Adil
September 29, 2017 at 1:04 am
Add the collate clause to the join to force a collation, something like
SELECT p.primary_server,p.primary_database, s.secondary_server,
s.secondary_database, p.last_backup_file, p.backup_threshold,
p.last_backup_date, s.last_restored_file, s.restore_threshold, s.last_restored_date,
s.last_restored_latency
FROM [WIN-54LA5NP2I65\PRIMARYSQL].msdb.dbo.log_shipping_monitor_primary p INNER JOIN [WIN02\SECONDARYSQL].msdb.dbo.log_shipping_monitor_secondary s
ON p.primary_server = s.primary_server COLLATE database_default and p.primary_database = s.primary_database COLLATE database_default
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply