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'?
BarryHi 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',REPLACE
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!|||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!|||Sure we could possibly improve the performance a little (for instance I
could do a binary search for the correct number which would in threory take
fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
name I want for the backup file), but so what. The question is why does
this die as a job after only ten iterations.? You are looking for a
workareound and I am looking for an answer to my question/problem. You are
not solving anything. Why does the sp fail to run as a job when it runs
perfectly well in query analyser?
Barry
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> 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!
>|||Hello Barry,
I would appreciate your patience while I am looking into this issue.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 30 Sep 2004 12:23:46 -0500
| Lines: 43
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sure we could possibly improve the performance a little (for instance I
| could do a binary search for the correct number which would in threory
take
| fewer trys) or MS could rewrite their DB Maint Plans so I can specify the
| name I want for the backup file), but so what. The question is why does
| this die as a job after only ten iterations.? You are looking for a
| workareound and I am looking for an answer to my question/problem. You
are
| not solving anything. Why does the sp fail to run as a job when it runs
| perfectly well in query analyser?
|
| Barry
|
|
| ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
message
| news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > 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!
| >
| >
|
|
||||Sure, thanks for taking a look.
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > 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!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: <barryfz@.home.com>
>References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
>Subject: Re: Error cause job to quit running
>Date: Mon, 4 Oct 2004 08:26:04 -0500
>Lines: 101
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#a2VKXhqEHA.1296@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12|||Any luck figuring this out yet?
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I would appreciate your patience while I am looking into this issue.
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | Lines: 43
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Sure we could possibly improve the performance a little (for instance I
> | could do a binary search for the correct number which would in threory
> take
> | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
> the
> | name I want for the backup file), but so what. The question is why does
> | this die as a job after only ten iterations.? You are looking for a
> | workareound and I am looking for an answer to my question/problem. You
> are
> | not solving anything. Why does the sp fail to run as a job when it runs
> | perfectly well in query analyser?
> |
> | Barry
> |
> |
> | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> message
> | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > 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!
> | >
> | >
> |
> |
> |
>|||Hello Barry,
I have already responded to your questions (on 10/4/04) as follows,
Hello Barry,
Looking at the issue, you are probably hitting the BUG documented in
FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
SQLAgent job to fail and to lose output in SQL Server
http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
The original intension of this behavior (by design) was to prohibit the
SQLAgent form running the job if there is an error. This has the potential
for denial of service by filling the error log up if the job raises an
error in an endless loop.
Note that, as mentioned in the KB article, this fix applies only to Errors
with Severity less than 20.
Please follow the workarounds documented in the KB.
If you want the HOTFIX please send email to (remove "online." from this no
Spam email address):
mailto:dscommhf@.online.microsoft.com with the following information,
*Include "Followup: < 24820437>" in the email Subject.
*Location of the post
*Subject Line
*First Name, Last Name
*MSDN Subscriber ID
*Company name (if any)
*Phone number
*e-mail address
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--
| From: <barryfz@.home.com>
| References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
<BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
<OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
<KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
<#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
<dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
<eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
<2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
<82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
<NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
<OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
<ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
| Subject: Re: Error cause job to quit running
| Date: Thu, 7 Oct 2004 07:14:06 -0500
| Lines: 104
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Any luck figuring this out yet?
|
| Barry
|
|
| "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
message
| news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
| >
| > Hello Barry,
| >
| > I would appreciate your patience while I am looking into this issue.
| >
| > Thanks,
| >
| > Vikrant Dalwale
| >
| > Microsoft SQL Server Support Professional
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > Get secure !! For info, please visit http://www.microsoft.com/security.
| > Please reply to Newsgroups only.
| >
| > --
| > | From: <barryfz@.home.com>
| > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
| > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
| > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
| > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
| > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
| > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
| > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
| > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
| > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
| > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
| > | Subject: Re: Error cause job to quit running
| > | Date: Thu, 30 Sep 2004 12:23:46 -0500
| > | Lines: 43
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Sure we could possibly improve the performance a little (for instance
I
| > | could do a binary search for the correct number which would in threory
| > take
| > | fewer trys) or MS could rewrite their DB Maint Plans so I can specify
| > the
| > | name I want for the backup file), but so what. The question is why
does
| > | this die as a job after only ten iterations.? You are looking for a
| > | workareound and I am looking for an answer to my question/problem. You
| > are
| > | not solving anything. Why does the sp fail to run as a job when it
runs
| > | perfectly well in query analyser?
| > |
| > | Barry
| > |
| > |
| > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
| > message
| > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
| > | > 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!
| > | >
| > | >
| > |
| > |
| > |
| >
|
|
||||Thanks. We are testing the workaround now. I do not see a posting on the
board from you on 10/4 and if you sent a private email it was not received.
Barry
"Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in message
news:N%232G5OKrEHA.3356@.cpmsftngxa06.phx.gbl...
> Hello Barry,
> I have already responded to your questions (on 10/4/04) as follows,
> Hello Barry,
> Looking at the issue, you are probably hitting the BUG documented in
> FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a
> SQLAgent job to fail and to lose output in SQL Server
> http://support.microsoft.com/default.aspx?scid=KB;[LN];309802
> The original intension of this behavior (by design) was to prohibit the
> SQLAgent form running the job if there is an error. This has the potential
> for denial of service by filling the error log up if the job raises an
> error in an endless loop.
> Note that, as mentioned in the KB article, this fix applies only to
> Errors
> with Severity less than 20.
> Please follow the workarounds documented in the KB.
> If you want the HOTFIX please send email to (remove "online." from this no
> Spam email address):
> mailto:dscommhf@.online.microsoft.com with the following information,
> *Include "Followup: < 24820437>" in the email Subject.
> *Location of the post
> *Subject Line
> *First Name, Last Name
> *MSDN Subscriber ID
> *Company name (if any)
> *Phone number
> *e-mail address
>
> Thanks,
> Vikrant Dalwale
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Get secure !! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
> --
> | From: <barryfz@.home.com>
> | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> <ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl>
> | Subject: Re: Error cause job to quit running
> | Date: Thu, 7 Oct 2004 07:14:06 -0500
> | Lines: 104
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <Okpa7cGrEHA.2732@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | Path:
> cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
> phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:363056
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Any luck figuring this out yet?
> |
> | Barry
> |
> |
> | "Vikrant V Dalwale [MSFT]" <vikrantd@.online.microsoft.com> wrote in
> message
> | news:ZjOuuF$pEHA.3944@.cpmsftngxa06.phx.gbl...
> | >
> | > Hello Barry,
> | >
> | > I would appreciate your patience while I am looking into this issue.
> | >
> | > Thanks,
> | >
> | > Vikrant Dalwale
> | >
> | > Microsoft SQL Server Support Professional
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > Get secure !! For info, please visit
> http://www.microsoft.com/security.
> | > Please reply to Newsgroups only.
> | >
> | > --
> | > | From: <barryfz@.home.com>
> | > | References: <OKSth7PnEHA.1444@.TK2MSFTNGP10.phx.gbl>
> | > <BGMEZlTnEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <OVlR5CwnEHA.556@.tk2msftngp13.phx.gbl>
> | > <KkGmMr9nEHA.2864@.cpmsftngxa06.phx.gbl>
> | > <#jj#0MMoEHA.3668@.TK2MSFTNGP15.phx.gbl>
> | > <dAnWzjUoEHA.3356@.cpmsftngxa06.phx.gbl>
> | > <eHLZgVWoEHA.648@.tk2msftngp13.phx.gbl>
> | > <2YCkG5roEHA.3312@.cpmsftngxa06.phx.gbl>
> | > <82#a5YUpEHA.2420@.cpmsftngxa06.phx.gbl>
> | > <NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl>
> | > | Subject: Re: Error cause job to quit running
> | > | Date: Thu, 30 Sep 2004 12:23:46 -0500
> | > | Lines: 43
> | > | X-Priority: 3
> | > | X-MSMail-Priority: Normal
> | > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | > | X-RFC2646: Format=Flowed; Original
> | > | Message-ID: <OQInVJxpEHA.1296@.TK2MSFTNGP12.phx.gbl>
> | > | Newsgroups: microsoft.public.sqlserver.server
> | > | NNTP-Posting-Host: exchange.gumdropbooks.com 12.108.131.130
> | > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:362199
> | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | > |
> | > | Sure we could possibly improve the performance a little (for
> instance
> I
> | > | could do a binary search for the correct number which would in
> threory
> | > take
> | > | fewer trys) or MS could rewrite their DB Maint Plans so I can
> specify
> | > the
> | > | name I want for the backup file), but so what. The question is why
> does
> | > | this die as a job after only ten iterations.? You are looking for a
> | > | workareound and I am looking for an answer to my question/problem.
> You
> | > are
> | > | not solving anything. Why does the sp fail to run as a job when it
> runs
> | > | perfectly well in query analyser?
> | > |
> | > | Barry
> | > |
> | > |
> | > | ""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in
> | > message
> | > | news:NV6c26fpEHA.404@.cpmsftngxa06.phx.gbl...
> | > | > 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!
> | > | >
> | > | >
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>