Solved

execute a MS SQL script as a schedule SQL job

Posted on 2016-09-27
72
99 Views
Last Modified: 2016-11-22
hi,

right now I have a procedure that we need to execute a script against a DB, then it will output a script, then we need to run that output script again the same DB,

in SQL job, what is the syntax we can run a saved script by a SQL job.
0
Comment
Question by:marrowyung
  • 33
  • 25
  • 12
  • +1
72 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41817611
Is that a batch file or SQL Script?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41817614
Try

Sample syntax will be..!!

---

USE msdb;   --Because all jobs will hanl
GO  

EXEC sp_add_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_name = N'Set database to read only',  
    @subsystem = N'TSQL',  
    @command = N'ALTER DATABASE SALES SET READ_ONLY',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
 
--

Open in new window


Enjoy !

Reference: https://msdn.microsoft.com/en-us/library/ms187358.aspx
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41817615
@Author - Please note that this is just an example via Microsoft site. If you can provide more details I shall write your SQL Job.

Thanks!
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 125 total points
ID: 41817629
It sounds like dynamic SQL. Thus caveat, it's a dangerous weapon and has many pitfalls.

But when I need to guess:

DECLARE @Script NVARCHAR(MAX);
DECLARE @Scripts TABLE ( Script NVARCHAR(MAX) );
INSERT  INTO @Scripts
        EXECUTE yourSprocGeneratingTheScript;
SELECT  @Script = Script
FROM    @Scripts;
EXECUTE (@Script);

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41817652
then it will output a script,
How it does this? Do you have an example?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819114
Pawan Kumar Khowal,

"Is that a batch file or SQL Script?"

it is an separate SQL script, I want to plug the script for the SQL job to run, that script will have an output separate .sql file, then I will use the same way to execute that separated output .sql file .

Victor,

like run a.sql and a.sql will create b.sql, then we need to run b.sql against the same DB.

ste5an,

'you are saying dynamic SQL is dangerous, right? why ?

how can I plug in the full path together with script name to your code? assuming the path can script name can be change anytime.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41819257
So you have a script that generates another script and you want a way to save the newly generated script in the disk?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41819269
Scope and permissions are different.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819309
Vitor,

yes, we have another DBA tried to improve the permission backup script a bit.

he build a A.sql which generate B.sql which is saved on DISK, then I planned to create another steps to run B.sql after some procedure executed.

once I know how to execute A.sql from within SQL job (I am not going to copy the whole script to the SQL job steps as this is what I don't know, i knew I can do it in this way. A.sql can be very long if the DBA change it again and again), then I can do the same thing to execute B.sql from within SQL steps.

again B.SQL is generated by A.SQL
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41819322
You can have a SQL Job to run a .sql script. Just perform a sqlcmd utility call:
sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819329
yeah, this should be sth work for me, I can't recall that command, what is the steps type ?

should not be T-sQL, right?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41819343
SQLCMD is a command prompt utility that can run T-SQL queries or scripts so you need to create a job step of type "Operating system" to run SQLCMD utility.
JobStepRunSQLCMD.PNG
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819486
yeah, it is working for my case now...

but how can I add exception/error handling in this case ? by only checking the existence of B.SQL ?

can we do it in coding level ?

in case the A.sql can't be finish then it email me with some meaningful text.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41819512
SQLCMD has more parameters. You can send the output to a file so you can check for errors:
sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41821112
"SQLCMD has more parameters."

so this means do not use CMDEXEC type ?

how about one thing if I can't generate error message, which is I check if B.sql exists first, then execute b.sql, then delete b.sql.

if b.sql doesn't exist already means executing A.sql has problem, right ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41821254
so this means do not use CMDEXEC type ?
I didn't say that. I said that you can add more parameters to the command.

how about one thing if I can't generate error message, which is I check if B.sql exists first, then execute b.sql, then delete b.sql.
You can check if the job step run successfully or not and perform a task accordling.
JobStepProperties.PNG
if b.sql doesn't exist already means executing A.sql has problem, right ?
Sorry, didn't understand your question.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41821268
"I didn't say that. I said that you can add more parameters to the command"

sorry, please give me an example.

"You can check if the job step run successfully or not and perform a task accordling."

I knew, this is what I am trying to do as this is one of the FEW thing we can do.

"Sorry, didn't understand your question."

I create a step to run A.SQL and do sth else, after that run a step to run B.sql, how can I make sure that a.sql run successfully?

as A.sql generate B.sql, then how can I make steps alert show me a.sql can't generate b.sql ?

steps to run b.sql can see the path and see if that path has that files, right?

so we need to include extra steps to delete b.sql once b.sql has been run, right?  how can I do it using SQL job ?

so each time I run that job I expect A.sql generate a new b.sql, so do you understand what I mean now ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41821277
sorry, please give me an example.
Man, already did:https://www.experts-exchange.com/questions/28972616/execute-a-MS-SQL-script-as-a-schedule-SQL-job.html#a41819512

so each time I run that job I expect A.sql generate a new b.sql, so do you understand what I mean now ?
Yes but you're not using properly the job step properties. Can you post a screenshot of what you have now?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41821400
"Yes but you're not using properly the job step properties. Can you post a screenshot of what you have now?"

I am now just designing it as I just get a new script and step for that, i just want to get more on exeception handling
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41821444
When you have it implemented and if isn't working come back with what you have and I'll be happy to help you with what is missing.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41826065
AHAH, nice , sorry just back from holiday,

sorry you are referring to this command:

qlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt

so still relies on result.txt ?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41826069
Whats the update ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41826115
so still relies on result.txt ?
If you used that file name as the output parameter (-o), then yes.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41827412
so no email anyway, so I have to relies on the job steps failure alert other than that, right?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41827415
Yes, In that case you have to manually check the status of this job on daily basis.

A best method would be in case of any failure , get an email in your inbox.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41827454
Pawan Kumar Khowal,

yes, this is the final thing I can think about instead of reading the log as email is more direct as what if I am on the street ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41827525
so no email anyway,
Where the email requirement comes from?

so I have to relies on the job steps failure alert other than that, right?
Ofc. We are talking about a SQL job, right?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41827529
@Author - In that case go ahead and create the job.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41829617
Victor:

"Where the email requirement comes from?
"
sorry , I just think it make more send, I might not in front of my PC all the time, email is the only thing let me know what is the status.


"Ofc. We are talking about a SQL job, right?

sorry ,what is ofc?,....... :):)

