Executing SQL queries from Powershell

  • Thread starter Pete Zerger (MVP)
  • Start date
P

Pete Zerger (MVP)

All,

Does anyone have a sample script for executing database queries from Powershell?
Looking for something simple I can parameterize and schedule to keep one
of my clients out of their SQL install.

Just didn't see anything when I googled for it.

Regards,

Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MCTS(Opsmgr) | MVP - Opsmgr
URL:http://www.systemcenterforum.org
User Group: http://www.systemcenterusergroup.com
MP Catalog: http://www.systemcenterforum.org/mps
Tools: http://www.systemcenterforum.org/tools/
 

My Computer

T

tojo2000

On Nov 1, 12:31 am, Pete Zerger (MVP) <[email protected]> wrote:

> All,
>
> Does anyone have a sample script for executing database queries from Powershell?
> Looking for something simple I can parameterize and schedule to keep one
> of my clients out of their SQL install.
>
> Just didn't see anything when I googled for it.
>
> Regards,
>
> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MCTS(Opsmgr) | MVP - Opsmgr
> URL:http://www.systemcenterforum.org
> User Group:http://www.systemcenterusergroup.com
> MP Catalog:http://www.systemcenterforum.org/mps
> Tools:http://www.systemcenterforum.org/tools/
Here's a couple of functions I've been using, they might be a good
starting point:

# SqlQuery.psm1
#
# Functions for getting data from a SQL Server.


[string]$DEFAULT_SQL_SERVER = 'sqlserver'
[string]$DEFAULT_SQL_DB = 'SQLDB'


# Run-SqlSelect
# Runs a SQL query and returns a hashtable of values.
#
# Args:
# $Query: The SQL query to be executed
# $SqlServer: The name of the target SQL Server
# $DB: The initial DB to connect to
# $RecordSeparator: The character to put betweeen field values in
# RowsOnly mode
# Returns:
# An array of strings where each string is a character-separated
value
# representation of a row.
#

function Run-SqlSelect {
param([string]$Query,
[string]$SqlServer = $DEFAULT_SQL_SERVER,
[string]$DB = $DEFAULT_SQL_DB,
[string]$RecordSeparator = "`t")

$conn_options = ("Data Source=$SqlServer; Initial Catalog=$DB;" +
"Integrated Security=SSPI")
$conn = New-Object
System.Data.SqlClient.SqlConnection($conn_options)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $Query
$reader = $cmd.ExecuteReader()

$results = @()
$record = 0
$columns = New-Object object[] $reader.FieldCount

while($reader.Read()) {
$results += $null
$reader.GetValues($columns) > $null
$results[$record] = ($columns | join-string2 $RecordSeparator)
$record++
}

return $results
}


# Run-SqlNonQuery
# Runs a non-query SQL statement.
#
# Args:
# $Statement: The SQL statement to be executed
# $SqlServer: The name of the target SQL Server
# $DB: The initial DB to connect to
#
# Returns:
# The number of rows affected.

function Run-SqlNonQuery {
param([string]$Statement,
[string]$SqlServer = $DEFAULT_SQL_SERVER,
[string]$DB = $DEFAULT_SQL_DB)

$conn_options = ("Data Source=$SqlServer; Initial Catalog=$DB;" +
"Integrated Security=SSPI")
$conn = New-Object
System.Data.SqlClient.SqlConnection($conn_options)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $Statement
$result = $cmd.ExecuteNonQuery()
return $result
}
 

My Computer

S

Shay Levy [MVP]

Hi Pete,

There's also the 'SQL Server PowerShell Extensions' codeplex project
http://www.codeplex.com/SQLPSX



---
Shay Levy
Windows PowerShell MVP
http://blogs.microsoft.co.il/blogs/ScriptFanatic
PowerShell Toolbar: http://tinyurl.com/PSToolbar



PZ> All,
PZ>
PZ> Does anyone have a sample script for executing database queries from
PZ> Powershell? Looking for something simple I can parameterize and
PZ> schedule to keep one of my clients out of their SQL install.
PZ>
PZ> Just didn't see anything when I googled for it.
PZ>
PZ> Regards,
PZ>
PZ> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MCTS(Opsmgr) | MVP -
PZ> Opsmgr
PZ> URL:http://www.systemcenterforum.org
PZ> User Group: http://www.systemcenterusergroup.com
PZ> MP Catalog: http://www.systemcenterforum.org/mps
PZ> Tools: http://www.systemcenterforum.org/tools/
 

My Computer

J

Jelly

clear
$SqlServer = "SQL_SERVER_NAME_HERE"
$SqlCatalog = "DATABASE_NAME_HERE"
$myuser = read-host "Enter Last Name to query for in Database: "
#*************** Query Below
*******************************************************************************
# You can organize the querys any way you want. Any Valid Tsql
statement should work... -Big Jelly. *
# $SqlQuery = "select name_agency, city, zip from T_Agency"
# $SqlQuery = "select * from T_USER"
$SqlQuery = "select nam_last_user, nam_first_user from T_USER where
nam_last_user = '$myuser' "
#*************** Query Above
*******************************************************************************
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database =
$SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
#Clear
$DataSet.Tables[0]

This query is a quick and easy one to get to work. took almost no
time from me. T_USER is the table name. so you have the server,
database and table. also, a couple of the fields. let me know how
this works for you.

John
 

My Computer

J

Jelly

And a more GENERIC SQL Database Query from Powershell, By Big Jelly

$SqlServer = "SQL_SERVER"
$SqlCatalog = "DATABASE_NAME"
#*************** Query Below
*******************************************************************************
# You can organize the querys any way you want. Any Valid Tsql
statement should work... -Big Jelly. *
# $SqlQuery = "select name_agency, city, zip from T_Agency"
# $SqlQuery = "select * from T_Agency where zip = 11787"
$SqlQuery = read-host "Please Enter a Query for the Scheduler
(eg:select * from T_Agency where zip = 11787): "
#*************** Query Above
*******************************************************************************
#do not change this code
#################################################################################################
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database =
$SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
Clear
#output the data
$DataSet.Tables[0]
 

My Computer

J

Jaykul

There are several on PoshCode...
http://poshcode.org/?q=sql*
-or-
http://poshcode.org/?q=database

--
Joel "Jaykul" Bennett
http://HuddledMasses.org/
qotd: However, never daunted, I will cope with adversity in my traditional
manner ... sulking and nausea. -- Tom K. Ryan



"Pete Zerger (MVP)" <[email protected]> wrote in message
news:[email protected]

> All,
> Does anyone have a sample script for executing database queries from
> Powershell? Looking for something simple I can parameterize and schedule
> to keep one of my clients out of their SQL install.
> Just didn't see anything when I googled for it.
>
> Regards,
>
> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MCTS(Opsmgr) | MVP -
> Opsmgr
> URL:http://www.systemcenterforum.org
> User Group: http://www.systemcenterusergroup.com
> MP Catalog: http://www.systemcenterforum.org/mps
> Tools: http://www.systemcenterforum.org/tools/
>
>
 

My Computer

Top