execute a MS SQL script as a schedule SQL job

Is that a batch file or SQL Script?

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

Select allOpen in new window

Enjoy !

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

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

then it will output a script,

How it does this? Do you have an example?

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.

So you have a script that generates another script and you want a way to save the newly generated script in the disk?

Scope and permissions are different.

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

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

Select allOpen in new window

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?

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.

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.

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

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

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.

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

Sorry, didn't understand your question.

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

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?anchorAnswerId=41819512#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?

"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

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.

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 ?

Whats the update ?

so still relies on result.txt ?

If you used that file name as the output parameter (-o), then yes.

so no email anyway, so I have to relies on the job steps failure alert other than that, right?

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.

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 ?

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?

@Author - In that case go ahead and create the job.

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?

Yes one can create a SQL job and if anything fails while processing the SQL job, SQL will send you a notification ( an email )

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

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

What more you need here ?

what error it cause during the execution. etc..

instead of just saying "step x failed"

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.

SQL Server gives you all information that you need about the errors. You just need to retrieve it.

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 ?

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?anchorAnswerId=41819343#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.

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.

What that script is doing? A restore?

"What that script is doing"

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

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.

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.

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"

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 ?

Can you attach Result.txt file here?

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

Those are the output from PRINT commands, right?

"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

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?

I want to see the exact message.

so this mean parse the exact output to output.txt ?

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.

ok

marrowyung, what's happening man?
You're letting your questions being neglected.

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?

Pawan,

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

Try

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

Errorx: HandleError Here

Completex : SELECT 'Script Completed'

Select allOpen in new window

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 ?

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

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

Select allOpen in new window

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

noonol, I mean where can I see the error, man... application log ?

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)

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.

Ok, will wait and check what kind of solution you'll get more.

basically at this moment I am still using this:

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

Select allOpen in new window

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

tks all anyway.

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

you mean the whole command is not completed ?

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?

you all good victor.