September 13, 2015 at 10:10 pm
Comments posted to this topic are about the item SQLCMD Mode; Run all SQL files in a directory
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 13, 2015 at 10:13 pm
Thank you Jason for this write up, very good reference indeed!
😎
September 14, 2015 at 7:24 am
u can also use CLR and run all the code from T-SQL
September 14, 2015 at 7:33 am
Hello!
Thx for the way how to call several script files!
My question is: How can I or what is the best way to catch an error in one of the script files and how can I change the execution in case of error?
Best Regards
Andreas
September 14, 2015 at 7:36 am
Nice article, thanks.
September 14, 2015 at 7:59 am
You know you can skip all of that and just run this following from the command line:
for %f in (*.sql) do echo osql -S servername -E -i %f
September 14, 2015 at 8:38 am
But that's not in SSMS. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 14, 2015 at 3:47 pm
andreas.brandsteidl (9/14/2015)
Hello!Thx for the way how to call several script files!
My question is: How can I or what is the best way to catch an error in one of the script files and how can I change the execution in case of error?
Best Regards
Andreas
You'll have to do some tricky work as described HERE.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 14, 2015 at 3:48 pm
wmyers01+SSC (9/14/2015)
You know you can skip all of that and just run this following from the command line:for %f in (*.sql) do echo osql -S servername -E -i %f
Sure, but that's not SSMS 🙂 and this article is more of an introductory into SQLCMD to show people what you can do and provide ideas for others.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 15, 2015 at 10:16 am
Do each of the sql files execute fully before the next one starts to run?
September 15, 2015 at 10:40 am
bgrossnickle (9/15/2015)
Do each of the sql files execute fully before the next one starts to run?
Yes.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 9, 2015 at 10:53 am
Great script, really useful in our shop.
I'm not sure if this is environmental or will always occur, but this step (!!NOTEPAD $(WorkDirectory)$(RunFile)) keeps the query executing until notepad is closed.
Don Simpson
October 9, 2015 at 3:13 pm
DonlSimpson (10/9/2015)
Great script, really useful in our shop.I'm not sure if this is environmental or will always occur, but this step (!!NOTEPAD $(WorkDirectory)$(RunFile)) keeps the query executing until notepad is closed.
Don, I'm glad it helps!
From what I can guess, this is by design (SQL and SQLCMD). Until notepad is closed, sql "thinks" that line of code hasn't completed yet. I'm not sure how to release focus programatically or simply do a call without setting focus in the first place.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 9, 2015 at 3:16 pm
andreas.brandsteidl (9/14/2015)
Hello!Thx for the way how to call several script files!
My question is: How can I or what is the best way to catch an error in one of the script files and how can I change the execution in case of error?
Best Regards
Andreas
I'm not sure if TRY/CATCH will work or not, but it's worth experimenting with.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 25, 2017 at 1:26 am
wmyers01+SSC - Monday, September 14, 2015 7:59 AMYou know you can skip all of that and just run this following from the command line:for %f in (*.sql) do echo osql -S servername -E -i %f
Using Sql 2k5 or higher, instead of osql, use sqlcmd. Don't be scared, it uses same arguments but offers more. But basically I totally agree with you. One should always choose the tool right for the job.
Also, what I really like is that I know can easily save detailed query results per executed sql-file. And adding the '-b' option, quit when there's an error.
My 'runallsql.cmd' looks like this:
@echo off
set SERVER=MYSERVER
for %%f in (*.sql) do (
echo %%f
sqlcmd -S %SERVER% -E -b -Q %%f -o %%~nf.log
if errorlevel 1 goto error
)
goto ok
:error
echo ERROR EXECUTING!
pause
:ok
n.b. I've tried to cover above functionality in powershell (without using sqlcmd), but saving the output prooved to be tricky.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply