I have below SQL statement in one of my stored procedure to fetch data from source server through linked server. In SQL Server 2012 the below query is running fine.
(SELECT policy_ID collate database_default
FROM OPENQUERY(Source_Linkedserver, 'SELECT policy_id FROM policy'))
As part of server migration, we are planning to migrate to SQL Server 2016. The above piece of code is throwing below error in SQL 2016.
Expression type float is invalid for COLLATE clause. [SQLSTATE 42000] (Error 447)
We have verified collation at table column level ,data base level and server level between SQL 2012 and SQL 2016 both are matching exactly and there is no change in source code.
Can anyone please help me to understand why in SQL 2016 I am getting this error where as in SQL 2012 it is running fine?