Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

PowerShell Support in SQL Server 2008 with the Invoke Sqlcmd Cmdlet


By:   |   Last Updated: 2009-02-10   |   Comments (9)   |   Related Tips: More > PowerShell

Problem
In my organization we write scripts to perform all sorts of administrative functions on our many SQL Server instances as well as other server products.  We are just getting started with using PowerShell and have heard that SQL Server 2008 provides some built-in support for executing T-SQL commands from within PowerShell.  Can you provide some details and examples?

Solution
PowerShell is a relatively new interactive shell and scripting tool from Microsoft that combines the capabilities of the MS-DOS Command Prompt, batch files, Windows Scripting Host and even the popular Unix shells. 

PowerShell includes an impressive array of commands (called cmdlets) and the ability to extend the tool with your own custom cmdlets.  Probably the best feature of PowerShell is the pipeline, which allows you to execute a cmdlet and pipe its output to another cmdlet.  Since PowerShell is built on the Microsoft.NET framework, the pipeline works with .NET objects rather than just text.  For an overview of PowerShell please refer to our earlier tip Introduction to Windows PowerShell for the SQL Server DBA Part 1.

SQL Server 2008 includes a cmdlet named Invoke-Sqlcmd.  As the name suggests, this cmdlet provides capabilities similar to the SQLCMD command-line tool that has been part of SQL Server since the 2005 release.  When you install SQL Server 2008, you can access PowerShell via "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe" (assuming the default installation location). 

SQLPS is PowerShell 1.0 with the SQL Server 2008 goodies added.  Note that if you launch Windows PowerShell 1.0, the SQL Server 2008 tools will not be available; you can install them by issuing the following commands in PowerShell:

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100

For purposes of this tip, we will assume that you are using SQLPS.

PowerShell has built-in documentation.  To get the details on the Invoke-Sqlcmd cmdlet enter the following command in PowerShell:

get-help invoke-sqlcmd -detailed

The output from the above command will include a detailed description of the cmdlet, the available parameters, and a few examples.  In our examples we will use the following parameters:

  • -Database <database name> - the name of the database to use
  • -ServerInstance <computer name\instance name>  - I have SQL Server 2008 installed as a named instance on my machine; i.e. localhost\sql2008
  • -Query "<T-SQL command>" - the T-SQL command to execute

There are a number of other parameters available; check the output from the get-help command above.  We'll use Windows Authentication in our examples; you can use SQL authentication by specifying the -Username and -Password parameters.

Let's walk through some examples of what we can do with PowerShell and the Invoke-Sqlcmd cmdlet using the AdventureWorks2008 sample database which is available here.  In each of the examples, the Invoke-Sqlcmd command is shown along with the results.

I added the following stored procedure to the AdventureWorks2008 database to get the list of products where the quantity on hand is less than the reorder point:

create procedure production.get_products_to_reorder
as
begin
 set nocount on
 select p.productnumber, p.reorderpoint, q.qtyoh  
 from Production.Product p
 join (
 select productid, sum(quantity) qtyoh
 from Production.ProductInventory
 group by productid
 ) q on q.productid = p.productid
 where q.qtyoh < p.reorderpoint
end

Example 1: Execute a stored procedure and display the results

Enter the following command in PowerShell (all on one line):

invoke-sqlcmd -query "exec production.get_products_to_reorder" -database adventureworks2008 -serverinstance localhost\sql2008

 


Example 2: Execute a stored procedure and write the results to a CSV file

Enter the following command in PowerShell (all on one line):

invoke-sqlcmd -query "exec production.get_products_to_reorder" -database adventureworks2008 -serverinstance localhost\sql2008 | export-csv -path c:\pstest\reorder.csv

The above command displays no output; the results from the stored procedure are written to a CSV file.  Note the use of the pipe ( | ) character in the above command.  This allows us to take the output of one cmdlet and "pipe" it to the next cmdlet.  The Export-CSV cmdlet takes whatever you give it, puts commas between each column, and writes it out to the file specified by the path parameter.

To view the contents of the CSV file, use the following command in PowerShell:

get-content c:\pstest\reorder.csv

 

Note the first line in the CSV file.  It's the .NET type for each row in the file.  Add the notypeinformation parameter to the command to suppress it; e.g.:

invoke-sqlcmd -query "exec production.get_products_to_reorder" -database adventureworks2008 -serverinstance localhost\sql2008 | export-csv -notypeinformation -path c:\pstest\reorder.csv

This example is one that you might typically do with a SQL Server Integration Services (SSIS)  package.  While I wouldn't recommend you abandon SSIS, there are some tasks that are so simple that you could certainly use PowerShell in lieu of SSIS.


Example 3: Use SQL Server navigation

PowerShell has a cmdlet called Set-Location that navigates the file system, registry, etc.  For instance to change to a particular directory in the file system you can use the command:

set-location c:\pstest

You can also use Set-Location to navigate in the registry; e.g.:

set-location hklm:\software\idera

You can display the contents of a location with this command:

get-childitem

 

To see the complete list of providers run the following command in PowerShell:

get-psdrive

SQL Server 2008 adds a provider to allow the Set-Location cmdlet to navigate the SQL Server object hierarchy.  Think of what you see in the SQL Server Management Studio Object Explorer.  You can navigate to the AdventureWorks2008 database then run the Invoke-Sqlcmd cmdlet without specifying the -Database and -ServerInstance parameters.  For example:

set-location sqlserver:\sql\localhost\sql2008\databases\adventureworks2008

The initial sqlserver:\sql\ is required; localhost\sql2008 is the server \ instance of my SQL Server 2008 database; \databases\adventureworks2008 navigates to the  AdventureWorks2008 database.

Run the same command as example 1 above (without the -Database and -ServerInstance parameters):

