Read image field in Management Studio

_{Thursday, April 21, 2011 7:24 PM}

I need to view binary file contents stored in a SQL server DB as an image field (even though the contents of the binary file stored there are text). 

I tried Select Convert(varchar(max), Convert(varbinary, OISFile))

From OIS_Filename

Where ID = soandso

but I only got back the first row of data of the stored file.  I need to see the entire file's content.

Does anyone know a way to accomplish this in Management Studio?

Edward R. Joell MCSD MCDBA

_{Friday, April 22, 2011 2:35 AM}

Hello Edward,

SSMS don't interpret the data you are selecting, it shows only the values; means: SSMS don't guess your binary may could be a valid image or a PDF or a XLS, you get only the hex values.

To show the images you could use SSRS = Sql Server Reporting Service with a report and an image control.

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

_{Tuesday, April 26, 2011 12:15 PM}

No I can't use SSRS. The person that wants to examine the content of the fields containing files does not have
SSRS installed and can't install it.  The image is a text file.

Edward R. Joell MCSD MCDBA

_{Thursday, April 28, 2011 9:03 AM}

Hi joeller,

As Olaf said, we cannot directly view the binary data in SQL Server Management Studio. If they are all TXT files, I think we may use the BCP Utility to save the binary data to the disk.

For example,

CREATE TABLE Table1 (Col1 INT, Col2 IMAGE)

INSERT INTO Table1 SELECT 1, BulkColumn FROM OPENROWSET(BULK 'D:\txt1.txt', SINGLE_BLOB) a
SELECT Col2 FROM sdd.dbo.Table1 WHERE Col1 = 1

Then please type the following statement in Microsoft Windows command prompt:

BCP "SELECT Col2 FROM TestDB.dbo.Table1 WHERE Col1 = 1" queryout "D:\file.txt" -T

Reference: bcp Utility

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

_{Thursday, April 28, 2011 1:00 PM}

Two possible issues. 

One.  This would require writing to the server.  While the Service user could do that, the user who =desires to see the data would have no access to the file.

Two. Wouldn't the BCP have to be run at the server's Windows command prompt?  If so that is not an option because none of use had access to the server.

Edward R. Joell MCSD MCDBA

_{Thursday, April 28, 2011 1:04 PM}

So I think you have to to create a CLR stored procedure or function to display the binary data in SQL Server.

Please see:

Using CLR Integration in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345136(v=sql.90).aspx

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

_{Thursday, April 28, 2011 5:03 PM}

Sad very Sad. 

I was going to say what a pity that something that was so straight forward in MS Access is not possible in SQL Server, when it occurs to me why not view the table in MS Access?  Surely Access can still connect to SQL Server tables like it did many years ago.  And as I recall from Access 97, it could display pictures (which they called OLE items).  I will try that.

Edward R. Joell MCSD MCDBA