Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
BillPrint statements in the stored procedure can result in the
sqlstate message.
-Sue
.
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:

>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill

Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
BillPrint statements in the stored procedure can result in the
sqlstate message.
-Sue
.
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:
>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill

Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
Bill
Print statements in the stored procedure can result in the
sqlstate message.
-Sue
..
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:

>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill
sql

Job step failing with "ConnectionRead (WrapperRead())." error

We have a job step which executes a stored proc that fails intermittently
with this error:
Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11). The step failed.
This stored proc executes just fine through QA.
Could it be that the SP does execute but that the step is being incorrectly
reported as failed?
Thank you
-- alan cranfield, DBAuse same ANSI settings in job like in QA
"Cranfield" wrote:

> We have a job step which executes a stored proc that fails intermittently
> with this error:
> Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTA
TE
> 01000] (Message 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11). The step failed.
> This stored proc executes just fine through QA.
> Could it be that the SP does execute but that the step is being incorrectl
y
> reported as failed?
> Thank you
> --
> -- alan cranfield, DBA|||I tried your suggestion. Job step still fails.
This must be a bug. I will raise a case with PSS.
thanks for the reply.
-- cranfield, DBA
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use same ANSI settings in job like in QA
>
> "Cranfield" wrote:
>

Job step failing with "ConnectionRead (WrapperRead())." error

We have a job step which executes a stored proc that fails intermittently
with this error:
Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11). The step failed.
This stored proc executes just fine through QA.
Could it be that the SP does execute but that the step is being incorrectly
reported as failed?
Thank you
--
-- alan cranfield, DBAuse same ANSI settings in job like in QA
"Cranfield" wrote:
> We have a job step which executes a stored proc that fails intermittently
> with this error:
> Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
> 01000] (Message 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11). The step failed.
> This stored proc executes just fine through QA.
> Could it be that the SP does execute but that the step is being incorrectly
> reported as failed?
> Thank you
> --
> -- alan cranfield, DBA|||I tried your suggestion. Job step still fails.
This must be a bug. I will raise a case with PSS.
thanks for the reply.
--
-- cranfield, DBA
"Aleksandar Grbic" wrote:
> use same ANSI settings in job like in QA
>
> "Cranfield" wrote:
> > We have a job step which executes a stored proc that fails intermittently
> > with this error:
> >
> > Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
> > 01000] (Message 258) General network error. Check your network
> > documentation. [SQLSTATE 08S01] (Error 11). The step failed.
> >
> > This stored proc executes just fine through QA.
> >
> > Could it be that the SP does execute but that the step is being incorrectly
> > reported as failed?
> >
> > Thank you
> >
> > --
> > -- alan cranfield, DBA

Job step failing with "ConnectionRead (WrapperRead())." error

We have a job step which executes a stored proc that fails intermittently
with this error:
Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11). The step failed.
This stored proc executes just fine through QA.
Could it be that the SP does execute but that the step is being incorrectly
reported as failed?
Thank you
-- alan cranfield, DBA
use same ANSI settings in job like in QA
"Cranfield" wrote:

> We have a job step which executes a stored proc that fails intermittently
> with this error:
> Executed as user: domain\user. ConnectionRead (WrapperRead()). [SQLSTATE
> 01000] (Message 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11). The step failed.
> This stored proc executes just fine through QA.
> Could it be that the SP does execute but that the step is being incorrectly
> reported as failed?
> Thank you
> --
> -- alan cranfield, DBA
|||I tried your suggestion. Job step still fails.
This must be a bug. I will raise a case with PSS.
thanks for the reply.
-- cranfield, DBA
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> use same ANSI settings in job like in QA
>
> "Cranfield" wrote:

Job step + linked server help

I have a job step that executes a lot of stored procs against linked servers
such as
exec server1.db.dbo.abc
exec server2.db.dbo.abc
exec server3.db.dbo.abc
exec server4.db.dbo.abc
I do not wish to create multiple job steps but would like to know
programatically how i can run all those statements even if say linked server
server2 is unavailable. Currently if server2 is unavailable, it just exits
out. I would like for it to proceed and execute the last 2 stored procs for
server3 and server4TSQL jobsteps will exit if you get errors. You would have to talk to the person who wrote agent and
get a new compile to change that.
One alternative is to use a CMDExec jobstep and OSQL.EXE instead.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%23WcbEJhkDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I have a job step that executes a lot of stored procs against linked servers
> such as
> exec server1.db.dbo.abc
> exec server2.db.dbo.abc
> exec server3.db.dbo.abc
> exec server4.db.dbo.abc
> I do not wish to create multiple job steps but would like to know
> programatically how i can run all those statements even if say linked server
> server2 is unavailable. Currently if server2 is unavailable, it just exits
> out. I would like for it to proceed and execute the last 2 stored procs for
> server3 and server4
>
>|||Hassan
Check for PING to the server.
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + 00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
..................
END
DROP TABLE #t_ip
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#WcbEJhkDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I have a job step that executes a lot of stored procs against linked
servers
> such as
> exec server1.db.dbo.abc
> exec server2.db.dbo.abc
> exec server3.db.dbo.abc
> exec server4.db.dbo.abc
> I do not wish to create multiple job steps but would like to know
> programatically how i can run all those statements even if say linked
server
> server2 is unavailable. Currently if server2 is unavailable, it just exits
> out. I would like for it to proceed and execute the last 2 stored procs
for
> server3 and server4
>
>

