Search code examples
sqlsql-serverviewcollation

Change the collation of a sql view

I currently have a view I have created in SQL, It looks at another database, and the view is then used in another process. The problem is that the 2 different databases have a collation conflict, one is CI_AS and the other CP1_CI_AS.

I've looked over the web and seem to find solutions to change the collation on the joins when I link the view to another database, but I can't do that as its an internal process within the product, all it allows me to do is give it a view.

Is there a way I can change the views collation?

Solution

  • You must apply the COLLATE operator to each column in the SELECT clause.

    As an exemple...

    With the view :

    CREATE VIEW V_CUST
AS
SELECT CustID, CustName, CustCity, CustDate
FROM   Customer
GO

    You need to re-create the view as :

    CREATE VIEW V_CUST
AS
SELECT CustID, CustName COLLATE French_BIN, CustCity  COLLATE French_BIN, CustDate
FROM   Customer
GO