0

I have created multiple SQL DB Maintenance scripts which I am required to run in a defined order. I have 2 scripts. I want to run the 2nd script, only on successful execution of 1st script. The scripts contain queries that creates tables, stored procedures, SQL jobs etc.

Please suggest an optimal way of achieving this. I am using MS SQL Server 2012. I am trying to implement it without using an SQL job.

Improve this question
1

4 Answers 4

Reset to default
1

I'm sure I'm stating the obvious, and it's probably because I'm not fully understand what you meant by "executed successfully", but if you meant no SQL error while running: The optimal way to achieve it is to create a job for your scripts, then create two steps - one for the first script and for the second. Once both steps are there, you go to the advanced options of step 1 and set it up to your needs.

Screenshot

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That would be indeed obvious. However, SQL job would be my last resort as I am trying to avoid using a job for this. Thanks for the suggestion.
I have accepted this one as the answer as it seems to be the first answer to my question. Also, I would be using this approach in my implementation. Thanks.
I understand, maybe you can simply have a 0 bit (lets call it "first_script") on a third table (let's call it "tbl_script_verifier"). You set "first_script" to 0 at the begging of the first script and you set it to 1 once you complete the first script successfully. Before you are running the second script you check whether or not "first_script" is set to 1. That should do the job.. You can even go a little fancier there and turn it into a log - set date scripts started / ended etc..
0

Can you create a SQL Server Agent Job? You could just set the steps to be Step 1: Run first script, Step 2: run second script. In the agent job setup you can decide what to when step 1 fails. Just have it not run step 2. You can also set it up to email you, skip to another step, run some other code etc... If anything the first code did failed with any error message, your second script would not run. -- If you really needed to avoid a job, you could add some if exists statements to your second script, but that will get very messy very fast

Improve this answer

1 Comment

Creating the SQL job would be my last resort as I am trying to avoid using a job for this. Thanks for the suggestion.
0

If the two scripts are in different files

Add a statement which would log into a table the completion and date .Change second script to read this first and exit,if not success

if both are in same file

ensure they are in a transaction and read @@trancount at the start of second script and exit ,if less than 1

Improve this answer

Comments

0

SQL Server 2005’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT .OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.