Execute sp_start job on many clients

I have about 200 msde instances running on several machines.I want to
execute sp_start_job on several clients and manage the jobs such that
the job schedules specified by the clients do not clash and can be
changed remotely if requred by us without dropping and recreating the
jobs.
All this nees to be done form a central server.How can this be done ?Do
I have to create a web service to do this?
I would not want to register each client as a remote server?
Thanks in anticipations.
AjayHi
This sounds like you want to take the scheduling away from SQL Server Agent,
in which case you will just need to create the job but not schedule it, then
have your own scheduling starting off the job. It would not need to be a web
service unless that is how you implement the registration process.
John
"AG" <ajayz90@.hotmail.com> wrote in message
news:1125849309.901461.240560@.g43g2000cwa.googlegroups.com...
>I have about 200 msde instances running on several machines.I want to
> execute sp_start_job on several clients and manage the jobs such that
> the job schedules specified by the clients do not clash and can be
> changed remotely if requred by us without dropping and recreating the
> jobs.
> All this nees to be done form a central server.How can this be done ?Do
> I have to create a web service to do this?
> I would not want to register each client as a remote server?
> Thanks in anticipations.
>
> Ajay
>|||Here is what the scenario is like.
I have a sql server behind a fire wall so only the client msde instances
can acess the sql server , the connection cannot be initiated by the sql
server.I have 200 client MSDE instances and they have dts packages to
load the data.I want to be able to monitor their hear beat and also
centrally control their schedules.I was wondering what approch would be
good besides the web service sinc i caanor register the remote servers
on MY sql becuase of the fire wall.
AJay
Ajay Garg
Data Warehouse Programmer
MCDBA
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
If you are going to administer the jobs centrally then you will need some
way of starting the job, whether that is an agent application or web service
running on the MSDE client is in the design. You will probably need two way
communication to make it work.
An alternative would be to use a VPN which would allow you to register the
instances.
John
"Ajay Garg" <ajayz90@.hotmail.com> wrote in message
news:%236l384tsFHA.304@.TK2MSFTNGP11.phx.gbl...
> Here is what the scenario is like.
> I have a sql server behind a fire wall so only the client msde instances
> can acess the sql server , the connection cannot be initiated by the sql
> server.I have 200 client MSDE instances and they have dts packages to
> load the data.I want to be able to monitor their hear beat and also
> centrally control their schedules.I was wondering what approch would be
> good besides the web service sinc i caanor register the remote servers
> on MY sql becuase of the fire wall.
>
> AJay
>
> Ajay Garg
> Data Warehouse Programmer
> MCDBA
> *** Sent via Developersdex http://www.examnotes.net ***

Execute proceduers from another Proceduer with error handling

I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

Regard JoelCheck the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

<joel.sjoo@.gmail.comwrote in message
news:1166184223.625115.296200@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?
>
I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
>
>
Regard Joel
>

|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

Even better is this check:

IF @.Returcode <0 OR @.@.error <0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(joel.sjoo@.gmail.com) writes:

Quote:

Originally Posted by

I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

I don't relly know what this is supposed to be, but note that the first
GO marks the end of USP_RUNJOB, so the call to SP_TEST2 is not part of
that procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

Execute Job from another Job based on failure

I have two jobs A and B. I want to execute Job B is job
A fails but only if the error number is 3013. How can I
make this happen?
Thanks,
Vichow about sp_start_job?
"Vic" <vduran@.specpro-inc.com> wrote in message
news:e9fa01c3f183$e79f4350$a101280a@.phx.gbl...
> I have two jobs A and B. I want to execute Job B is job
> A fails but only if the error number is 3013. How can I
> make this happen?
> Thanks,
> Vic

Execute Job from another Job based on failure

I have two jobs A and B. I want to execute Job B is job
A fails but only if the error number is 3013. How can I
make this happen?
Thanks,
Vichow about sp_start_job?
"Vic" <vduran@.specpro-inc.com> wrote in message
news:e9fa01c3f183$e79f4350$a101280a@.phx.gbl...
> I have two jobs A and B. I want to execute Job B is job
> A fails but only if the error number is 3013. How can I
> make this happen?
> Thanks,
> Vic