在SQL Server中設定排程自動執行結果的問題 |
|
尚未結案
|
|
Donna
一般會員   發表：3 回覆：0 積分：0 註冊：2003-05-27 發送簡訊給我 |
我在SQL Server執行這一支stored procedure後出現一些訊息，請大家幫忙分析一下
set nocount on
declare @sys_date char(8),@ten_date char(8)
set @sys_date=convert(char(8),getdate(),112)
set @ten_date=convert(char(8),getdate()-10,112)
create table ##pack_mail(form char(100)) --存放國內普限包裏暫存表(封發)
create table ##pack_express(form char(100)) --存放國內快捷暫存表(封發)
declare @officecode char(6),@date1 char(8),@date2 char(8),@upload_date char(14),@flag1 char(1),@flag2 char(1),@format char(100),@tendate char(7)
set @upload_date='00000000000000' --上傳日期時間設為0
set @flag1='0' --判斷是否有國內普限包裏資料輸出TXT
set @flag2='0' --判斷是否有國內快捷資料輸出TXT
select @officecode= OFFICE_CODE from BSC_SYSINF --作業局號 if substring(@ten_date,1,4) < '2011'
set @tendate='0'+cast(cast(@ten_date as numeric)-19110000 as char(6))
else
set @tendate=cast(cast(@ten_date as numeric)-19110000 as char(7)) declare @index_no numeric,@rcv_office char(6),@acc_date char(7),@mlst_no char(11),@mail_no varchar(20),@proc_date char(7),@proc_time char(6),@rec char(1),@mail_final char(20) select INDEX_D320,DMSD320.RCV_OFFICE_D310,DMSD320.ACC_DATE_D310,DMSD320.MAIL_NO,DMSD320.PROC_DATE,DMSD320.PROC_TIME,DMSD320.MLST_NO_D310 into #data
from DMSD310,DMSD320
where @tendate<=DMSD310.ACC_DATE_D310
and DMSD310.EMP_MAIL_TYPE_D310=DMSD320.EMP_MAIL_TYPE_D310
and DMSD310.RCV_OFFICE_D310=DMSD320.RCV_OFFICE_D310
and DMSD310.ACC_DATE_D310=DMSD320.ACC_DATE_D310
and DMSD310.MLST_NO_D310=DMSD320.MLST_NO_D310
and DMSD310.MLST_STUS='Y'
and DMSD320.SND_HOST_MARK='N' insert into ##pack_mail(form)
values('')
insert into ##pack_express(form)
values('') declare pack cursor local read_only for
select * from #data open pack
fetch pack into @index_no,@rcv_office,@acc_date,@mail_no,@proc_date,@proc_time,@mlst_no while @@fetch_status=0
begin
set @mail_no=rtrim(@mail_no)
exec sp_mail_class @mail_no,@rec output --input 郵件號碼 output: '0' , '1' , '2' , '3'
if @rec='1' --國內普限包裏
begin
set @flag1='1'
set @date1=cast(cast(@proc_date as numeric)+19110000 as char(8)) --處理日期時間 轉成西元格式YYYYMMDD
set @date2=cast(cast(@acc_date as numeric)+19110000 as char(8)) --帳務日期 轉成西元格式
set @mail_final=@mail_no
set @format='084'+'Z4'+@officecode+@mail_final+@date1+@proc_time+@upload_date+@date2+@rcv_office+@mlst_no
insert into ##pack_mail (form)
values (@format)
update DMSD320
set SND_HOST_MARK='Y'
where INDEX_D320=@index_no
end
if @rec='2' --國內快捷
begin
set @flag2='1'
set @date1=cast(cast(@proc_date as numeric)+19110000 as char(8)) --處理日期時間 轉成西元格式
set @date2=cast(cast(@acc_date as numeric)+19110000 as char(8)) --帳務日期 轉成西元格式
set @mail_final=@mail_no
set @format='084'+'Z4'+@officecode+@mail_final+@date1+@proc_time+@upload_date+@date2+@rcv_office+@mlst_no
insert into ##pack_express (form)
values (@format)
update DMSD320
set SND_HOST_MARK='Y'
where INDEX_D320=@index_no
end
fetch pack into @index_no,@rcv_office,@acc_date,@mail_no,@proc_date,@proc_time,@mlst_no
end
close pack
deallocate pack
--------------------------- OUTPUT TXT -----------------------------------------
create table ##pack_ini(form char(40))
declare @sysdate char(6),@temp_path char(40),@host_name varchar(20)
declare @filename varchar(80),@serial_no char(2)
set @sysdate=substring(convert(char(8),getdate(),112),3,6) --YYMMDD yy:西元年後2碼
exec master..xp_cmdshell 'bcp ##pack_ini in c:\dms\dms.ini -c -Usa -P'
select @temp_path=form from ##pack_ini
set @host_name=substring(@temp_path,20,20)
set @host_name=rtrim(ltrim(@host_name))
set @filename='net use s: \\'+@host_name+'\d$ post /user:workgroup\post'
--exec master..xp_cmdshell @filename if @flag1='1' --##pack_mail 有資料(國內普限包裏)
begin
if exists (select * from BSC_UPLOAD where UPLOAD_DATE=@sysdate --當日是否已產生記錄於上傳記錄檔
and TXT_TYPE='1'
and SUBSYSTEM_TYPE='1')
begin --已經產生記錄
select @serial_no=SERIAL_NO from BSC_UPLOAD ----- serial_no=nn
where UPLOAD_DATE=@sysdate ----- first n = 0~1
and TXT_TYPE='1' -----second n = 1~9
and SUBSYSTEM_TYPE='1' set @serial_no=case
when substring(@serial_no,2,1)='9' then '11'
else substring(@serial_no,1,1)+char(ascii(substring(@serial_no,2,1))+1)
end
update BSC_UPLOAD
set SERIAL_NO=@serial_no
where UPLOAD_DATE=@sysdate
and TXT_TYPE='1'
and SUBSYSTEM_TYPE='1'
set @filename='bcp ##pack_mail out c:\dms\dms.txt -w -Usa -P'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'c:\dms\filterf.exe' --濾掉第一筆空白記錄
set @filename='rename c:\dms\filterf.txt '+@sysdate+@serial_no+'.txt'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'del c:\dms\dms.txt'
-- set @filename='move c:\dms\'+@sysdate+@serial_no+'.txt s:\lmq\txt'
-- exec master..xp_cmdshell @filename
end
else
begin --尚未產生記錄
set @serial_no='01'
insert into BSC_UPLOAD(UPLOAD_DATE,TXT_TYPE,SUBSYSTEM_TYPE,SERIAL_NO)
values(@sysdate,'1','1',@serial_no)
set @filename='bcp ##pack_mail out c:\dms\dms.txt -w -Usa -P'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'c:\dms\filterf.exe'
set @filename='rename c:\dms\filterf.txt '+@sysdate+@serial_no+'.txt'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'del c:\dms\dms.txt'
-- set @filename='move c:\dms\'+@sysdate+@serial_no+'.txt s:\lmq\txt'
-- exec master..xp_cmdshell @filename
end
end
if @flag2='1' --##pack_express 有資料(國內快捷)
begin
if exists (select * from BSC_UPLOAD where UPLOAD_DATE=@sysdate --當日是否已產生記錄於上傳記錄檔
and TXT_TYPE='2'
and SUBSYSTEM_TYPE='1')
begin --已經產生記錄
select @serial_no=SERIAL_NO from BSC_UPLOAD ----- serial_no =nn
where UPLOAD_DATE=@sysdate ----- first n : A~B
and TXT_TYPE='2' ----- second n : 1~9,A~Z
and SUBSYSTEM_TYPE='1' set @serial_no=case
when substring(@serial_no,2,1)='9' then substring(@serial_no,1,1)+'A'
when substring(@serial_no,2,1)='Z' then 'B1'
else substring(@serial_no,1,1)+char(ascii(substring(@serial_no,2,1))+1)
end
update BSC_UPLOAD
set SERIAL_NO=@serial_no
where UPLOAD_DATE=@sysdate
and TXT_TYPE='2'
and SUBSYSTEM_TYPE='1'
set @filename='bcp ##pack_express out c:\dms\dms.txt -w -Usa -P'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'c:\dms\filterf.exe'
set @filename='rename c:\dms\filterf.txt '+@sysdate+@serial_no+'.txt'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'del c:\dms\dms.txt'
-- set @filename='move c:\dms\'+@sysdate+@serial_no+'.txt s:\lmq\txt'
-- exec master..xp_cmdshell @filename
end
else
begin --尚未產生記錄
set @serial_no='A1'
insert into BSC_UPLOAD(UPLOAD_DATE,TXT_TYPE,SUBSYSTEM_TYPE,SERIAL_NO)
values(@sysdate,'2','1',@serial_no)
set @filename='bcp ##pack_express out c:\dms\dms.txt -w -Usa -P'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'c:\dms\filterf.exe'
set @filename='rename c:\dms\filterf.txt '+@sysdate+@serial_no+'.txt'
exec master..xp_cmdshell @filename
exec master..xp_cmdshell 'del c:\dms\dms.txt'
-- set @filename='move c:\dms\'+@sysdate+@serial_no+'.txt s:\lmq\txt'
-- exec master..xp_cmdshell @filename
end
end
set @filename='move c:\dms\*.txt d:\lmq\txt'
exec master..xp_cmdshell @filename
-- exec master..xp_cmdshell 'net use s: /delete'
drop table ##pack_ini
drop table ##pack_mail
drop table ##pack_express
end 由job所產生的錯誤或訊息如下 ...tput file.output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Starting copy...
(null)
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 63 Avg 63 (15.87 rows per sec.)
(6 rows(s) affected)
output
---------------------------------------... The step succeeded.
|
發表時間：2003-06-03 08:58:20
IP:61.30.xxx.xxx 未訂閱
系統時間：2024-05-18 9:39:31
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |