Executing a large number of SQL scripts

Perl! I love perl. I hate the batch file syntax - I'd always resort to perl. There was a day when any SQL Server I managed immediately got ActivePerl installed on it :)

Incidentally, you can also do some creative things like this with SSIS with the For and For Each containers. SSIS isn't just about moving data around...

Nice reminder Linchi that we don't necessarily need a complex or static solution to what seems on the service to be a complicated or time-consuming task.

Nice meeting you BTW!

You set me up...  It's even more trivial than the perl example.  This is not through SQLPS, but through the normal powershell, using the snapins from the SQL Server 2008 install (we need invoke-sqlcmd).

sl = 'N:\Apps\Release\PRS\PR12345'

gci -include *.sql -recurse | % {invoke-sqlcmd -server "SQLNYC01" -inputfile $_.fullname}

Save that as a ps1 file...done.

Ok...first, I didn't HAVE to use invoke-sqlcmd, I could've just as easily used the executable, just as you have.  I don't really think it's a problem to use cmdlets.  You like to use PERL modules that were written by others, I assume.  Same thing, in my humble opinion.  Second, I don't believe that it's really a contest...but if it were, you're going to win, no matter what.  It's your blog. :)

Oh, you guys are quibbling over details :) The overall message here is - don't build a yacht when a raft works just fine :)

Linchi and Don,

I have “kicked the tires” a bit with PowerShell and can read Perl to a limited degree, but I am no expert in either.  However, I have observed a similarity between PowerShell and SQL – they are both set-based languages / environments that can also process data using explicit iteration (but maybe not as efficiently in all cases) – recordsets for SQL and .NET object collections for PowerShell.

Solving a problem in set-based form (implicit iteration, without using explicit iteration – loops, etc.) often results in a more efficient (run time and computer resource perspective – memory, CPU, etc.) and more elegant (compact – fewer lines of code to develop, debug, maintain, etc.) solution.  Explicitly iterative solutions are also possible in the respective scripting languages, but may not be as efficient.  Efficiencies are more likely to come when the iteration is pushed to the lowest levels by use of higher-level set-based operators.  There are exceptions to this set-based efficiency advantage, as many others have noted in past discussions on “the evils of cursors” in T-SQL.  It also assumes a mature, well-optimized set of higher-level set-based operators.

Perl has been around longer, but PowerShell seems to have some compelling uses.  A solution that can be done in a few lines by piping results between basic cmdlets and little or no other “scripting” (declared variables, explicit iteration, etc.) can be a thing to behold, once you get to know the cmdlets and their options.  However, examples I have seen of heavier PowerShell script usage can be cumbersome to read and understand.  Perhaps this is one of the issues that Linchi is exploring in this comparison.  My lack of deeper Perl knowledge puts me at the same disadvantage with some Perl examples.  I believe proficiency in either environment takes time, effort, much practice, and a good bit of trial and error in the learning process.

I still use a lot of VBScript and BAT scripts in work situations, because I am more familiar and proficient with them (recalling how much effort it took me to get from zero to here), and their run-time environments are pre-installed on any system I use.  Some companies have “policies” against using “other” scripting environments (for “security” reasons, staff training and knowledge, etc.).  I’m not saying those reasons are right, but they may exist and have to be addressed.

Personally, I hope to learn more about PowerShell and possibly Perl also, to have more options available in my tool box for a given situation.  While it might be nice to settle on one “best” language / environment, I believe that knowledge of a few useful tools in this area (and knowing when to use which one) can be an advantage.

It is a paradigm shift to think of solutions in set-based form (versus explicit iteration), much as it was a paradigm shift for event-based programming when VB first came out.  Many developers still struggle with the need to use explicit loops to process data within T-SQL, when set-based solutions may be available and more effective.  The developer thinking process is largely “item / row at a time” for most other programming aspects, and continues to use that same approach in areas where better solutions may exist.  I don’t always succeed here myself, but I continue to strive for set-based solutions in the work I do where possible, learning from past solutions and better examples along the way.  I recall similar struggles when learning APL in a “survey of programming languages” college class – all set-based operators with special characters for each operator – compact, “elegant”, but very cryptic.

To put it in Joe Celko terms: “Thinking in Sets”

Your thoughts and observations?

Let the debate continue!

Scott R.

Mnn, I must suck to use 3rd party tools like RedGate SQL Multi Script, or xSQL Script Executor (add Folder)

I have .bat file using foreach, but that doesn't work with UNC

I have VBScript too..

Learning the handle of PowerShell every day

Gotta go with Powershell

gci N:\Apps\Release\PRS\PR12345 -filter '*.sql' -recurse |% {sqlcmd -S SQLNYC01 -E -e -i $_.FullName}

No just one physical line of code, but one logical code statement.  Not all of us have the SQL 2008 PowerShell plugins installed.

Linchi,

Not trying to change your opinion or drink any Kool-Aid! :-)

Sets of things to process are not limited to RDBMS analogies – tables, rows, etc.  Common hierarchies of collections / sets from non-RDBMS data sources are exposed through a growing set of PowerShell data providers (file systems, registry, WMI, Exchange, etc.) as well as SQL Server metadata and configuration info.

Reminds me of the Microsoft LogParser tool – initially awkward to learn how to use, but very productive once you get the hang of it.

Like most tools in any toolbox, one size rarely fits all.  Find the hammer(s) and nails that work for you.  I will continue to use VBScript and BAT scripts, and learn more about PowerShell and Perl to use as appropriate.

Best wishes on your comparisons!

Scott R.

With the 'p' bit...

gci N:\Apps\Release\PRS\PR12345 -filter 'p*.sql' -recurse |% {Invoke-Sqlcmd -S SQLNYC01 -E -e -i $_.FullName}

And I'll raise you a "files that have appeared since yesterday"

Get-ChildItem N:\Apps\Release\PRS\PR12345 -filter 'p*.sql' -rec |

Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)} |

% {Invoke-Sqlcmd -S SQLNYC01 -E -e -i $_.FullName}

(And I've removed the use of aliases, just to be clearer...)

Rob

Rob;

Here's the Perl:

use File::Find;

find sub {unshift @abc, $File::Find::name if -M $File::Find::name <= 1;}, 'N:\Apps\Release\PRS\PR12345';

print map {"sqlcmd.exe -SNYCSQL01 -E -w250 -i$_"} grep(/^(.+\\p\_[^\\]+\.sql)\s*$/i, @file);

Oops! that @file should be @abc from the previous line.

But don't get me wrong, Rob. Since they've put so much effort into all these cmdlets, they've provided a lot of short-cuts into individual systems (SQL Server, etc) that are not available elsewhere, and I'll use these short-cuts by wrapping Perl around PowerShell. Perl is great in stiching disparate systems together, and PowerShell is good at providing the system-specific short-cuts. That could be a nice marriage.

I have long used a solution that combines MS Access and Word.  I use MS Access to store a list of file names (in order and by categories).  It also includes features like description columns and ability to change what folder to find the code in.  Then there is a button on the Access form which uses automation to build a Master Document in Word.  

Of course, there are cons to this approach, but I love it (and have been using this approach without problem and with lots of joy for many years) because I get two things at once: 1) the ability to work with all my code in separate files, 2) a fast way not only to be able to compile all those files for running them when I need to re-create a database, but also a single place to do global search and replace across all my code (in the master document - which points back to the original files).  Want to change a column name? No problem, you will catch every instance of that column name used in code and be able to update in one step.  

Another reason I like this approach is that the Access .mdb gives me a very clear tree-view of my code.  I think it is good documentation for me and any future person who replaces me.

Just sharing.