|
|
Posted by Connie on 05/08/07 13:29
On May 8, 1:00 am, "MC" <marko.culoNOS...@gmail.com> wrote:
> 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" <csaw...@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- Hide quoted text -
>
> - Show quoted text -
Sorry here is more detail. Last week we had determined that on step 2
I was experiencing a failure due to loss of connection to our Pegasus
server. This time I got an error on step 1 which is the code above
and here is the error message:
Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\NaviplanOptical001\The filename, directory name, or
volume label syntax is incorrect.DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCreateProcessTask_1 DTSRun OnError:
DTSStep_DTSCreateProcessTask_1, Error = -2147220330 (80040496)
Error string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':
Process returned code 1, which does not match the specified
SuccessReturnCode of 0. Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 4900 Error Detail Records:
Error: -2147220330 (80040496); Provider Error: 0 (0) Error
string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process
returned code 1, which does not match the specified SuccessReturnCode
of 0. Erro... Process Exit Code 1. The step failed.
Basically the stored proc is runs
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)
and there are no blobs (PDF's) returned by the select above so the
procedure fails as there are no PDF's to move to (SET @file_name = 'F:
\NPPDFs\'+cast(@pk as varchar(10))+'.pdf') @file_name.
Does that help (or make better sense)
Thanks much :)
Navigation:
[Reply to this message]
|