Converting characters in data script

JDS

In text and varchar columns there is ASCII 160 characters in the data. Using the packager this is getting prefixed with a value of ACSII 194. This occurs when saving as unicode 8 if the data file is saved as ASCII the value is replaced with a "?" in the file. I was having this problem in version 1.1 of the packager so downloaded the 5.2 version and am seeing the same results.

Brian Donahue

Hello Doug,

I have a suspicion that that has to do with the collation order of this particular text/varchar column. Even if the character's ASCII value doesn't exceed 255, I believe that collation begins to have an effect on values higher than 64 (Latin1 alphanumeric characters).

Can you tell me what datatype the column is in db1, what datatype is in db2, and what the collation order of each is?

Thanks!

JDS

Actually I am looking in the file that is generated by the packager, not even imported into the database yet. That is where I am seeing the differences.

If the script is saved in unicode the saved file matches the source database. Which I am invesitgating using but it doubles the files size.

an example (don't know if these will render correctly for you or not)

In database-"Caolán McNamara"
In script file-"Caolán McNamara"

Brian Donahue

Hello,

What program are you using to view these saved scripts?

JDS

Query Analyzer.

Brian Donahue

Do you still get this result when you save the script in Unicode format?

JDS

No the characters seem to come over fine in the unicode version. Actually what I am trying now is saving it as unicode from the packager then opening it in Query Analyzer and saving it as ANSI. There is no unicode in the database at all and this keeps the file from doubling in size.

Brian Donahue

I don't think that there is an answer to this as Query Analyzer needs to open the file with a proper unicode format in order to properly display the character. If you do not use Unicode, then the file is smaller, but is full of invalid characters.

JDS

Well my question is then if there is no unicode items in the file, by virtue of there not being any unicode fields in the database, how come the packager is changing them?

Brian Donahue

I think there is a difference between the data in the database and the script encoding that you can save to a file. In addition to being able to specify Unicode in SQL Server (ntext, nvarchar, nchar) you can control the way that SQL Server presents non-unicode data (case-sensitive, accent-sensitive, language support). You have got some accented characters in this column, but the only way to get Query Analyzer to display them properly is to tell Query Analyzer to expect accented characters. And the way to do this is to save the script file with a Unicode or UTF-8 (with preamble) header on the file.