SQLCMD Mode; Run all SQL files in a directory

  • wim.buyens - Monday, September 14, 2015 7:24 AM

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

    I love it when people say things like that.... and then fail to post any code or even a link to a reference to demonstrate how.

    And, seriously... write compiled code to do what is frequently a one-off?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

    +1 for that.  Just in case you don't know about it, lookup the FORFILES command in DOS for a possible alternative.  And, yeah... OSQL was deprecated a long time ago even if this was posted 2 years ago.  As already suggested, have a look at SQLCMD.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • i do something exactly like this in PowerShell; with that, i can capture the output of the script, whether it was an info message, a  data set or an error message.
    I think that is a much better practice, with more control.

    $DirectoryToProcess = "F:\StandardTakeOver\"
    foreach ($CurrentFileName in Get-ChildItem -path $DirectoryToProcess -Filter SQL_*.sql | sort-object )

    {
    #add an Output sub directlry,and then the file name
    $out = [System.IO.Path]::Combine([System.IO.Path]::Combine($DirectoryToProcess,"Output") , $CurrentFileName.name.split(".")[0] + ".txt") ;

    #invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
    #Flags:
    # -S server\instancename
    # -E = trusted connection
    # -i input file
    # -I Enable Quoted Identifier: critical fro stored proc creations
    $CurrentFileName.name #debug/echo out the current filename so I can see what is being processed
    sqlcmd -S "(local)" -E -I -i $CurrentFileName.fullname | format-table | out-file -filePath $out

    }

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • BTW, Jason... cool article.  I know what your goal was and you nailed that.  The article is also spawning some pretty good side-bars.  Thanks for taking the time!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lowell - Friday, August 25, 2017 7:52 AM

    i do something exactly like this in PowerShell; with that, i can capture the output of the script, whether it was an info message, a  data set or an error message.
    I think that is a much better practice, with more control.

    $DirectoryToProcess = "F:\StandardTakeOver\"
    foreach ($CurrentFileName in Get-ChildItem -path $DirectoryToProcess -Filter SQL_*.sql | sort-object )

    {
    #add an Output sub directlry,and then the file name
    $out = [System.IO.Path]::Combine([System.IO.Path]::Combine($DirectoryToProcess,"Output") , $CurrentFileName.name.split(".")[0] + ".txt") ;

    #invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
    #Flags:
    # -S server\instancename
    # -E = trusted connection
    # -i input file
    # -I Enable Quoted Identifier: critical fro stored proc creations
    $CurrentFileName.name #debug/echo out the current filename so I can see what is being processed
    sqlcmd -S "(local)" -E -I -i $CurrentFileName.fullname | format-table | out-file -filePath $out

    }

    Cool.  And it's commented, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I once wrote a Powershell script to release multiple files into several environments and log messages and errors.  I requires creating some text files with run-time arguments (so you don't have to modifiy the script, itself).  We used this script for all releases at my old job.  To make an entry invisible to the script, precede it with a colon (:).  
    For folder run order, use entries like this in DBFolders.txt:
    1 - DatabaseOne
    2 - DatabaseTwo
    3 - DatabaseThree

    @JeffModen, you could probably clean it up to streamline it.

  • Jeff Moden - Friday, August 25, 2017 9:55 AM

    BTW, Jason... cool article.  I know what your goal was and you nailed that.  The article is also spawning some pretty good side-bars.  Thanks for taking the time!

    Thanks Jeff.

    The larger picture and real power comes in by integrating this into SSDT's Pre-Deploy phase. By using a specific folder structure in your project and having the scripts prefixed with TFS task numbers (which ensures they get ran in the proper order) the final deploy script that gets generated will inject your custom code into that script. You can also extend this into the Post-Deploy phase as well. All in all, this resolves the one major drawback of SSDT. Data Movement is not handled, but with this approach it's pretty sweet. I just haven't gotten around to writing it up.

    ______________________________________________________________________

    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
  • Also, my apologies, Jason!  The first line of my previous was supposed to resemble, "Great article, I gave you 5 orbs!"  Again, sorry.

  • John Hick-456673 - Friday, August 25, 2017 10:17 AM

    I once wrote a Powershell script to release multiple files into several environments and log messages and errors.  I requires creating some text files with run-time arguments (so you don't have to modifiy the script, itself).  We used this script for all releases at my old job.  To make an entry invisible to the script, precede it with a colon (:).  
    For folder run order, use entries like this in DBFolders.txt:
    1 - DatabaseOne
    2 - DatabaseTwo
    3 - DatabaseThree

    @JeffModen, you could probably clean it up to streamline it.

    thanks to John Hick for this script

Viewing 9 posts - 16 through 24 (of 24 total)

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