SQL Server 2005 - How do I convert image data type to character format

Question

Background: I am a software tester working with a test case management database that stores data using the deprecated image data type. I am relatively inexperienced with SQL Server.

The problem: Character data with rich text formatting is stored as an image data type. Currently the only way to see this data in a human readable format is through the test case management tool itself which I am in the process of replacing. I know that there is no direct way to convert an image data type to character, but clearly there is some way this can be accomplished, given that the test case management software is performing that task. I have searched this site and have not found any hits. I have also not yet found any solutions by searching the net.

Objective: My goal is to export the data out of the SQL Server database into an Access database There are fewer than 10,000 rows in the database. At a later stage in the project, the Access database will be upsized to SQL Server.

Request: Can someone please give me a method for converting the image data type to a character format.

.

Answer 1

You presumably want to convert to byte data rather than character. This post at my blog Save and Restore Files/Images to SQL Server Database might be useful. It contains code for exporting to a byte array and to a file. The entire C# project is downloadable as a zip file.

Answer 2

One solution (for human readability) is to pull it out in chunks that you convert from binary to character data. If every byte is valid ASCII, there shouldn't be a problem (although legacy data is often not what you expect).

First, create a table like this:

create table Nums(
  n int primary key
);

and insert the integers from 0 up to at least (maximum image column length in bytes)/8000. Then the following query (untested, so think it through) should get your data out in a relatively useful form. Be sure whatever client you're pulling it to won't truncate strings at smaller than 8000 bytes. (You can do smaller chunks if you want to be opening the result in Notepad or something.)

SELECT
  yourTable.keycolumn,
  Nums.n as chunkPosition,
  CAST(SUBSTRING(imageCol,n*8000+1,8000) AS VARCHAR(8000)) as chunk
FROM yourTable
JOIN Nums
ON Nums.n <= (DATALENGTH(yourTable.imageCol)-1)/8000
ORDER BY yourTable.keycolumn, Nums.n