| gerben 2005-07-22, 7:23 am |
| Oke, I figured it out myself. this is the solution:
----copy textcopy.exe to de c:\
This file can be found on this location: C:\Program Files\Microsoft SQL
Server\MSSQL\Binn
Create PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'c:\textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "where id=' + @whereclause +
'" /F "c:' + @filename +
'" /' + @direction
EXEC master..xp_cmdshell @exec_str
Execute this stored procedue:
declare @pub_id int
declare @pr_info varchar(100)
DECLARE DOC_Cursor CURSOR FOR
SELECT [ID],[description] FROM northwind.dbo.myimages
OPEN DOC_Cursor
FETCH NEXT FROM DOC_Cursor into @pub_id, @pr_info
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_textcopy1 @srvname = '<servername>,
@login = '<userid>',
@password = '<password>',
@dbname = 'Northwind',
@tbname = 'MyImages',
@colname = 'imgfield',
@filename = @pr_info,
@whereclause = @pub_id,
@direction = 'O'
fetch next from DOC_Cursor into @pub_id, @pr_info
end
CLOSE DOC_Cursor
DEALLOCATE DOC_Cursor
"gerben" wrote:
> Hello,
>
> i have a large number of zip files in my SQL Server database. New files are
> added on a daily bases by customers. Now i want to save those files
> automaticly to my local disk so i can unzip these files and use this data to
> load in a seperate application.
>
> Does anyone have an example how to do this?
>
> Thanks
>
> Gerben
|