|
|
Posted by MC on 05/08/07 06:00
I dont know where exactly do you get an error and what the error is but in
general you could issue count(*) from the table (same select as in a cursor,
only put count instead of id). Then you can set variable value with the
results of the count. After that, its just handling the package depending on
the var value....
MC
"Connie" <csawyer@rwbaird.com> wrote in message
news:1178553740.827232.235550@u30g2000hsc.googlegroups.com...
>I have a query that takes blobs that have been created within the last
> day out of sql server and places them on a disk drive. The query is a
> stored proc which runs in a DTS job. The job shows failure when there
> are no pdf's created in the last day, how do I correct this??
> Here is my stored proc code:
> CREATE PROCEDURE [dbo].[sp_PDFExport] AS
> begin
> set quoted_identifier off
>
> declare @pk int
>
> declare @where_clause varchar(100)
>
> declare @file_name varchar (50)
>
> declare @debug varchar (50)
>
> Declare @cmd varchar (50)
>
> --debug
>
> /*if @Debug = 1
> print @cmd
> exec Master..xp_cmdShell @cmd */
>
>
> -- begin cursor
>
> DECLARE LOOKUP CURSOR FOR select pr.[id]
> from plan_report pr, plan_version pv
> where pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time
>>= pv.update_time and pr.create_time > (Getdate()-1)
>
> OPEN LOOKUP
>
> FETCH NEXT FROM LOOKUP INTO @pk
>
>
> -- Loop through the list
>
> WHILE @@FETCH_STATUS = 0
>
> BEGIN
>
>
> SET @where_clause = 'Where' + '[ID]' + '=' + cast(@pk as
> varchar(10))
>
>
> SET @file_name = 'F:\NPPDFs\'+cast(@pk as varchar(10))+'.pdf'
>
> exec sp_textcopy @srvname = 'MILNPPRODSQL',
>
> @login = 'sa',
>
> @password = '<sa password>',
>
> @dbname = '<db name>',
>
> @tbname = 'Plan_Report',
>
> @colname = 'document',
>
> @filename = @file_name,
>
> @whereclause = @where_clause,
>
> @direction = 'o' -- 'o' for output, 'i' for input
>
> -- loop cursor
>
> SET @pk = NULL
>
> SET @where_clause = NULL
>
> SET @file_name = NULL
>
> FETCH NEXT FROM LOOKUP INTO @pk
>
>
> END
>
>
> -- cleanup
>
> CLOSE LOOKUP
>
> DEALLOCATE LOOKUP
>
> end
> GO
>
Navigation:
[Reply to this message]
|