Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
SELECT object_name(object_id) as TableName,
name as columnName, collation_name
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects where name in ('table1','table2','table3'))
and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.
ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
This time i found that issue was resolved and there was no error.