Export Image datatype to File

[ejc00]

Hello,

Within a database we are saving files as the image data type. I would like to create a DTS package to export these files to a network directory. Does anyone know of any examples that show how to do this? Or does anyone have any basic code that does this?

Any help would be greatly appreciated.

Thanks,
ejc

 

[NoCoolHandle]

I really don't know about DTS's ability to do this directly, however there is a very usefull utiltiy that comes with SQLSErver that you could script (sql or os) to do what you want.

Do a search under yur sql install dir and look for "textcopy"

It will let you do what you want, just not the way you expected..

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the text or
image data. If 'database' is not specified, the default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from 'file'.
/O Copy text or image value out of SQL Server into 'file'.
/K chunksize Size of the data transfer buffer in bytes. Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

For example..

textcopy /O /Dtrent /Tfiles /S. /Cfdata /W"where fileid=58" /Fc:\test2.jpg

Opens a connection to the local server, querys a table called files and exports the image data in the column fdata where the fileid = 58 to a file in the root of c: called test2.jpg

The only issue i have found it that it wants to prompt you if you aren't using a sql login...

HTH


Rob

PS [blue][shameless pitch for work][/blue]I could write you a custom app if you need [blue][/shameless pitch for work][/blue]

 

[ejc00]

Rob,

Thanks for the advice. I just placed a request to have that stored procedure created on the server. Hopefully I will be able to create a DTS that loops through that stored procedure to extract all of the files I need to the appropriate locations.

Thanks,
ejc

 

[NoCoolHandle]

My gut feeling with DTS is that you will have probems as it requires a datasource for the output file... This might get tricky (read impossible) if the filenames are dynamic and locations change..

You might also need to run a filecopy action after it to makesure you can get the next image without overwriting the last one.

If you have any vb programmers they could proabably do it by saving the DTS package to a com object and then editing the results to take parametner on the output datasource..

Good luck


Rob

 

[ejc00]

I think that using the ActiveX task I may be able to achieve what I want. Currently, I have a VB exe that does this... however, I'd like to run it on the server side... thus the use of DTS.

thanks,
ejc