Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-22 : 08:24:56
|
| Bob writes "We have this really cool app that stores very large image files and other files as BLOBs for security and other reasons. However, we don't seem to be able to get BLOB data to export. Are we missing something, or is SQL Server missing something.Thanks,Bob" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-22 : 08:34:19
|
| One of the shortcomings of SQL Server is it's BLOB support. You *can* store them, but they're a pain to get into and out of the database. That's one of the reasons why it's better to keep the BLOB data stored in a regular disk file and use SQL Server to store a link to that file.Are you actually using replication between servers, or are you trying to dump the contents into a file? DTS and bcp will export text/image columns, but they'll dump all of the data into one file. You'd have to use a cursor to iterate through each row and save the text column out as a separate file (yuuuuuuuuuuuuuuck!)Another method is to use ADO to open a recordset or Stream, and then stream the contents into a file. It's also a cursor method but there are a lot of code examples on ADO Streams:www.4guysfromrolla.comwww.asp101.comwww.aspalliance.comwww.15seconds.comAlso look at the ADO documentation, there are examples that use Streams. If you don't have it, go to http://www.microsoft.com/data/download.htm and look for the MDAC SDK package. Download and install it, and then look for the ADO260.CHM file (it should also create an ADO documentation shortcut when you install)Finally, there is an undocumented command-line utility called TextCopy.exe, you will find it in your SQL Server program folder (where bcp.exe and osql.exe are stored) You can run it as "Textcopy/?" and you'll get the available options and usage syntax displayed. You might be able to use this in a SQL procedure to export the BLOBs into individual files.If you want to go the latter route or use bcp, post some more detail on your table structures and what kind of data you're exporting and we'll take a crack at it. |
 |
|
|
|
|
|
|
|
Replicating BLOBs