• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

execute a MS SQL script as a schedule SQL job

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
marrowyung
Asked:
marrowyung
  • 33
  • 25
  • 12
  • +1
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
Is that a batch file or SQL Script?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
ste5anSenior DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
then it will output a script,
How it does this? Do you have an example?
0
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So you have a script that generates another script and you want a way to save the newly generated script in the disk?
0
 
ste5anSenior DeveloperCommented:
Scope and permissions are different.
0
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
"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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
"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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
"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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Whats the update ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
so still relies on result.txt ?
If you used that file name as the output parameter (-o), then yes.
0
 
marrowyungAuthor Commented:
so no email anyway, so I have to relies on the job steps failure alert other than that, right?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@Author - In that case go ahead and create the job.
0
 
marrowyungAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
"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
 
Pawan KumarDatabase ExpertCommented:
What more you need here ?
0
 
marrowyungAuthor Commented:
what error it cause during the execution. etc..

instead of just saying "step x failed"
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server gives you all information that you need about the errors. You just need to retrieve it.
0
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What that script is doing? A restore?
0
 
marrowyungAuthor Commented:
"What that script is doing"

to clone out all permission and reapply all permission once DB restore from other DB is done .
0
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you attach Result.txt file here?
0
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Those are the output from PRINT commands, right?
0
 
marrowyungAuthor Commented:
"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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
I want to see the exact message.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
so this mean parse the exact output to output.txt ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
ok
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
marrowyung, what's happening man?
You're letting your questions being neglected.
0
 
marrowyungAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
-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
 
marrowyungAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Try

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

Errorx: HandleError Here

Completex : SELECT 'Script Completed'

Open in new window

0
 
marrowyungAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
marrowyungAuthor Commented:
noonol, I mean where can I see the error, man... application log ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, will wait and check what kind of solution you'll get more.
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
tks all anyway.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
you mean the whole command is not completed ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
marrowyungAuthor Commented:
you all good victor.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 33
  • 25
  • 12
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now