#PowershellBasics: Running a .sql script file.

5

September 16, 2021 by Kenneth Fisher

As with all programming tasks there are a number of ways to do something, this is just one that I found recently, and that I found to be particularly useful. And by useful I mean it works, and it’s easy.

    $myData = invoke-sqlcmd -InputFile $MyScript -serverinstance $SQLInstance

invoke-sqlcmd is a super easy, super useful command that you can use to run a sql query, or as in the example above, run a .sql script file. In this particular case I only need three variables.

  • $MyScript – This is the name/path of the script file you want to run. (I said this was easy right?)
  • $SQLInstance – The instance you want to connect to.
  • $myData – This is the first result set from the script. Interestingly enough, if any subsequent result sets match the structure of the first, they are appended to the data set. In my opinion this is particularly powerful with a script file. Imagine creating a script that collects data on six different types of clients. They are all clients, and your result sets for each client are the same so all of that information ends up in $myData. In my particular case I used a script to pull data from the SQL log and pull back out the most recent logins/failed logins.

As with many of these types of commands there are also other handy parameters such as -Database, -UserName, and -Password just to name a few.

Last but not least, because I was able to pass in the instance name as a parameter I was able to throw this into a loop and hit several hundred instances to centralize some reporting.

5 thoughts on “#PowershellBasics: Running a .sql script file.

  1. Michael says:

    I also like the DBATools equivalent

    Invoke-DBAQuery

  2. […] Kenneth Fisher has a one-liner for us: […]

  3. […] my last post I talked about invoke-sqlcmd. It’s a nice easy way to run a query or a .sql file. The project I’m working on has me looping through a number of SQL Instances, grabbing some […]

  4. Roi Bailey says:

    Reblogged this on NCBI and commented:
    $bulkCopy.DestinationTableName = $OutputTable

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,580 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: