Knowledge Base

Search Knowledge Base

KB #240016: SQL Query Results Truncated or Missing When Selecting Encrypted Columns

Type: Information
The result set from a SELECT statement in Query Analyzer may appear to be truncated or missing (rows and/or columns), if the set includes encrypted columns.
Additional Information:
Encrypted columns contain pure binary data that Query Analyzer is attempting to display as text. It may contain certain print control codes that confuse Query Analyzer when results are displayed in text.The best way to display encrypted data is by converting it to Binary or Varbinary. E.g.,

-- HomePhone is encrypted column
select convert(varbinary(128),HomePhone) from Employees

Alternatively you can sometimes improve the display by making the encrypted column the last one in the Select statement if possible, e.g.,

-- HomePhone is encrypted column
select LastName, FirstName, ... ,  HomePhone from Employees