Displaying varbinary in SSRS?

I was recently writing a report in SSRS that required me to display a SID – a nasty little piece of data normally represented as a varbinary(85), and that looks like an enormous hex number.

I wrote my query in the usual way:

SELECT 'ServerRole' = spv.name,
       'MemberName' = lgn.name,
       'MemberSID' = lgn.sid
FROM   master.dbo.spt_values spv,
       master.dbo.sysxlogins lgn
WHERE  spv.low = 0
   AND spv.type = 'SRV'
   AND lgn.srvid IS NULL
   AND spv.number & lgn.xstatus = spv.number

(Those of you who are extremely geeky will notice that this is (basically) the contents of sp_helpsrvrolemember in SQL Server 2000…) The results:

I was somewhat perturbed when the draft report layout came back with #ERROR in the MemberSID field, as the results posted back by a query tool (above) look just fine.

I tried changing the SELECT statement to use CONVERT (or CAST), but to no avail – they made things look bad in SSMS’s query results window!

And it looks really bad if you switch to the Text view, with even the resultset font being messed around:

A little bit of googling pointed me in the direction of the undocumented function: fn_varbintohexstr. Put that into the SELECT statement in the appropriate place:

SELECT 'ServerRole' = spv.name,
       'MemberName' = lgn.name,
       'MemberSID' = master.dbo.fn_varbintohexstr(lgn.sid)
FROM   master.dbo.spt_values spv,
       master.dbo.sysxlogins lgn
WHERE  spv.low = 0
   AND spv.type = 'SRV'
   AND lgn.srvid IS NULL
   AND spv.number & lgn.xstatus = spv.number

And, at last, SSRS shows what I need it to show:

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , . Bookmark the permalink.

One Response to Displaying varbinary in SSRS?

  1. Kevin says:

    This was very helpful!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s