invoke-sqlcmd -query "exec production.get_products_to_reorder"

 


Example 4: Execute a PowerShell command in a SQL Agent job step

SQL Server 2008 adds PowerShell as a new SQL Agent job step type.  As an example we will simply execute our example 2 above in a SQL Agent job step:

Note the Run as PowerShell.  This is not a built-in feature.  I created a SQL Server Agent proxy for the PowerShell subsystem and named the proxy PowerShell.  The proxy allows you to specify a credential to be used to run the job step.  The alternative would be to run the job step using the credential of the SQL Server Agent service.  Since that credential would typically be one with limited privileges, you may need to create a proxy so that your PowerShell job steps can run with the necessary privileges.  For more details on proxies please refer to our earlier tip SQL Server Agent Proxies.

NOTE: One other thing is that in order to extend a PowerShell command over multiple lines, you have to use the back-tick character.  This is a little hard to see in the above screen shot.  The back-tick character is the one to the left of the number 1 on your keyboard whose shifted value is the tilda (~) character.

Next Steps

  • Take a look at the SQL Server PowerShell Overview in Books on Line for additional details on the built-in PowerShell support in SQL Server 2008.
  • There are a number of PowerShell webcasts available here.  Check them out to quickly get a basic understanding of the tool.


Last Updated: 2009-02-10


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, November 12, 2012 - 11:03:44 AM - Ray Barley Back To Top

If the select statetment returns no rows, the variable $a is null so checking whether $a.count is equal to zero fails.  First you have to check if $a is not null; you do that by 

if($a) { -- put code here if $a is not null; e.g. if($a.count -eq 0) } else { -- put code here for when $a is null; in your case this is the same as returning 0 rows


Sunday, November 11, 2012 - 3:27:05 PM - aravind Back To Top

$a = Invoke-Sqlcmd -ServerInstance "srv_name"-Database master -Query

“select name,state_desc from sys.databases where state<>0”

-ErrorAction silentlyContinue -ErrorVariable err

if($a.count -eq 0)

{

write-host "offline or suspect databases are ter"

}

else {write-host "no offline db"}

 

Here i want to check the count of $a.. If $a has any value (offline databases) it shud prompt "offline or suspect databases are ter" else "no offline db"

But the thing is I am always getting the else option as answer "offline db" .

How do i manipulate the count of the result of invoke sqlcmd (if 2 dbs offline ) shud print IF part..if not then ELSE part.. ???

your help in much appreciated .


Friday, May 04, 2012 - 7:40:59 AM - John Yavelak Back To Top

Thanks!!

It worked perfectly...

Interesting that using the "-MaxCharLength 9999" option did not settle this, the output to CSV was needed.

But Thanks Again, it's working.

Am more and more liking PowerShell

John


Thursday, May 03, 2012 - 9:56:19 PM - Ray Barley Back To Top

One way is to send the output to a CSV file; e.g. pipe your output to the Export-CSV cmdlet; see this link for the details http://technet.microsoft.com/en-us/library/ee176825.aspx


Thursday, May 03, 2012 - 2:16:48 PM - John Yavelak Back To Top

 

How do you get around what seems to be a 133 character limit when you run powershell/invoke-sqlcmd on a PC?

I'm losing part of each returned SQL qyery line


Wednesday, February 11, 2009 - 12:19:14 PM - phillips_jim Back To Top

Follow up -

Powershell Feature Pack only works with PowerShell V2.


Wednesday, February 11, 2009 - 10:42:59 AM - phillips_jim Back To Top

Thanks for the reply.  I will attempt to install the Powershell Feature Pack on a SQL Server 2005 server and see how usable it is.

You can add the two add-pssnapin commands into your profile file and you can run PoSh scripts from SQL Agent. 

An example: "powershell H:\PO.ps1" (don't use the double quoutes) using the job step type of Operating System (CmdExec).

You can not run PoSh commands directly from within the job step which you can do in SS2K8.

Even installing the PowerShell feature pack would not install all the functionality in Management Studio but might give me the functionality within Powershell to use invoke-sqlcmd.

 


Wednesday, February 11, 2009 - 9:33:17 AM - raybarley Back To Top

Assuming you aren't going to install SQL Server 2008 yet, you can download the Microsoft Windows PowerShell Extensions for SQL Server from the Microsoft SQL Server 2008 Feature Pack Site at this URL: http://www.microsoft.com/downloads/details.aspx?FamilyID=228de03f-3b5a-428a-923f-58a033d316e1&DisplayLang=en  It says that you will also need to download SQL Server 2008 Management Objects (available on the same page).  There is also a link to get WIndows PowerShell 1.0.

Once you have downloaded the PowerShell Extensions you can install them into PowerShell by using these two commands:

add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100

I think there is a way that you can add these to your profile so you don't have to do it each time you launch PowerShell.

If you have access to SQL Server 2008 you can install PowerShell which will give you the SQLPS utility; then you have PowerShell and the SQL: Server 2008 support.

As far as connecting to a SQL Server 2005 database I can't find anyting in Books on Line that says you can or you can't.  I would assume you will be able to do that. 

With SQL Server 2008 you can launch PowerSheel from just about anywhere in SQL Server Management Studio.  I haven't seen anything that says there is a way to do that with SQL Server 2005 Management Studio.  I don't htink you can execute a PowerShell script in a SQL Agent job step with a SQL Server 2005 instance either.

 

 

 

 


Tuesday, February 10, 2009 - 2:05:30 PM - phillips_jim Back To Top

Can you add or use the Invoke-Sqlcmd PowerShell related commands with a SQL Server 2005 database server?

If so what would you have to do to get it working?

 

Thanks


Learn more about SQL Server tools