Undeserved error SQL46010 given by SSDT 2014 on CODEPAGE argument

2
votes

I have an SSDT 2014 SQL Server Database Project in Visual Studio 2013. It targets Sql Server 2008. There is a stored procedure which has a bulk insert statement in it. The statement is absolutely valid, but SSDT reports error SQL46010 - Incorrect syntax.

The statement is:

BULK INSERT #tmp
FROM 'C:\Imports\BulkyFile.txt'
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', KEEPNULLS, ROWTERMINATOR = '\n', CODEPAGE='850')

Apparently SSDT thinks '850' is not a valid. If I change the codepage to 'ACP' all is well:

BULK INSERT #tmp
FROM 'C:\Imports\BulkyFile.txt'
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', KEEPNULLS, ROWTERMINATOR = '\n', CODEPAGE='ACP')

Is there a setting or something for SSDT, so that it will except a number for the CODEPAGE? See also https://msdn.microsoft.com/en-us/library/ms188365.aspx.

1
sql-servervisual-studio-2013sql-server-data-tools
I can reproduce this - the statement works if I create a stored procedure using SSMS but fails during compilation in SSDT. It seems SSDT doesn't like any numeric codepage - Panagiotis Kanavos
I'd go with the documentation's recommendation though and use collations in a format file. - Panagiotis Kanavos
The store procedure does a lot of BULK INSERTs on many different tables. Format files would force us to create one for each separate BULK INSERT. Besides the point that this is high maintenance, I also wonder if it would suffice. To my understanding the format file would contain the target collation for each field. Whereas the CODEPAGE attribute informs about the content of the file being imported and hints SQL SERVER how to perform the conversion to its collation settings. - AroglDarthu
First, you can create the stored procedure, it's only SSDT that complains. Second, the problem is that 850 is kind-of-ambiguous, as were all DOS codepages. They aren't used anymore and your file is probably in Windows-1252 or ISO-8859-1. The real solution is to use UTF8 (even DB/2 on AS400 supports it) to avoid codepages altogether. You can also use RAW to read the text unconverted. This will work if your server's (actually, column's) collation is one of the Latin collations - Panagiotis Kanavos
Thank you for the suggested workarounds. Unfortunately I am not at liberty to change the codepage in the stored procedure. I know there is no problem with the stored procedure itself; it will work. The reason I want it in SSDT is so that I can compare the version in source control to the version on the database server. Now because SSDT doesn't like this valid statement, I cannot. It won't compile the stored procedure, causing the compare tool to think it is not in the current project. Looks like I should file a bug with the SSDT team :-( - AroglDarthu

1 Answers

0
votes

It seems there is no workaround for this issue. So I've issued a bug with the SQL Server team: https://connect.microsoft.com/SQLServer/feedback/details/1177490

Please upvote it ;-)