Error cause job to quit running

I have a sp that calls another sp. The second sp will generate an error
which the parent sp expects and then calls the child sp again with a
different set of parameters until success is achieved. If I run this in
Query Analyser all is good. But when I put the parent sp in a job step
and run it as a job, it quits on the first generated error. I don't
understand what is going on here??
Barry
Hi Barry,
Could you help show me detailed sample stored procedures, with which I
could repo it on my machine and then make further research on it.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sure, here's the code I am running. It is just a db restore. You will need
a db backup file named the way that the maintenance plans name backup files.
create proc spSYSRestoreGDInventoryDB
as
begin
declare @.MinCntr int
declare @.MinStr varchar(8)
set @.MinCntr = 0
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'GDInventory')
DROP DATABASE [GDInventory]
CREATE DATABASE [GDInventory] ON (NAME = N'GDInventory_data', FILENAME =
N'c:\database\mssql\data\GDInventory_Data.MDF' ,
SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'GDInventory_log', FILENAME =
N'c:\database\mssql\data\GDInventory_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
-- Expect possible failure as we don't really know what the backup name is
exec spSYSRestoreGDInventory @.MinCntr
while @.@.Error > 0 and @.MinCntr <= 2459
begin
set @.MinCntr = @.MinCntr + 1
--print 'Trying ' + cast(@.MinCntr as varchar)
exec spSYSRestoreGDInventory @.MinCntr
end
--print 'GDInventory Done'
end
go
CREATE proc spSYSRestoreCPI @.MinCntr int
as
begin
declare @.FileName nvarchar(255)
declare @.BUDate datetime
declare @.BUDateStr varchar(30)
declare @.BUDateMM varchar(3)
declare @.BUDateDD varchar(3)
declare @.MinStr varchar(8)
set @.BUDate = getdate()
set @.BUDateMM = '0' + cast(DatePart(mm,@.BUDate) as varchar)
set @.BUDateDD = '0' + cast(DatePart(dd,@.BUDate) as varchar)
set @.BUDateStr = cast(DatePart(yyyy,@.BUDate) as varchar(4)) +
substring(@.BUDateMM,len(@.BUdateMM) -1,2) +
substring(@.BUDateDD,len(@.BUdateDD) -1,2)
set @.MinStr = '0000' + cast(@.MinCntr as varchar)
set @.MinStr = right(@.MinStr,4)
set @.FileName = N'\\Backup01\BackupHarold\GDInventory\GDInventory_ db_' +
@.BUDateStr + @.MinStr + '.BAK'
restore database GDInventory
FROM DISK = @.Filename
WITH MOVE 'GDInventory_data' TO
'c:\database\mssql\data\GDInventory_data.mdf',
MOVE 'GDInventory_log' TO
'c:\database\mssql\data\GDInventory_log.ldf',REPLA CE
end
go
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Could you help show me detailed sample stored procedures, with which I
> could repo it on my machine and then make further research on it.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Thanks for your prompt updates and detailed information!
However, I found you show me spSYSRestoreGDInventoryDB and spSYSRestoreCPI.
spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
putting spSYSRestoreCPI here? Or would you please so kind as to tell me
how to reproduce that error?
Moreover, it is highly appriciated if you could provide me error log in SQL
Server
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Sorry I got my files mixed up. Here are the correct sp's and the output I
get from running these in QA vs in a job. Basically the QA runs to
completiong while the job quits after 10 iterations??
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your prompt updates and detailed information!
> However, I found you show me spSYSRestoreGDInventoryDB and
> spSYSRestoreCPI.
> spSYSRestoreGDInventoryDB was calling spSYSRestoreGDInventory but
> spSYSRestoreCPI doesn't call any other stored procedures. Have you missed
> putting spSYSRestoreCPI here? Or would you please so kind as to tell me
> how to reproduce that error?
> Moreover, it is highly appriciated if you could provide me error log in
> SQL
> Server
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
begin 666 RestoreJobErrorProblem.zip
M4$L#!!0````(`(U%-#&V-(*^YP$``&H$```;````<W!365-297-T;W)E1T1)
M;G9E;G1O<GDN<W%LC9-=;YLP%(:OA\1_.'= 0U*2IM,Z*5*;DK911U.%KM,D
MI,I@.0U'!I+E'\_&P@.E)5+G"^3#.<_Q^_HC8@.0)`AM61, W_E]_3;@.H&+EU
MEW1+J)SNX-)+Z345#%(J= UQ7=.UD"0I51-=.SU1WV\-"%TR9D4.HL!HQR%$
MT5NYJ4K;AC/(4UH*PB$J2BH(D\50<@.)QP1H`XC0C%.4$*G0['CF.#<[%</Q]
M.'&<J0WA6ZQ2)Z>U'$RB#$D=ES<2?% @.W2(6O2)F3L[/K4[!_+>KO&/Y$6E.
M>AE?*MRC9TZ?]+R/=#_KNL>STGNW\0^KELV):!7-`!(BE"ZSGY6KSL!P#!A
MA+@.PU;]'Q(29YW938@.'B^P6L`UB*.@.YC_#7L5R=VR.WD.$::4\N"@.3RP9 @.`O
M0RY82A.S]6'+@.U41KB(+AF-[\@.7DNEW(=1NHD=ELK7(H1VNRO6Y]8RW!TN2U
MKI2Q/;7:36]OT0P>C""85Y?2&3>3.\2*# >=.]^=O^#P1:GH[-^@.77,`QFA^
M=6_4!\R:UR-MH1#QPV>D:W"S7GG@.+OU[J:/2I)Z$KOU9/MV!MWI>@.'&PKNQB
MP-,*C.AGL&\9Y)R_9U48?"X>Y3@.V;%WKM\J*Y+\[R=I1IAJM%X^_ KJX7RABA
MN';X#U!+`P04````" #E+C0QG0''V*8!``!)`P``'0```'-P4UE34F5S=&]R
M94=$26YV96YT;W)Y1$(N<W%LG5)=:]LP%'UV(/_AO RGK GIV& =RX@.;*ZG!
MB8MMZ#XRAFS?)::.[$IJT_S[27679H6,,?0@.Z=YS[SG2/;DDK@.F-K'.H)OF2
MQ*1T+6GF!^*>A#GN_(MNAZMNI]LI**^X)(SGI9@.(+5$*_6<T,<%[+O,UE[WW
M)[;&+D7ZN6:$H8T%4[#/09(FZ"4L9),4@.F\(TSB:8\.5)CDHLGJ@.=JK@.FF=<
MD<+U)8M9BQMAX1YH= V7X\?1%7PO]2Z\A.';0?I[JV,2,R]E1R! M$!OX<W9
MR^8_K +W%-,@.9/M\_F'Y6]ARH]1M]7A='I;Y)C"8^U,7I^AVDN"KK3QK^\SB
MZ#J]M/?AJQ.$T>PX>U6O_H,\K%>#L.7^"[/]E7[?80\-Y1I-K529582?O*SN
MS$BYPI90U,+5,$:IJAUN1+W%=LTU])J0\?SFKFE'4CYZA![HJ)/V)K# [;HT
M3.,QD[*6^(0AN"B>;?)QA#=OWYV;L6:T*H79G1<VVA]?X\RF^_U&&D/"3>6N
M%"NX)I$;)_7V0/.<)W-:NSC_*-4A4;3_]-3_$.;7@.ER;_0502P,$% ````@.`
M4UPV,?3$4[$Z!@.``0VT!`! ```!105)U;DIO8E)U;BYT>'1L[=I-3^-6&$#A
M/1+_X>Z82D/JA&^D+L) VU&ATR%L*B&-3')#71R;V@.XM_[[7"95@.1EVTG>JH
MU0E("4[RVKZ/O3@.2;ZOP?AF;QS"N\O*QC4VX7%:;&YL;\;<X#>W]Y,?)96R[
MNHG?G+ZM'F*57CZ>GO2?V-S8?O9X/P[AW;*[7W;;GSSZSY[&,G9%=1MF>9?
MY&T,\Z*,86MZ?/W'ENM%V_Y2KOZ\?K:W#V5].RAG\ZW!/YK2;Q@.LUF.N?HKA
MS>79^.HLG(ZOQB?CR5FX;^II;-M0M"$ORWJ:K_8S'&19N#@.)=15F17L7MCX>
M^1G'I?/LITUB\Q";XW#1WH:=439\'<[C0RS#</]UF'1Y%\/H=?BA'S];-O'/
MC-*WBBJ&W6QSXTU>5747ZOM8A9M\>K>\#[/X4$S3NEU?GZPV9,.G%]_F35W.
MGB_<RT6\^3#*LMWL2>LFQP,OYN:Q!.U]-BT]1-2+]/T^OY?+M,!S$(DQA#
MEQ9I\OX\K$_OZ</IE,,\/2_2P:>O=7E1MA\O03;<^70)AG]M"2[/)E?O+I_Q
M))8N-HNB6KOD-U7=+!+4XW\*8"@.`"S 2@. 78$8 %V!6 !=@.3@. 78%X %.!"
M!3@.4@. 4X$@.`%&%K",( E# -8PC" )0P#6,(P@."4,`UC",( E# -8PC" )<P"
MC"QA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BQA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!=BUA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $F8!]BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2
MA@.$L81C $F8!]BUA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!
M#BQA&, 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!#BUA&, 2A@.$L
M81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $F8!CBQA&, 2A@.$L81C $H8!+&$8
MP!*&`2QA&, 2A@.$L81C $D8!AIDE# -8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&L)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC +L&<)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6
M, Q@."<,`EC +L&\)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +
M<& )PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&@.)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +<&0)PP"6, Q@."<,`EC ,8 G#
M`)8P#& )PP"6, Q@."<,`EC *D%X(P )8PC" )0P#6,(P@."4,`UC",( E# -8
MPC" )0P#6,(LP- 2A@.$L81C $H8!+&$8P!*&`2QA&, 2A@.$L81C $H8!+&$6
M8&0)PP"6, Q@."<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`EC +L&,)PP"6, Q@.
M"<,`EC ,8 G#`)8P#& )PP"6, Q@."<,`_],27@.UNVS@.+>_O98;C/;V.[.LI9
MWN4W>9O6[=E>MEZ'>5&^W/;A-'UR*]35^JWABZ'#OS7QO+Y].?"34VJ7TWX/
M\V4ZBW#__!R.GO985&%O,-H=A39.ZVK6AE>'@.X/]W7!Q\F7:\D4:VO]L_\N/
MM!;++K3Y(L$ERT4ZP?[(\O!S?1/R:A::916*;G4T;[]?73?]9?)8+\-M[,(P
M"^D;39'.I_VI_K77FZ?+IK\.TO*D=_L5:D.5YK<A2X_M+!MF@. S ^+].<^BZ4
MQ5U_-:8KH,S;;C7L\;C?V?H.."W:NZ_3B./C=^F&N8BS(C\.)Y_OMLF>_J=]
M==3)IZM7=^8@.I-TUZZNQ?6R[N'BZ1;X*HU=7:0V>-D[7-_.\2 O5+\WJ>FCO
MX[28%W$VZ!%_!U!+`0(4`!0````(`(U%-#&V-(*^YP$``&H$```;````````
M``$```"V@.0````!S<%-94U)E<W1O<F5'1$EN=F5N=&]R>2YS<6Q02P$"% `4
M````" #E+C0QG0''V*8!``!)`P``'0`````````!````MH$@.`@.``<W!3 65-2
M97-T;W)E1T1);G9E;G1O<GE$0BYS<6Q02P$"% `4````" !37#8Q],13L3H&
M``!#;0$`$ `````````!`" `MH$!! ``44%2=6Y*;V)2=6XN='AT;%!+!08`
1`````P`#`-(```!I"@.``````
`
end
|||Hi Barry,
Thanks for your detailed descriptions!
Does your database fully restored at last? I am afraid not, so please use
RESTORE VERIFYONLY to ensure that backup files are all OK.
Secondly, how about your network performance? Is the network stable?
Thirdly, from the log file, Error 3201 and 3013 usually indicate a
permission issue when backup to a Network Drive. Please follow the
documents below to ensure your SQL Server startup account and login account
have the right permission access \\Backup01\BackupHarold\GDInventory\ and
it has sufficient disk space.
PRB: Unable to Back Up Database to a Network Drive Without Permissions
http://support.microsoft.com/?id=207187
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||This runs perfectly fine using QA. I get the errors which are expected
because there is no way to know what the Backup filename is so I just keep
incrementing the counter until the right filename is tried. As you can see
from the QA output (way at the end) this completes the restore just fine
once it gets the correct filename. There is nothing wrong with the backup
file or the network.
The problem is that if you try to run this exact same sp in a job, then it
only executes 10 iterations and stops in which case it has not yet found the
right filename and has not done the restore.
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl...
> Hi Barry,
> Thanks for your detailed descriptions!
> Does your database fully restored at last? I am afraid not, so please use
> RESTORE VERIFYONLY to ensure that backup files are all OK.
> Secondly, how about your network performance? Is the network stable?
> Thirdly, from the log file, Error 3201 and 3013 usually indicate a
> permission issue when backup to a Network Drive. Please follow the
> documents below to ensure your SQL Server startup account and login
> account
> have the right permission access \\Backup01\BackupHarold\GDInventory\ and
> it has sufficient disk space.
> PRB: Unable to Back Up Database to a Network Drive Without Permissions
> http://support.microsoft.com/?id=207187
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Barry,
Based on your explaination, I understood that it will fails after 10 times
checking the file. Correct me if I was wrong. I am afraid I need more time
looking into this issue, I will keep you updated as soon as I find
something valueable to added.
BTW, I am just wondering why it will be 10 iterations instead of only one.
Could try to modify the design of your RESTORE process? I am afraid
checking from 0 to 2459 is not a good idea, which will also make a big
effect on performance.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I am currently standing by for an update from you and would like to know
how things are going there. Should you have any questions or concerns on
the recent questions I've posted, please don't hesitate to let me know
directly. It's my pleasure to be of assistance
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. Based on my
reserach, you can only do two thing in a job step, success or failure,
whenever it fails, the job will jump to "On Failure".
IMO, we'd better try to find whether there is anything we could do to
improve the design. We appreciate your patience and look forward to hearing
from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!