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 to run a remote process

I have a question regard the scheduling of a database job. I want to run a job that executes the following steps:

1) backup an entire database from DB server A

2) restore the database on DB Sever B

3) run a non database process on Server C (with xp_cmdshell)

Step 1 and 2 are easy enough but how do I start a process on an remote machine (Step 3)?

In my case the process in step 3 is will execute a Cognos DecisionStream job (rundsjob.exe)

My environment is

SQL Server 2000.

Windows Server 2003

Paul

If you are trying to do something like run a batch file remotely, you may want to look at WMIC.

Prior to Windows 2003, you'd use RCMD. This link might help you get started:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/featusability/wmic.mspx

-Sue

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 running with status performing completion action

I noticed this morning that
I have a job status "performing completion action" for one of mine sql scheduled task job.

The job executes a dts pkg (which I just rum manually, and all worked fine)

What does it mean?, I can not stop this job and refresh is not working ether.
What I can do?

Thank you,
Lclose your em and reopen it. it does not refresh very well.|||Sorry,
What is a EM and how I can close it / reopen it.
I'm a user, who is just trying to find out more about processies.

Thank you|||enterprise manager has a problem refreshing sometimes. if you are looking at jobs, i assume this is the application you are looking at.|||I did close it, the message still there.|||close your em and reopen it. it does not refresh very well...or right-click on the job and choose "refresh job" ;)

I also had this happen recently because our jobs are set up to send email notifications when the job is complete, and the servers(s) in question were unable to send emails because the network was having email problems. I ended up stopping and starting the mail service (xp_stopmail, xp_startmail) and the problem was fixed.

Then I had to send an abusive email off to the network admin group for not letting us know when they were going to intentionally damage the network.|||THANK YOU FOR ALL SUGGESTIONS, BUT WE HAD TO RESTART THE SERVER.
AS WELL AS OUT MAIL SERVER WAS REBOOTED.

jOB TASK WAS NOT ABLE TO SEND AN E-MAIL DUE TO MAIL SERVER FAILRE,

THANK YOU|||Yeah, I was gonna say...

issue the xp_stopmail and xp_startmail on the affected server, OR reboot the entire infrastructure.

either one will work ;)

Job Fails

I have a package that runs fine when manually executed, but if a job is created that executes the package the job fails. The job is run as the same account that runs sql server agent. The error message that appears is to see the job history but the job history just says the package failed. When I try to add logging no log is created.When you manually execute the package are you executing it under the same account that Agent is running. If not, then try and do that and see if the package runs. If it doesn't then it would most likely be a permissions problem for that account. If it does then you should try and post this in the Agent forum.

Thanks,
Matt|||

I get this exact same problem... but only with one specific package.
Other packages run fine - both manually and as jobs.

This particular package runs fine manually but NOT as job.
This package only has file tasks which copies file from a remote location to local disk. I have double-checked permissions. The account which has permission on the remote location is the same account under which SQL Server, and SQL Agent are running.

Any clue?

thanks,
Nitesh

|||So you are running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server? Then I don't have any further thoughts but you should first verify that if you use runas to run a cmd shell under the agent account that the package still works. You should verify that the job isn't running under a proxy with a different account. If both these are true then you should post in the Agent forum since this is not a SSIS problem if the package runs when not run under Agent.

Sorry I can't be of more assistance,
Matt|||

Yes, I am running both SQLServer and Agent as a domain account and the same domain acount has permissions on the remote server.

I have 16 packages scheduled like this. 15 of them work fine (none of them copy files from remote location). Only this package breaks.

I will try to post this on Agent forum and update this thread with replies.

thanks,
Nitesh

|||Matt,

I found the problem.

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

thanks,
Nitesh|||

How are you mapping to X:? Are you executing the package on a different machine when you execute it under a job? Sounds like a problem with the context of the machine; doesn't have the mapping. So, if it doesn't have the mapping, it will be looking for the literal X: drive.
K

|||

Nitesh Ambastha wrote:

I had mapped the network share (\\servername\sharename) to X:\ on the box running SSIS. So, my file source connection strings looked like X:\filename.txt

When I changed the file source connection string to \\servername\sharename\filename.txt , then it started working fine - both manually AND as a job.

Although my problem is solved, can you throw some light on why it happens like this?

This is the way share security works in Windows 2003 - the network share mapped by user is private to this user session, and is invisible to other users running on same machine.

E.g. I mapped a disk using 'net share x: \\machine\share' and now have a drive x:. But when I (simultaneously) create another remote desktop connection to the same machine, I don't have x: drive.

The Agent creates a new user session, and thus this session does not see the share created by interactive user session.|||Thanks Michael.
That explains it.

regards,
Nitesh|||This is really not important but after reading your post, I created 1 desktop connection, mapped the network drive (X: drive to \\remoteserver\sharename) and then created another desktop connection to the same machine - and I found the mapped network drive (X: drive). Am I missing something or maybe network admins here carry over my profile and map it for me :(

regards,
Nitesh|||Did you check 'reconnect at logon' option (or /persistent:yes in net.exe command line)? In this case every logon creates a connection, but technically they are different :)

Is this Windows 2003? I'm not sure it worked the same way on Windows 2000.

This is simple to check with SysInternal's WinObj utility - \DosDevices object used to live in root, but now it is \Sessions\<N>\DosDevices.

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
>>

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.earthlink.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
>|||I believe that I saw this same question posted not too long ago.
The SP has to return an integer return status in order for DTS to know if it
completed successfully.
In other words, put
RETURN 0
at the bottom of your procedure.
--
"Neil" wrote:

> I have three stored procedures that need to run nightly in SQL 7. The thre
e
> 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 ste
ps
> 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 Step
s
> 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.earthlink.net...
>