C # and SQL Server disagree on whether Base64 string is really correct?

Question

The SQL Server 2008 R2 (SP1) database has the following table:

-- irrelevant columns omitted
create table Person
           ( PersonID int,
             Portrait varchar(max) )

The column Person.Portraitcontains Base64 strings encoded from JPG images - this is populated by a third-party system in which we do not control. I need to convert this data back to raw bytes so that I can display them in a report:

select isnull(cast(N'' as xml).value('xs:base64Binary(sql:column("psn.Portrait"))', 'varbinary(max)'), 0xdeadbeef) as [Portrait]
  from Person psn with (nolock)
 where psn.PersonID = <n>

For some strings this returns valid varbinarydata, for others it returns OxDEADBEEF(in other words, the result of the XML expression returns null).

However, if I run the following C # code for the rows in the table Personreturning nullto SQL Server, I get the correct JPG output images:

var portraitBytes = Convert.FromBase64String(Sql.SelectSingleString(
@"select psn.Portrait
    from Person psn with (nolock)
   where psn.PersonID = <n>"));

using (var writer = new FileStream(@"C:\portrait.jpg", FileMode.CreateNew))
{
  writer.Write(portraitBytes, 0, portraitBytes.Length);
}

, , "" SQL Server, "", , "" Z. =, SQL Server .

# Base64, Z, char = , 1 , (-) .

, :

• Base64 Z?
• : #, Base64 SQL Server, ?
• Z =, SQL Server ? , , JPG?

Answer 1

• .
• , . .
• , . .

, psn.Portrait NUL (\0), select SQL Server Management Studio. , SSMS , trailing NUL , , . , , NUL MSSQL .

# , Sql.SelectSingleString , result.Trim('\0'). , , # NUL... , .