SQL through PowerShell

Working with Databases using PowerShell can be a little tricky from time to time. In this post we’ll take a look at how to work with database tables using .NET and we’ll also check out some CmdLets available. Let’s start connecting to our SQL database using the SQLConnection object:


PS > $Connection = New-Object System.Data.SQLClient.SQLConnection
PS > $Connection


StatisticsEnabled                : False
ConnectionString                 :
ConnectionTimeout                : 15
Database                         :
DataSource                       :
PacketSize                       : 8000
ServerVersion                    :
WorkstationId                    : Server
FireInfoMessageEventOnUserErrors : False
State                            : Closed
Site                             :
Container                        :

Next, we have to set the ConnectionString, this defines which DataBase that we want to connect to.
Note that the authentication method that is used in this example is integrated authentication. If you use non-integrated authentiacation, you can add “userid=username;password=password;” to the connection string.

Note that the database we will use in these examples is Northwind which is a sample database from Microsoft that you can download for free at Microsoft.com


PS > $Connection.ConnectionString = 
"server=Server;database=Northwind;trusted_connection=true;"
PS > $Connection.ConnectionString


server=Server;database=Northwind;trusted_connection=true;

And when all is set, we open the Connection.


PS > $Connection.Open()

When you are finished using your database, you can close it with the close() method.


PS > $Connection.Close()

Now that we’ve opened a connection to our database, we can start with a couple of queries. The four basic queries are:

SELECT
INSERT
UPDATE
DELETE

When executing queries, use the SQLCommand Object. Note that if you use INSERT, UPDATE or DELETE, you won’t get a value returned so you should use the ExecuteNonQuery() method. In case of SELECT, you should use the ExecuteReader() method. Let’s start looking at the SELECT query:

Northwind contains a table called Products. So if we want to see all Columns in Products we would use “SELECT * from Products” to retrieve all columns and rows:

Note. In order to retrieve the data from our database, we have to use the ExecuteReader() method and
loop through it. Column Names are retrieved through .GetName() and values are retrieved through .GetValue()


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Products"

PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}

Name                           Value
----                           -----
ProductID                      1
ProductName                    Chai
SupplierID                     1
CategoryID                     1
QuantityPerUnit                10 boxes x 20 bags
UnitPrice                      18,0000
UnitsInStock                   39
UnitsOnOrder                   0
ReorderLevel                   10
Discontinued                   False
ProductID                      2
ProductName                    Chang
SupplierID                     1
CategoryID                     1
QuantityPerUnit                24 - 12 oz bottles
UnitPrice                      19,0000
UnitsInStock                   17
UnitsOnOrder                   40
ReorderLevel                   25
Discontinued                   False
ProductID                      3
ProductName                    Aniseed Syrup
SupplierID                     1
CategoryID                     2
QuantityPerUnit                12 - 550 ml bottles
UnitPrice                      10,0000

If we want a specific Row in the table, we can specify it in our SELECT query.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Products where ProductName = 'Röd kaviar'"

PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}

Name                           Value
----                           -----
ProductID                      73
ProductName                    Röd Kaviar
SupplierID                     17
CategoryID                     8
QuantityPerUnit                24 - 150 g jars
UnitPrice                      15,0000
UnitsInStock                   101
UnitsOnOrder                   0
ReorderLevel                   5
Discontinued                   False

And if we want to check out another table, all we have to do is change the SELECT query as shown below.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       1
RegionDescription              Eastern
RegionID                       2
RegionDescription              Western
RegionID                       3
RegionDescription              Northern
RegionID                       4
RegionDescription              Southern

Let’s check out the INSERT query. INSERT is used when you want to add new data to a table. First specify the Columns that you want to INSERT into and the specify the Values.

Let’s play a little with the Region table.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = 
	"INSERT INTO Region (RegionID,RegionDescription) VALUES ('5','Even More North')"
PS > $Command.ExecuteNonQuery()

1

The return value is 1 since the row was affected.

Let’s check that the row really was affected by using a SELECT query:


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region where RegionID = '5'"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       5
RegionDescription              Even More North

The UPDATE query modifies existing data. It’s often used with a WHERE clause to limit the range of affect to the specified rows. Let’s UPDATE the row that we INSERTED above.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = 
"UPDATE Region set RegionDescription = 'Go West' WHERE RegionID = '5'"

PS > $Command.ExecuteNonQuery()

1

And again, we check that our row was affected.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region where RegionID = '5'"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       5
RegionDescription              Go West

DELETE will remove data from the database.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "DELETE * FROM Region WHERE RegionID = '5'"
PS > $Command.ExecuteNonQuery()

1

And, finally, we check that the data really was deleted.


PS > $Command = New-Object System.Data.SQLClient.SQLCommand
PS > $Command.Connection = $Connection
PS > $Command.CommandText = "SELECT * FROM Region"
PS > $Reader = $Command.ExecuteReader()
PS > $Counter = $Reader.FieldCount
PS > while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}


Name                           Value
----                           -----
RegionID                       1
RegionDescription              Eastern
RegionID                       2
RegionDescription              Western
RegionID                       3
RegionDescription              Northern
RegionID                       4
RegionDescription              Southern

Below is the Code used in this post.


$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = 
  "server=Server;database=Northwind;trusted_connection=true;"
