Collation Issue in MSDB.

• adilahmed1989

  Right there with Babe

  Points: 739

  More actions

  September 29, 2017 at 12:13 am

  #325565

  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

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  September 29, 2017 at 1:04 am

  #1961529

  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