Collation Conflict Printing The Tax Detail Report

We upgraded a client from Microsoft Dynamics GP 9 to Microsoft Dynamics GP 2010 recently, and the first time they tried to run the VAT 100 report they received a number of errors:

Microsoft Dynamics GP – An error occurred executing SQL statements.

Once they clicked OK they received a second error message:

Microsoft Dynamics GP – GPS Error: 58

Clicking OK again presented a third error message:

Microsoft Dynamics GP – SQL Error: 468 [Microsoft][SQLServer Native Client 10.0][SQL Server]Cannot resolve the collation conflict between “Latin1_General_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal operation.

Clicking OK a fourth time produced another error message:

Microsoft Dynamics GP – ODBC Error: 37000

This is an error message that seriously worried me. Dynamcis GP is supported on only two collations (Latin1_General_BIN and SQL_Latin1_General_CP1_CI_AS) and they were both mentioned in the error message. This is a client that Perfect Image, my current employer, implemented a number of years ago, so I know they were installed with the SQL_Latin1_General_CP1_CI_AS collation. So seeing the Latin1_General_BIN mentioned is of great concern because it should not be in the database.

I did some exploring and could find the Latin1_General_BIN collation on only one table: EDCVAT40. As the upgrade had recently been performed the old SQL 2000 install of Dynamics GP 9 was still available so I used Enterprise Manager to check the table and found that the collation on the RXTAXDTLID column on EDCVAT401 was Latin1_General_BIN:

I opened SQL Server Management Studio on the new server to:

I selected the RCTAXDTLID field and clicked to edit the collation:

I checked the SQL Collation radio button and set the combo to SQL_Latin1_General_CP1_CI_AS and clicked OK:

This sets the column back to the database default collation:

When the Save button is clicked a prompt will be displayed warning that the table will be dropped and recreated:

Click Yes to continue and SQL Server will create a temp table, copy rows from EDCVAT40 into it, delete and recreate EDCVAT40 with the updated column definitions, transfer the data back and then delete the temporary table.

If you do need to do this, make sure you have set the option to allow tables to be dropped and recreated in this way.