August 19, 2021 at 3:09 pm
hi
i want to run a SQL reindexing query from a batch file.
i have the query saved on the c: drive. i have put this query into my batch file
sqlcmd -S myservername -i c:\BackupScripts\SQLReindex.sql
it seems to run ok when i type it into a CMD window (see attached)
is it possible to get a confirmation that it ran and if there were any errors etc. i want to create a task in task scheduler to reindex the DataBase each week and get confirmation.
i'm new to SQL queries so go easy :p (also hopefully i've posted in the correct forum?)
thanks
August 19, 2021 at 3:34 pm
Why are you using a file to run a batch from? Why not a stored procedure?
Also, any chance of you posting your SQLReIndex.sql file? I ask because it appears to have done a REORGANIZE. For more than 2 decades, people have been using a set of "Best Practices" for index maintenance that are actually the very worst practices and almost everyone has mistaken what REORGANIZE actually does (it actually PERPETUATES fragmentation).
And, no... I'm not some hack that's trying to troll some new person. Google me if you want.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2021 at 4:38 pm
I agree with Jeff here on not using a bat file for this UNLESS you are on SQL Express. If you are on SQL Standard or higher, then I would use a SQL Agent Job to run the query.
Now, I'm going to assume you are on SQL Express which is why you using the bat file. Now, if you are looking to use windows task scheduler for this, you can probably strip out the bat file portion of it as you can run commands with arguments straight from task scheduler. The advantage to this is that task scheduler automatically captures the error code - zero for success, non-zero for errors. So you can monitor if the last run was a success or failure.
Now, if you are wanting to use the bat file as there are other scripts you are running, you will likely want to capture the error code provided after the calling application exits and check it for success (0) or failure (non-0). Now, where that gets tricky is if your .sql file is running a lot of different commands as I am not sure what value sqlcmd will return if one of the queries in the middle fails but the final one succeeds.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 19, 2021 at 4:46 pm
Now, I'm going to assume you are on SQL Express which is why you using the bat file. Now, if you are looking to use windows task scheduler for this, you can probably strip out the bat file portion of it as you can run commands with arguments straight from task scheduler. The advantage to this is that task scheduler automatically captures the error code - zero for success, non-zero for errors. So you can monitor if the last run was a success or failure.
An issue with using sqlcmd for this is it won't return a failure if the SQL fails, only if there's a major error like cannot connect to server. If you have to use batch for this it might be better to use powershell so you can get an error if the SQL fails.
August 19, 2021 at 6:36 pm
An issue with using sqlcmd for this is it won't return a failure if the SQL fails, only if there's a major error like cannot connect to server. If you have to use batch for this it might be better to use powershell so you can get an error if the SQL fails.
Yes and no. By default SQLCMD does not return an error, but you can use the -b option to have an errorcode to be produced.
As for T-SQL job step vs. running the .Bat file, that discussion is a little more complex. It could be that NorthernSky needs to run the same set of commands on more than one instance, in that case the .BAT file is a better idea.
I also like to point out that if you have an application that wants to run a stored procedure in an Agent job, a CmdExec job with a proxy can absolutely be the best alternative, I discuss here: https://www.sommarskog.se/perm-hijack.html#agentjobs.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2021 at 7:28 pm
As for T-SQL job step vs. running the .Bat file, that discussion is a little more complex. It could be that NorthernSky needs to run the same set of commands on more than one instance, in that case the .BAT file is a better idea.
Heh... that's what a whole lot of folks said for backups when PoSh came out. Then, people found out the true cost in exploding log files when the central system was taken down for maintenance, lost connection, or had a problem.
I might use PoSh to push a scheduled job to each machine but I probably wouldn't use it to run something important on each machine as a "replacement" for the Agent. That even holds true with EXPRESS. As much as I hate the Windows Schedule, the more I hate centrally controlled jobs.
And, yes... As with all else, "It Depends" and there can be exceptions to anything.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2021 at 8:40 am
hey folks,
many thanks for responses, need to get a moment to digest them 🙂
but yes i should have said it's for customers i have with SQL Express
so i can't create a maintenance plan. i just have the one SQL script to run.
so can i run that straight in Task scheduler?
August 20, 2021 at 2:21 pm
Task scheduler it is, then. Keep in mind that Task Scheduler runs under Local System which typically does not have access to SQL Server, so you will have to arrange for that, one way or another. Either give Local System permission to SQL Server, or run the task under a user which has access.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 27, 2021 at 12:21 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply