How to run multiple dependent SQL Jobs with OSQL

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

DaveCreate a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

-PatP|||I already tried that with no success. The logic looks as follows:

sp_start_job 'DBA Test' -- Inserts one record into a table
go
sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
go
sp_start_job 'DBA Test3' -- Insert a record
go

All jobs execute immediately.

Dave|||That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

-PatP|||Lightbulb.

We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

Dave|||I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

Any suggestions.

Dave

How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

How to revert the commited query in MS Sql Server?

Accedentally i have updated the specific field by update query of the table and to preserve the previous data .....so please help me in this regard

How to revert the commited query in MS Sql Server?

ThanX in advance!!!!!!!!

If you've commited the transaction then your only real option is to restore your database from backup. Depending on the recovery model of the database and your backup strategy it may be possible to recover to the point just before you made the update. However, changes after this point will be lost.

Of course, if the data is fairly static, its possible you could restore your database as a new database and then manually correct the action by updating the data from the restore database.

Check out overview of Restore and Backup recovery in Books Online.

HTH!

|||

Thanx. Richbrownesq

i tried with point in time recovery with the RESTORE command but it show the following error..

Msg 4338, Level 16, State 1, Line 2

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

pleas help me in this regard.

|||

The error looks like its saying that your STOPAT time is incorrect. Make sure that you are applying your transaction logs in the correct order and that the date/time specified is accurate.

If you're still having issues, please post an example of the syntax you are using to restore your database and logs.

How to retrieve the text of UDF in SQL Server 2005

In the previous versions of SQL Server, retrieving the text of User
Defined Functions was easy:
SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
For some reason the above statement doesn't work in SQL Server 2005 (it
works for procedures and for views, but not for functions).
How can I retrieve the text of User Defined Functions in SQL Server
2005?
TIA
Dariusz DziewialtowskiHi
No, it works very well for user's UDF as well. This udf is not created by
an user ,moreover if i'm mo mistaken it isnt supported by MS
However you can achive it by issuing
sp_helptext 'fn_dblog'
<dariusz.dziewialtowski@.gmail.com> wrote in message
news:1144554215.276141.57970@.u72g2000cwu.googlegroups.com...
> In the previous versions of SQL Server, retrieving the text of User
> Defined Functions was easy:
> SELECT text FROM syscomments sc INNER JOIN sysobjects so ON sc.id =
> so.id WHERE so.name = 'fn_dblog' ORDER BY sc.colid
> For some reason the above statement doesn't work in SQL Server 2005 (it
> works for procedures and for views, but not for functions).
> How can I retrieve the text of User Defined Functions in SQL Server
> 2005?
> TIA
> Dariusz Dziewialtowski
>|||Hi Uri,
Thank you for your help.

>This udf is not created by an user
Yes, I gave it only as an example.

>However you can achive it by issuing sp_helptext 'fn_dblog'
I didn't think of that - I have to retrieve the text programmatically,
from VB6 code, but if I cannot make the old method work - "SELECT text
FROM syscomments" - than I'll try to execute sp_helptext
programmatically.
Still it puzzles me why "SELECT text FROM syscomments" is failing for
User Defined Functions in SQL Server 2005.
Thanks again for your help.
Dariusz Dziewialtowski.|||(dariusz.dziewialtowski@.gmail.com) writes:
> Still it puzzles me why "SELECT text FROM syscomments" is failing for
> User Defined Functions in SQL Server 2005.
In general it isn't:
CREATE FUNCTION myudf() RETURNS int AS
BEGIN
RETURN (99)
END
go
SELECT text FROM syscomments WHERE id = object_id('myudf')
go
DROP FUNCTION myudf
works for me.
However, in your original post you had fn_dblog, and that function
has moved and no longer lives in master, as have all other system
procedures and system UDFs. They now live in the hidden resource
database.
Also beware that SQL 2005 completely changes how metadata is stored.
The system tables from SQL 2000 are now merely compatibility views
on top of the new catalog views. The catalog views in their turn
refers to the new system tables that are accessible outside system code.
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|||Hello Erland,
Thank you for your help - I was not aware about the changes in metadata
in SQL Server 2005. Thanks a lot for explaining them to me.
Dariusz Dziewialtowski.