I have a table with an image column in SQL Server 2000. I need to save the image data to a file on the file system. In SQL Server 2005 I can use the ADODB.Stream object for file I/O but this doesn't seem to exist in SQL Server 2000. The closest thing I can find is Scripting.FileSystemObject but it only seems to have support for text files which won't work for me (I don't think).
A:
ADODB.Stream is not a SQL component, is an ADO component distributed with MDAC/WDAC. So simply install latest MDAC/WDAC on the SQL 2000 machine and you'll have ADODB.Stream at your disposal to use.
Remus Rusanu
2009-08-25 16:30:57
Oh I see, that is helpful. On SQL Server 2005 I need to run sp_configure 'Ole Automation Procedures', 1; to enable to the various OLE functions. This gives me an error on SQL Server 2000 saying it does not exist. Do I still need to do this? Also, SQL Server 2000 won't let me create a local variable of type IMAGE, should VARBINARY work for this?
Jason
2009-08-25 17:07:56
no, on SQL 2k the OLE automation is always on. Only in SQL 2k5 due to the security push idea of 'off by default', were features started to be deployed disabled and admins had to enable them.
Remus Rusanu
2009-08-25 17:47:34
VARBINARY in SQL2K is limited to 8000 bytes. You would have to read the IMAGE field in chunks of 8k using READTEXT and write each chunk out, appending to the file. Frankly, I found the idea of writing new code for doing this in 2009 a big waste of time, that 2k machine should be updated any day now...
Remus Rusanu
2009-08-25 17:51:08
I agree with you whole heartily. Unfortunately, I am at the mercy of a 3rd party vendor who refuses to update their software to support 2k5+. Could you give me an example of how to use the READTEXT function?
Jason
2009-08-25 18:16:18
See here: http://msdn.microsoft.com/en-us/library/aa172742(SQL.80).aspx
Remus Rusanu
2009-08-25 18:26:56
Thanks for the link, but I don't quite understand how to read my data in 8000 byte chunks. I am looping over my rows in a cursor, I assume I need to select TEXTPTR(mycol)? Then in my loop after I fetch the row I need to call READTEXT to read the data? I don't see how I am supposed to loop over the image data or how I even get the data out of READTEXT at all?
Jason
2009-08-25 19:15:49
You need to read from offest 0 a length of 8000 bytes, assign them to a @variable of type varbinary(8000), write them to the stream, then read from offset 8000 another length of 8000, asssign it to variable, then append to stream, then read from offset 16000 length 8000, then from offset 24000 length 8000 and so on untill you read the entire IMAGE field.
Remus Rusanu
2009-08-25 20:22:06
I apologize for being dense, but I don't quite see how to use READTEXT to read data into a variable. It appears as though the READTEXT function forces the data to be returned as if you were doing a standard query and it doesn't look like you can read into a variable (from my naive understanding, at least).
Jason
2009-08-25 21:07:36
Sry, that was me being obtuse actually. READTEXT is not the best choice, since it returns the result to the client. You should use `SELECT @chunk = SUBSTRING(..., 0, 8000)`, as it works in IMAGE fields too.
Remus Rusanu
2009-08-25 21:20:29
Ahh, that's exactly what I needed, thanks a lot for your help.
Jason
2009-08-25 22:10:01