I want to integrate the script inside the job, this is the way to automate sth, right?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41829626
Yes one can create a SQL job and if anything fails while processing the SQL job, SQL will send you a notification ( an email )
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41829688
I might not in front of my PC all the time, email is the only thing let me know what is the status.
Then use the job feature to notify in case of failure.

I want to integrate the script inside the job, this is the way to automate sth, right?
Right. In SQL Server automation usually means SQL job ;)

Ofc = Of course :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831454
"Then use the job feature to notify in case of failure."

yes ,this is the way twe are using but I am thinking more on code level instead of relies on the job step alert, it will just said failed but not saying what failed and what is the error.

"Right. In SQL Server automation usually means SQL job ;)"

I need more control on error message which means more to me.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831456
What more you need here ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41831459
what error it cause during the execution. etc..

instead of just saying "step x failed"
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41831464
This you can get using try catch block in your script.

You can catch it in the catch block and Use Raise Error or throw.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41831508
SQL Server gives you all information that you need about the errors. You just need to retrieve it.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:marrowyung
ID: 41839615
hi,

anyway to delete a .sql file on local hdd using SQL job ?

victor,

can this statement:

sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql

overwrite the existing C:\ScriptName.sql if it does exists ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839624
anyway to delete a .sql file on local hdd using SQL job ?
Yes. Create a new job step with the Operating System type (as in https://www.experts-exchange.com/questions/28972616/execute-a-MS-SQL-script-as-a-schedule-SQL-job.html#a41819343) and then type the delete command.


overwrite the existing C:\ScriptName.sql if it does exists ?
-i is the input script so it won't write the file but read from it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41839817
one thing, I get an requirement when I execute that script, all running session must be gone, anyway other than restart the SQL server instance can do it automatically ? not single user mode I prefer.

all login other than the login which run that script must be gone.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41839829
What that script is doing? A restore?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41841310
"What that script is doing"

to clone out all permission and reapply all permission once DB restore from other DB is done .
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41841346
victor,

I found this:

sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt

the final output result.txt is stored in C:\Windows\System32, anyway for me to store it in the path I want ?
just add the path in front of Result.txt?

I am testing this.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41841443
I found this:
Where did you find it. In one of my above comments? :)


just add the path in front of Result.txt?
Yes. C:\Windows\System32 is the path where sqlcmd is stored so it is using it as default location.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41841522
Victor you post this one:

"SQLCMD has more parameters. You can send the output to a file so you can check for errors:
sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt"
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41841524
I also found when running this:

sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt

the text show in the Result.txt is not the same as the one shown in the message console of SSMS, any reason ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41841667
Can you attach Result.txt file here?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41841712
result.txt:

Changed database context to 'xxxxxx'.
Schema Authorizations have been updated: 2016-10-13T00:36:11.963
User granted permissions have been revoked: 2016-10-13T00:36:12.107
Role Memberships have been dropped: 2016-10-13T00:36:16.260
Users have been dropped: 2016-10-13T00:36:37.810
Users have been Created: 2016-10-13T00:36:38.620
Roles have been dropped: 2016-10-13T00:36:53.153
Roles have been Created: 2016-10-13T00:36:53.607
Permissions have been set: 2016-10-13T02:07:41.883
Role Memberships have been added: 2016-10-13T02:07:46.087
Schema Authorizations have been updated: 2016-10-13T02:07:46.993
Orphan users have been updated: 2016-10-13T02:07:47.057
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41841739
Those are the output from PRINT commands, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41843016
"Those are the output from PRINT commands, right?"

