Lesson Learned #358:Error SQL46010-contains a statement that is not supported on Azure SQL Database

Today, we got a service request that our customer faced the following error message: Error SQL46010: Procedure: [dbo].[XYZ] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near '1252'. I would like to share with you the lessons learned.

The first thing that we need to pay attention is trying to find the text of this incorrect syntax error: The specific error is: Incorrect syntax near '1252'. The error message indicates that the statement in the stored procedure [dbo].[XYZ] contains a syntax error that is not supported on Microsoft Azure SQL Database v12. The error message states that there is an incorrect syntax near '1252'.

Based on the following code, it appears that the error is coming from the BULK INSERT statements. Specifically, the CODEPAGE option is causing the error.

BULK INSERT Table1
FROM 'XYZ.txt'
WITH (DATA_SOURCE = 'DataSource',
FIRSTROW = 1,
    FIELDTERMINATOR = ';',  --CSV field delimiter
    ROWTERMINATOR = '\\n',   --Use to shift the control to next row
FORMAT = 'CSV',
DATAFILETYPE='char',
CODEPAGE = '1252',
TABLOCK
)

As CodePage parameter is not supported in Microsoft Azure SQL Database, you need to comment or remove this option to be able to execute the query. 

Enjoy!