Skip navigation

How do I store/retrieve text and image data in SQL Server?

A. To store/retrieve this sort of data within TSQL scripts you have to use the WRITETEXT and READTEXT commands rather than standard INSERT/SELECT statements. These are documented, with examples, in the books-online but are basically a real pain to use. There are more manageable commands available from within the relevant programming languages - e.g. RDO and ADO from VB/C can use GetChunk and AppendChunk commands - but you still have to manage the image/text chunks/blocks of data at a time. About the only upside of storing this sort of data within SQL Server is that it can be kept transactionally consistent with the other data. For sample code see Q194975 - "Sample Functions Demonstrating GetChunk and AppendChunk".

For native ODBC access use the SQLPutData and SQLGetData commands.

If you just want to insert/retrieve an entire image/text then look at the TEXTCOPY program (textcopy /? for parameters) in the <sql>\BINN directory. It is a command-line program along the lines of BCP.

If transactional consistency doesn't matter, or can be achieved programmatically, then it is easier to store the data outside the database as an ordinary file. Within the database just hold a UNC pointer to where the file is held. This usually makes it much easier to display/edit the data as the name can simply be passed to whatever tool is doing the manipulation.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.