$Connection.Open()

$Query = "SELECT * FROM Products

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $Query

$Reader = $Command.ExecuteReader()
$Counter = $Reader.FieldCount
while ($Reader.Read()) {
	for ($i = 0; $i -lt $Counter; $i++) {
		@{ $Reader.GetName($i) = $Reader.GetValue($i); }
	}
}

SQL Server 2008 R2 includes two snapins, SqlServerCmdletSnapin100 and SqlServerProviderSnapin100.
The SqlServerCmdletSnapin100 adds the Invoke-SQLCmd cmdLet. So instead of typing the code in the examples above
we can simply Add the snapin to our current session and use the CmdLet.


PS > Add-PSSNapin SqlServerCmdletSnapin100
PS > Invoke-Sqlcmd -Database NorthWind -Query "SELECT * FROM Products" -ServerInstance Server01

The SqlServerProviderSnapin100 adds a new PSDrive that when working with SQL.


PS > Add-PSSnapin SqlServerProviderSnapin100
PS > Get-PSDrive

Name           Used (GB)     Free (GB) Provider      Root
----           ---------     --------- --------      ----
A                                      FileSystem    A:\
Alias                                  Alias
C                  15,07         84,83 FileSystem    C:\
cert                                   Certificate   \
D                                      FileSystem    D:\
Env                                    Environment
Function                               Function
HKCU                                   Registry      HKEY_CURRENT_USER
HKLM                                   Registry      HKEY_LOCAL_MACHINE
SQLSERVER                              SqlServer     SQLSERVER:\
Variable                               Variable
WSMan                                  WSMan

After the SnapIn is added we can use the Provider to access SQL Server.


PS > cd SQLServer:
PS SQLSERVER:\> dir

Rating 3.67 out of 5

[?]

9 thoughts on “SQL through PowerShell”

AndrewBoldman on June 4, 2009 at 12:58 pm said:

Hi, good post. I have been wondering about this issue,so thanks for posting.
Jonas on July 5, 2010 at 2:56 pm said:

Would be good to add that you need to use “MultipleActiveResultSets=True” in the $Connection.ConnectionString =
“server=Server;database=Northwind;trusted_connection=true;MultipleActiveResultSets=True”.
Kevin on July 7, 2010 at 10:43 pm said:

“If you use non-integrated authentiacation, you can add “userid=username;password=password;” to the connection string.”
userid is actually two words – user id
http://www.connectionstrings.com/ seems like a good reference website for this type of thing.
Good article – thanks.
kannan on February 6, 2012 at 12:16 pm said:

Thanks for this info its very useful for me
Eddie on June 14, 2012 at 4:54 am said:

This has been a very good write up, the explanations were great. I hate to ask for help, but it seems that you may be able to answer this one very quickly.

I have an csv file with 100 records that I insert my PostgreSQL Server, my script works, because the database is updated but at the end of the script I get an error….”Exception calling “ExecuteNonQuery” with “0″ argument(s): “ERROR [22P02] ERROR: invalid input syntax for integer: “”; ” …..short of posting the entire sciprt, I’m not sure what about this is generating that error…

$SQLHEADER=”INSERT INTO tbl_temp_log_print (job_number, result, start_time, end_time, department_id, job_type, file_name, user_name, origional_pages, output_pages, sheets_by_copies, end_code)“

$SQLVALUES=”VALUES ($job_number, $result, $start_time, $end_time, $department_id, $job_type, $file_name, $user_name, $origional_pages, $output_pages, $sheets_by_copies, $end_code)”

$SQLQUERY=$SQLHEADER+$SQLVALUES
$DBCmd.CommandText = $SQLQUERY
[void]$DBCmd.ExecuteNonQuery()

any ideas and suggestions are very appreciated.
Thank You.
Niklas Goude on June 14, 2012 at 7:44 pm said:

check out this post: http://stackoverflow.com/questions/3949594/postgresql-error-22p02-invalid-input-syntax-for-integer
Pingback: Anwendungskontrolle » SQL through PowerShell
Matt Duguid on August 2, 2012 at 11:14 pm said:

Great post, illustrates just whats needed
HESLOus on March 18, 2013 at 2:36 pm said:

Hello nice post here PS Guru’s i have a request ,may be you can help me ,i’m newby in PS My goal is to update a SQL server table with the result of the following PS script This PS script basically loop into a list of SQL server list in a text file and collects info on DB Tlog size for each of them I wish i could create a job in my central SQL Server that would run this script and insert the result in a table in a database of this central SQL Server

the PS script : foreach ($svr in Get-Content “C:\SQL_List.txt”) { $con = “server=$svr;database=master;User Id=dba;Password=XXXXXX;” $cmd = “select @@servername, convert(date, getdate()), b.name, c.name, c.filename, c.size * 8/1024 from master.dbo.sysaltfiles as c join master..sysdatabases as b on c.dbid = b.dbid where c.groupid = 0″ $da = New-Object System.Data.SqlClient. SqlDataAdapter ($cmd, $con) $dt = New-Object System.Data.DataTable $da.fill($dt) Write-Output $dt }

PowerShell.nu

Windows PowerShell

9 thoughts on “SQL through PowerShell”

Leave a Reply Cancel reply