Error : Expression type float is invalid for COLLATE clause

Transact-SQL
1

Hi Team,

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?

2

Collate clause is applied to (n)char, (n)varchar... (string) data types.
Policy_id - seems to be a numeric data types

Check the DDL of your table.
and if Source_Linkedserver is pointing to the right server

3

I do agree that collate is applied for string data types.

The linked server is pointing to correct server.

I didn't understand why it is falling in Sql 2016 server only.

Do i need to verfiy collation any other leval in addition to column,data base and server level?