SQLCMD Mode; Run all SQL files in a directory

  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Eirikur Eiriksson

    SSC Guru

    Points: 182369

    Thank you Jason for this write up, very good reference indeed!

    😎

  • wim.buyens

    Ten Centuries

    Points: 1139

    u can also use CLR and run all the code from T-SQL

  • melgoth

    Valued Member

    Points: 70

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Nice article, thanks.

  • wmyers01+SSC

    SSC Journeyman

    Points: 75

    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

  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • bgrossnickle

    Ten Centuries

    Points: 1332

    Do each of the sql files execute fully before the next one starts to run?

  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • DonlSimpson

    SSCertifiable

    Points: 6774

    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



    I'm not sure about Heisenberg.

  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    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. Selburg
  • Gjh71

    Grasshopper

    Points: 15

    wmyers01+SSC - Monday, September 14, 2015 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

    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 24 total)

You must be logged in to reply to this topic. Login to reply