How to script tables in SSMS that contain non-unicode text

Question

I'm working with some tables in SQL Server that store text using 8-bit characters rather than unicode -- varchar rather than nvarchar. A certain amount of the text contains characters with values outside the ASCII range, for example curly quotes, em-dashes, and international characters such as ñ. Presumably this works because all our PCs and servers use the same code page.

However, when I use the Task > Generate Scripts scripting tool in SSMS to script such a table, the resulting script translates the special characters in such a way that if I use the script to reconstruct the table, the special characters are corrupted. For example "cañon" becomes "ca±on." I can see this in the INSERT statements that the script contains, where "cañon" from the database appears as "ca±on" in the INSERT statement.

In SSMS, how do I script a table that contains varchar data outside the ASCII range so that round-tripping will work?

In the Script Wizard's Output Option, are you saving as Unicode text, or ANSI text? — John Dewey, Mar 06 '12 at 01:21
That's the answer. It defaults to Unicode, but I needed ANSI. — howard39, Mar 06 '12 at 18:10

score 0 · Answer 1 · answered Mar 06 '12 at 18:13

0

In the Script Wizard's Output Option, you need to save as ANSI text, if you want to script extended ASCII data in CHAR and VARCHAR fields correctly.

answered Mar 06 '12 at 18:13

howard39

25
5

How to script tables in SSMS that contain non-unicode text

1 Answers1