xp_cmdshell To Extract PDF's 1

[Skittle]

I have a stored procedure that calls xp_Cmdshell to extract a PDF file from a blob to disk.
In the stored procedure it does something like this.

...
...
...
DECLARE @bcpCommand varchar(1000)      -- Command
SET @bcpCommand = 'bcp "SELECT Invoice_PDF_Image FROM [MyDB].dbo.[Invoices] WHERE invoice =  ''I18450''    "  queryout "C:\Invoices\I18450.pdf" -T -n -S UKPC-02'
EXEC master..xp_cmdshell @bcpCommand;

...
...
..
[/code]

It works well but I'm a bit concerned about allowing xp_cmdshell.
Is there another way I could do this within an SQL Stored Procedure without using xp_cmdshell?



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[yelworcm]

You could isolate the permissions a little better by having the stored procedure push a message onto a service broker queue, then having a second (admin run) procedure read that queue, and extract the BLOBs.

 

[Skittle]

OK thanks, I'll look into it.

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.