there are not print commands in the script I ran.

this is the one from SSMS message box:

Schema Authorizations have been updated: 2016-10-13T06:12:57.993
Msg 50000, Level 1, State 1
User granted permissions have been revoked: 2016-10-13T06:12:58.010
Msg 50000, Level 1, State 1
Role Memberships have been dropped: 2016-10-13T06:12:58.243
Msg 50000, Level 1, State 1
Users have been dropped: 2016-10-13T06:13:18.323
Msg 50000, Level 1, State 1
Users have been Created: 2016-10-13T06:13:18.387
Msg 50000, Level 1, State 1
Roles have been dropped: 2016-10-13T06:13:30.980
Msg 50000, Level 1, State 1
Roles have been Created: 2016-10-13T06:13:31.137
Msg 50000, Level 1, State 1
Permissions have been set: 2016-10-13T06:22:01.980
Msg 50000, Level 1, State 1
Role Memberships have been added: 2016-10-13T06:22:04.027
Msg 50000, Level 1, State 1
Schema Authorizations have been updated: 2016-10-13T06:22:04.357
Msg 50000, Level 1, State 1
Orphan users have been updated: 2016-10-13T06:22:04.357
Msg 50000, Level 1, State 1
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41843166
Msg 50000, Level 1, State 1
Looks like instead of PRINT they used RAISEERROR() function.
So you're expecting to see those annoying Msg 50000, Level 1, State 1 lines?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41843245
I want to see the exact message.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41843252
I'm not sure that's possible and can't understand your reason but if it's a requirement try this:
sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt > output.txt

Open in new window

and check the output.txt file.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41843255
so this mean parse the exact output to output.txt ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41843258
This returns the messages to output.txt file instead to screen. I'm not sure if this gives what you want but can't see any other option. You should give it a try to verify it by yourself.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41843363
ok
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41863684
marrowyung, what's happening man?
You're letting your questions being neglected.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41876537
ahhah, in US super jet lag, can't sleep at all for 12 days ! what will I look like man ? I don't even have much time to read all my company's email at all.

everyday just tried to get as much sleep as possible.

"Looks like instead of PRINT they used RAISEERROR() function."

yeah, they are doing this, you are great man..

Pawan Kumar Khowal,

"You can catch it in the catch block and Use Raise Error or throw."

but by using SQLCMD command instead of a SQL script, I can't do it, right?
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
ID: 41876582
-b option in sqlcmd. <<http://msdn.microsoft.com/en-us/library/ms162773.aspx>>

-b returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0.

Read more at the URL mentioned.

Hope it helps !!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41883212
Pawan,

so where can I supposed to see the error message/output of it,  I will only run that as a scheduled SQL job.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41883241
Try

sqlcmd -b <Script>
    
IF ERRORLEVEL 1 GOTO Errorx;

Errorx: HandleError Here

Completex : SELECT 'Script Completed'

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 41887312
hi,

you mean in the SQL jobs, for the type Operation system (CmdExec), I do that?

"Completex : SELECT 'Script Completed'"

what is this suppose to output ?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41887365
sqlcmd -b <Script>
    
IF ERRORLEVEL 1 GOTO Errorx;

Errorx: PRINT 'Error'
Completex : PRINT 'Script Completed'

Open in new window


Create a stored procedure, put you code in there and call the procedure using SQL Job.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41892625
noonol, I mean where can I see the error, man... application log ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41892648
noonol, I mean where can I see the error, man... application log ?
I'm not sure why are you went to this path when I already mentioned about the output.txt file that you can provide in the output parameter. And that was more than a month ago!
Do you still suffering from jet lag? :o)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41895299
Victor, no jetlag man...

if I said that, this mean I am not sure yet and I am waiting for an answer man. any better method will be consider and I am using your right now.

which process just happen yesterday night.

I use the output this time as i compare the result of last time, it is a bit different, so I contact the original developer.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41895882
Ok, will wait and check what kind of solution you'll get more.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41897153
basically at this moment I am still using this:

sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql

Open in new window


but I just need more robust solution if I can, I am quite happy on this however.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41897154
tks all anyway.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41897164
basically at this moment I am still using this:
This is not complete if you compare it with the solution you choose:
sqlcmd -S ServerName\instanceName -E -i C:\ScriptName.sql -o Result.txt > output.txt
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41897167
you mean the whole command is not completed ?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41897176
you mean the whole command is not completed ?
Well, why you choose my comment as solution if isn't what you're actually using? Isn't the output that you wanted to have?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41898590
you all good victor.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now