Extract Image From Row And Identify If Successful In T-SQL

[Skittle]

I need to loop through a table and for each row extract a pdf of an invoice from each row.
I have found the command below which extracts an image.

declare @bcpCommand varchar(1000);
SET @bcpCommand = 'bcp "SELECT Invd_PDF_Image FROM Invoice_Details WHERE inv_ref = ''17992''   " queryout "C:\17992.pdf" -T -n';
SELECT @bcpCommand;
EXEC master..xp_cmdshell @bcpCommand;

I have put a loop round it using an SQL cursor for each table row.
However I don't know how to capture the 'output' results to check if the extract was a success for each row.
Ideally I want to log each extract to a table with datetime, invoice number and error details.


The output is displayed in the Sql Server Management Studio Query results window as :-

Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 31     Average : (32.26 rows per sec.)
NULL

When it is not successful is shows:-

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15    
NULL

Can somebody point me in the right direction?



Dazed and confused.

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

 

[gk53]

look on that link

http://ayesamson.com/2012/06/06/exporting-photos-with-bcp/

it is explain how to extract from SQL database images to file

 

[Skittle]

Hi,

thanks but that doesn't solve the problem. I'll try to explain better.
I want to know how to programmatically capture the 'output' data so I can deduce if the export was successful or not.
This is the '0 rows copied' or '1 rows copied' line of the 'output' table that is returned.
How can I incorporate something to catch this value within the following looped code:-

DECLARE @Invoice VarChar(15)
DECLARE @bcpCommand varchar(1000)
DECLARE @path varchar(100)
SELECT @path = 'C:\Test Invoice Output\'


DECLARE db_CURSOR CURSOR FOR 
SELECT InvoiceID 
FROM [Invoices].[MyInvoiceTable]

OPEN db_CURSOR
FETCH NEXT FROM DB_CURSOR INTO @Invoice
   
  
WHILE @@FETCH_STATUS = 0
BEGIN

    SET @bcpCommand = 'bcp "SELECT Invd_PDF_Image FROM MyInvoiceTable WHERE InvoiceID = ''' + @Invoice +       '''   " queryout "' + @path       + @invoice +      '.pdf" -T -n';
    
    SELECT @bcpCommand;
	BEGIN TRY
    EXEC master..xp_cmdshell @bcpCommand;
    END TRY
    FETCH NEXT FROM db_cursor INTO @Invoice  
    END

CLOSE db_Cursor
DEALLOCATE db_Cursor

Dazed and confused.

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

 

[fredericofonseca]

google is your friend... too bad many don't use it.

http://stackoverflow.com/questions/9501192/get-results-from-xp-cmdshell

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886