Advertisements
Home > Powershell, SQL Server > PowerShell for Execute SQL Query

PowerShell for Execute SQL Query

Invoke-Sqlcmd cmdlet which is all-purpose SQL utility cmdlet being used in PowerShell. As the name suggests, Invoke-Sqlcmd cmdlet allows you to run T-SQL code or scripts and commands supported by the SQLCMD utility. It also allows you to run XQuery code. In this article, we will be looking at two ways of using Invoke-Sqlcmd.

The first is by specifying a SQL File Name and exporting results to CSV file

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"

#provide your database name where you want to execute your SQL File
$DatabaseName = "AdventureWorks2008R2"

#Invoke SQL to execute SQL File and export to CSV
Invoke-Sqlcmd `
-InputFile "C:\Temp\SQLScript.sql" `
-ServerInstance $SQLInstanceName `
-Database $DatabaseName |
Export-CSV -LiteralPath "C:\Temp\PowerShellRessults.csv" `
-NoTypeInformation

The second is by providing inline SQL Query and view the results.

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"

#provide your database name where you want to execute your SQL Script
$DatabaseName = "AdventureWorks2008R2"

#Invoke SQL to execute SQL File and export to CSV
Invoke-Sqlcmd `
-Query "SELECT ProductID, Name, ProductNumber, Color, Size FROM Production.Product" `
-ServerInstance $SQLInstanceName `
-Database $DatabaseName |
Select ProductID, Name, ProductNumber, Color, Size |
Format-Table

Hope this helps

Sandip

Advertisements
  1. March 11, 2013 at 4:34 pm

    It’s really a great and helpful piece of information. I’m glad that you shared this helpful information with us. Please keep us informed like this. Thanks for sharing

  2. vps
    March 14, 2013 at 9:01 pm

    I really love this site. You write about very interesting things. Thanks for all your tips and information.

  3. April 4, 2013 at 1:07 am

    Heya i am for the first time here. I found this board and I find It really useful & it helped me out much. I hope to give something back and help others like you helped me.

  4. April 4, 2013 at 10:02 pm

    Good day! This post could not be written any better! Reading through this post reminds me of my good old room mate! He always kept chatting about this. I will forward this write-up to him. Fairly certain he will have a good read. Many thanks for sharing!

  5. April 9, 2013 at 1:10 am

    Can I simply say what a reduction to find somebody who truly is aware of what theyre speaking about on the internet. You definitely know the way to carry an issue to gentle and make it important. Extra people have to learn this and understand this aspect of the story. I cant imagine youre not more in style since you definitely have the gift.

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: