8

I have a DACPAC file that was built in Visual Studio 2013, for an SSDT project. This SSDT project defines a post-deploy script designed to merge some static data into the published tables, and one piece of data contains a copyright symbol.

Now, when I publish the database through Visual Studio, the copyright symbol is preserved, and merged correctly into the target table. When I publish the same database (with the same dacpac and publish profile) using MSDeploy, the copyright symbol is merged into the target database as a "?" symbol. Likewise, when I use Action:Script instead of Action:Publish, the generated SQL script contains a "?" rather than the copyright symbol.

It seems as if the script Visual Studio is generating is UTF8 encoded, but the script that gets baked into the dacpac loses the UTF8 encoding. Does anyone have any ideas of how to work around this issue?

Michael J. Heier
  • 251
  • 2
  • 8
  • 1
    Have you checked an encoding of the script file itself. If the SQL script file is in ANSII save it as UTF8. – Vadim Loboda Mar 15 '15 at 14:50
  • Yes, I checked. The Post-Deployment Script file in Visual Studio is indeed encoded in UTF-8. Likewise, the postdeploy.sql file that gets unpacked if I double-click the .dacpac file is also UTF-8 encoded. I also compared the publish script file that gets generated by Visual Studio and the one that got unpacked from the .dacpac file. The only differences (in the post-deploy section, worth mentioning) were the lost special characters. – Michael J. Heier Mar 17 '15 at 18:16
  • Did you ever find a solution for this? Our workaround will be to ignore the offending procedure as the workarounds below would go against continuous deployment. For us the ignoring is easy enough as we have a customizable deployment modifier that allows me to exclude any step targeting the object in question. – rshadman Apr 06 '18 at 21:39
  • Any update on this topic? I'm facing the same situation after some months of using the same pipeline. – Playing With BI Jul 23 '21 at 13:51
  • I solved my problem, in my case created a view and provided the name with copy and paste from excel, and that add another character for the "-" symbol. Wrong one `[Operations DB – Plant Master]` right one: `[Operations DB - Plant Master]` – Playing With BI Jul 23 '21 at 14:07

3 Answers3

3

I had the same issue. Open this file in notepad and 'save as' UTF-8 encoding in the same folder to replace the old one. Then publish again. It should work.

Notepad Screenshot

SufianRashid
  • 25
  • 8
Nan Zheng
  • 31
  • 2
0

Do you prefix the string literal with N to denote that it contains a Unicode string? Is your column defined as nchar or nvarchar? The process of creating the dacpac may perform a conversion based on the distinction that your data is declared as a non-unicode string. It is not surprising that the copyright character would not survive this conversion.

See https://msdn.microsoft.com/en-us/library/ms179899.aspx for details about unicode vs. character strings.

Michael Richardson
  • 4,213
  • 2
  • 31
  • 48
  • Yes, I've prefixed the string values with N'', and declared all relevant types as NVARCHAR(len) as opposed to VARCHAR(len). The odd thing is that Visual Studio seems to work just fine if I run a Publish operation through the context menu on the SSDT project. It's just when I deploy the DACPAC through SqlPackage or MSBuild that the character encoding comes out incorrect. – Michael J. Heier Aug 28 '15 at 16:48
0

Finally I got the answer. I need to put character N before the unicode string inside insert statement.

INSERT INTO [Library] ([DisplayNameCN])
    VALUES (N'鴨脷洲公共圖書館')
Mickey
  • 419
  • 7
  • 17