Job Step

Hi All,

I am executing a stored procedure within a job step. This stored procedure executes the xp_sqlmaint stored procedure with some parameters. I run this job as 'sa'.
The job fails with the following error message:

sqlmaint.exe failed. Step failed.

What could cause this error? Please help. Thanks.Copy and paste the sqlmaint command to Query Analyzer and see if you get more informative error messages.|||This is the stored procedure that I have created.

CREATE PROCEDURE usp_RebuildIndexes

AS

DECLARE @.PlanID char(36)
SET @.PlanID = NEWID()

EXECUTE master.dbo.xp_sqlmaint N'-PlanID '' + @.PlanID + '' -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

Then within the job step I run:

execute msdb.dbo.usp_RebuildIndexes

and when I run the job I get that error message.|||You seem to be confused about PlanIDs. They are created by the Maintenance Plan Wizard and have to correspond to an existing Maintenance Plan. You are create a random PlanID on the fly using newid(), which of course does not correspond to any existing plan, so your sqlmaint statement fails.

Here's a tip: you can refer to your maintenance plans by name rather than by PlanID by using the "N'-PlanName" parameter. Take a few minutes with Books Online to review all the parameters that are available for sql_maint. It is quite powerful.|||Thank you very much for your help.|||This is what the job step looks like now and I am still getting the same error message.

EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

What am I doing wrong now?|||SQL Server does not like double quotes. Try this:EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight Maintenance Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'|||Just tried it. Same error message.|||Ok. Try eliminating the spaces in your file name. SQLMaint may be interpreting as separate parameters:
EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight_Maintenance_Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'|||That didn't work either. Same error.|||Not sure what is going on then. Is that the error you get when you run the statement through Query Analyzer?

Try removing parameters one at a time to narrow in on the culprit.|||I executed it as a step in the job in EM. When I looked in the maintenance report I found the following error:

Error 21268: [SQL-DMO]Row or column specified is outside the range of the specified query result set.|||That's the error I got when I executed your statement in Query Analyzer, so try dropping off parameters to see which one is causing the problem.|||Once I added the Server name, the user and the password to the script it worked. I made this user a sysadmin.

So now it looks like this:

EXEC master.dbo.xp_sqlmaint ' -S servername -U user -P password
-PlanName OptimizationPlan -Rpt "C:\MaintPlanRpts\Plan.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

I don't understand why I need to supply these 3 parameters. Any ideas?|||I would guess it has something to do with the accounts under which you, SQL Server, and SQL Server Agent are logging in.|||Thanks for the help.

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/defaul...730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand

Job scheduler

Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.

>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
the
>
>.
>

Job scheduler

Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
>> Hi all
>> In which table in msdb Database the Executing status of
>> the job is stored. I need the table name - which holds
the
>> information like
>> 1. Job started.
>> 2. Job executing
>> 3. Job completed info.
>> The above status may be in the same field of the table
>> Thanks in advance
>> Anand.
>
>.
>

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;249730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand|||Do you SET NOCOUNT ON at the beginning of your stored
procedure? Sometimes this can help with this type of
problem.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>Hi all
>When I run a stored procedure using the job scheduler in
>SQL 2000 it takes more time to complete comparing when I
>run it in the Query analyser.
>Is there any reason behind this?
>Example
>I am running a stored Procedure for generating a report
>every one minute using a job scheduler.
>It takes 13 seconds to complete.
>Whereas when I run the same SP in Query Analyser it
takes
>only 2 sec or 3 seconds.
>Please clarify.
>Thanks in advance
>Anand
>.
>

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
Will
Never Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:

> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job History

Is there a limit to the number of rows that are stored in job history?
I see where the Maintenance Plan history can be set, but not for the
individual jobs. I have set up a test job and run it over 100 times, but
there seems to be a limit of 100 entries in the sysjobhistory table.
Is this correct and if it is, where is that documented?
Thanks,
WillNever Mind - I see that the setting is in the SQL Server Agent properties.
"Will Winn" wrote:
> Is there a limit to the number of rows that are stored in job history?
> I see where the Maintenance Plan history can be set, but not for the
> individual jobs. I have set up a test job and run it over 100 times, but
> there seems to be a limit of 100 entries in the sysjobhistory table.
> Is this correct and if it is, where is that documented?
> Thanks,
> Will

