Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need an urgent help to fix some data conversion issue in SQL server 2008. I got some requirement change on storing the data. Without much care I converted existing varbinary data to varchar in SQL server 2008 using "CONVERT(NVARCHAR(max), @bytearraydata, 1)".

The same conversion in C# is done using Encoding.Default.GetString and Encoding.Default.GetBytes api's. Encoding.Default.GetBytes(string) gets back the bytearray as it was earlier. But when I try to get back the byte array of string which I converted using "CONVERT() method" of SQL server 2008 gives me a wrong result.

My work is to fetch byte array stored as string (in db) and convert it to byte array and finally render the content as PDF. Data going through encoding mechanism (while saving and while fetching) works fine for me. But when I try to fetch the data which was converted using CONVERT of sql is failing to generate PDF. Its very urgent, as we have to push production release in a near time. Please help me. Thanks in advance.

Summary:

Byte array column has been changed to string.

Existing data conversion done using below sql script

Convert(NVARCHAR(MAX), @bytearraydata, 1)

In application level byte array conversion is done using below code Encoding.Default.GetString(bytearraydata)

Is Encoding and CONVERT are not compatible?

share|improve this question
    
Have you got a backup of the data prior the change? –  vonPryz Jan 7 at 13:01
2  
Can you show us an example of the binary data and the expected string and show where the string you got deviates from the expectation? –  Sebastian Meine Jan 7 at 13:03
    
I'm unclear. You're saying that you converted data from VARBINARY to VARCHAR, and when you try to convert it back to VARBINARY you're getting a different result? That should be a lossless process. –  Jon of All Trades Jan 7 at 15:47
    
Thanks for your responses. –  user3169103 Jan 8 at 10:44
    
Please check my question once again, I rephrased my question. Even I could able to see the data conversions happening fine with test data. I am using encoding mechanism for converting bytearray to string and vice versa in application level. For existing data I had written a sql script for converting bytearray to string. I could able to generate PDF content for the data which was converted through application level (Encoding mechanism), but it is failing for sql converted data. Varchar -> bytearray using Encoding.Default.GetBytes -> Generate pdf). Is encoding compatible with CONVERT? –  user3169103 Jan 8 at 11:06

2 Answers 2

up vote 1 down vote accepted

When you use Encoding.Default the result is dependent on local settings:

An encoding for the operating system's current ANSI code page.

The specification is pretty direct about the dangers of using the default encoding and very specifically calls out the recommendation not to use it:

Different computers can use different encodings as the default, and the default encoding can even change on a single computer. Therefore, data streamed from one computer to another or even retrieved at different times on the same computer might be translated incorrectly. In addition, the encoding returned by the Default property uses best-fit fallback to map unsupported characters to characters supported by the code page. For these two reasons, using the default encoding is generally not recommended

Now, for whatever reason, you expect the random current local encoding to match the server encoding. Even if the CONVERT function would do what you believe it does, the results would be random and unpredictable because:

  • the server local code page may differ from the client code page.
  • a code page change would render the persisted data unreadable because it was written with one encoding and then attempted to be read with another.

Furthermore, CONVERT does not do what you expect. CONVERT will cast a VARBINARY to an NVARCHAR using the UCS-2 encoding, since this is the encoding SQL Server uses for NVARCHAR data.

I suggest you approach your urgent problem by first reading this article The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!). Follow up with International Considerations for SQL Server.

share|improve this answer

I can't replicate that problem. Were there any extra steps involved? I can convert text to binary and back again, or vice-versa, without loss:

DECLARE @OrigText      VARCHAR  (100) = 'There once was a bear'
DECLARE @Binary        VARBINARY(100) = CONVERT(VARBINARY(100), @OrigText)
DECLARE @RoundTripText VARCHAR  (100) = CONVERT(VARCHAR  (100), @Binary)
DECLARE @RoundTripBin  VARBINARY(100) = CONVERT(VARBINARY(100), @RoundTripText)

SELECT @OrigText, @Binary, @RoundTripText, @RoundTripBin

Results:

  • There once was a bear
  • 0x5468657265206F6E63652077617320612062656172
  • There once was a bear
  • 0x5468657265206F6E63652077617320612062656172

This also works with NVARCHAR and using CAST rather than CONVERT. Note that I'm not specifying a style for CONVERT; if you specify one my understanding is that your text needs to be a hexadecimal string. Is that what you are storing, or is some more conventional text?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.