Job Failure

Dear All

A quick question:

I run a set of scheduled jobs. The jobs run Stored Procedures.
However, if the sproc fails, the job quits and moves on to the next
one. However, the sproc should carry on. E.g, IF find bad records,
EXPORT to file, GO TO NEXT RECORD (BUT DON'T QUIT THE SPROC). The job
scheduler does not allow this, therefore, the sproc does not get a
chance to finish.

Is there a way to ensure a sproc can finish before moving on to the
next step?

Thanks

Simonaaronss@.the-mdu.com (Simon) wrote in message news:<f526ea06.0402050300.499b8b81@.posting.google.com>...
> Dear All
> A quick question:
> I run a set of scheduled jobs. The jobs run Stored Procedures.
> However, if the sproc fails, the job quits and moves on to the next
> one. However, the sproc should carry on. E.g, IF find bad records,
> EXPORT to file, GO TO NEXT RECORD (BUT DON'T QUIT THE SPROC). The job
> scheduler does not allow this, therefore, the sproc does not get a
> chance to finish.
> Is there a way to ensure a sproc can finish before moving on to the
> next step?
> Thanks
> Simon

I'm not sure I understand you exactly - I guess the issue is not
really the scheduling, but rather how to handle an error in your
stored procedure? If the procedure exists because of an error, then
control goes back to the scheduled job. You could set a number of
retry attempts for that job step, but it would probably be better to
handle or prevent the error in your procedure. There are some useful
articles here:

http://www.sommarskog.se/index.html

If this isn't helpful, perhaps you could post (some of) your procedure
code to show where it fails, along with the error message, and someone
may be able to suggest how to handle the error condition.

Simon

Job Failure

I have a job that runs stored procedures. I ran the stored
procedures in the correct order myself and they ran fine.
But when I try to run them in a job the job fails
immediately. I tried changing the user for the job so
that it matches the creator of the database and stored
procedures, and the job still failed. So then I wrote the
output of the job to a file, and this is the error that is
happening.
Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
error. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Does anyone have any ideas?
Thank you for your assistance,
AdamIs the job owner sysadmin? Are you accessing linked server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:fed501c411be$81b90020$a6
01280a@.phx.gbl...
> I have a job that runs stored procedures. I ran the stored
> procedures in the correct order myself and they ran fine.
> But when I try to run them in a job the job fails
> immediately. I tried changing the user for the job so
> that it matches the creator of the database and stored
> procedures, and the job still failed. So then I wrote the
> output of the job to a file, and this is the error that is
> happening.
> Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
> error. [SQLSTATE 42000]
> Msg 7312, Sev 16: [SQLSTATE 01000]
> Does anyone have any ideas?
> Thank you for your assistance,
> Adam|||Yes, I am accessing several linked servers. I have tried
running the job with the owner as sysadmin and as another
user. Both fail.
>--Original Message--
>Is the job owner sysadmin? Are you accessing linked
server?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message news:fed501c411be$81b90020$a601280a@.phx.gbl...
stored
fine.
the
is
>
>.
>|||Do you get the same error regardless of which login owns the job?
You will get problems is the job owner isn't sysadmin, as Agent then tries t
o emulate the job owner's login's
user name in the database using the SETUSER command. And after executing SET
USER, you are not allowed to do
operations at the server level. accessing linked server is a server (long) l
evel operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:12d7401c411df$be50c4c0$a401280a
@.phx.gbl...
> Yes, I am accessing several linked servers. I have tried
> running the job with the owner as sysadmin and as another
> user. Both fail.
> server?
> message news:fed501c411be$81b90020$a601280a@.phx.gbl...
> stored
> fine.
> the
> is|||Yes, I have tried running the job as the database's owner
and as the 'sa' system account. Both fail, yet both are
set up to use a valid account on the linked servers. I
have this same setup on another server linked to the same
servers, and it works just fine.
>--Original Message--
>Do you get the same error regardless of which login owns
the job?
>You will get problems is the job owner isn't sysadmin, as
Agent then tries to emulate the job owner's login's
>user name in the database using the SETUSER command. And
after executing SETUSER, you are not allowed to do
>operations at the server level. accessing linked server
is a server (long) level operation.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
><anonymous@.discussions.microsoft.com> wrote in message
news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
tried
another
so
stored
wrote
that
reported an
>
>.
>|||I see... There goes my theory. I'm afraid I'm out of ideas, then. If you hav
e searched KB and are on current
service pack, and you don't get other suggestions here, I suppose its MS PSS
time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:1339b01c411ec$094cd200$a
101280a@.phx.gbl...
> Yes, I have tried running the job as the database's owner
> and as the 'sa' system account. Both fail, yet both are
> set up to use a valid account on the linked servers. I
> have this same setup on another server linked to the same
> servers, and it works just fine.
> the job?
> Agent then tries to emulate the job owner's login's
> after executing SETUSER, you are not allowed to do
> is a server (long) level operation.
> news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
> tried
> another
> so
> stored
> wrote
> that
> reported an

Job failure

I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> >--Original Message--
> >Is the recovery model of the destination database
> changed recently? Are the
> >backup jobs running successfully?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
> >I have a job that runs every evening that calls 2 stored
> >procedures. The first sp inserts new records from a
> >source database.table into a destination database.table
> >the second job updates the records that were changed at
> >the source and updates them at the destination.
> >
> >This job has run successfully without problems for 2+
> >years. Now for the past two days the second step is
> >blowing up the transaction log to more than 7GB (it's
> >limit). Any idea what could cause this new behaviour?
> >
> >Any suggestions as to what I need to look at would be
> >most appreciated.
> >
> >Thanks,
> >Marc
> >
> >
> >.
> >|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
>> Could be that the recovery model was changed from
simple
>> to full, but I'm not 100% as this isn't my server.
>> The backups that are scheduled after this job runs fail
>> as there is no longer adequate disk space.
>> >--Original Message--
>> >Is the recovery model of the destination database
>> changed recently? Are the
>> >backup jobs running successfully?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>> >I have a job that runs every evening that calls 2
stored
>> >procedures. The first sp inserts new records from a
>> >source database.table into a destination
database.table
>> >the second job updates the records that were changed
at
>> >the source and updates them at the destination.
>> >
>> >This job has run successfully without problems for 2+
>> >years. Now for the past two days the second step is
>> >blowing up the transaction log to more than 7GB (it's
>> >limit). Any idea what could cause this new behaviour?
>> >
>> >Any suggestions as to what I need to look at would be
>> >most appreciated.
>> >
>> >Thanks,
>> >Marc
>> >
>> >
>> >.
>> >
>
>.
>

Job Execution

I've got a scheduled job that runs a package every couple of minutes, and I also have a method in an application that calls a stored proc that can also execute the same scheduled job. My question is what will happen if I call the stored proc to execute the job at the same time that the job is executing on its regular schedule. Is it possible that the an instance of the package will execute in parallel?

SQL Agent jobs won't run multiple instances simultaneously, as long as you are calling the same job.

Job Executes Only First Step

I have three stored procedures that need to run nightly in SQL 7. The three
procedures are not related; but to keep the procedures from running at the
same time, I placed them as three steps of a single job. The first two steps
are set to "Goto next step" on success; the last step is set to "Quit with
success."

The job runs every night. However, only the first step/procedure is
executed. Also, the first step has a green flag next to its ID in the Steps
tab of the job properties

I suppose I could just call all three SPs from a single stored procedure
that is run nightly. But I thought that putting them as three steps in a
single job would cause all three to run. What am I doing wrong?

Thanks!

NeilAre you sure only the first step is executing? If you right click on the
job and choose show history, then check the checkbox to show the steps do
you see all three or only one?

--
Andrew J. Kelly SQL MVP

"Neil" <nospam@.nospam.net> wrote in message
news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>I have three stored procedures that need to run nightly in SQL 7. The three
>procedures are not related; but to keep the procedures from running at the
>same time, I placed them as three steps of a single job. The first two
>steps are set to "Goto next step" on success; the last step is set to "Quit
>with success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the
> Steps tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil|||Yes, you are right. All three steps are executing. There was a problem with
the data which was giving the impression that the job wasn't executing.
Thanks!

Neil

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OsJ7bwNMGHA.740@.TK2MSFTNGP12.phx.gbl...
> Are you sure only the first step is executing? If you right click on the
> job and choose show history, then check the checkbox to show the steps do
> you see all three or only one?
> --
> Andrew J. Kelly SQL MVP
>
> "Neil" <nospam@.nospam.net> wrote in message
> news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>>I have three stored procedures that need to run nightly in SQL 7. The
>>three procedures are not related; but to keep the procedures from running
>>at the same time, I placed them as three steps of a single job. The first
>>two steps are set to "Goto next step" on success; the last step is set to
>>"Quit with success."
>>
>> The job runs every night. However, only the first step/procedure is
>> executed. Also, the first step has a green flag next to its ID in the
>> Steps tab of the job properties
>>
>> I suppose I could just call all three SPs from a single stored procedure
>> that is run nightly. But I thought that putting them as three steps in a
>> single job would cause all three to run. What am I doing wrong?
>>
>> Thanks!
>>
>> Neil
>>