Executing SQL queries from Powershell


  1.    01 Nov 2008 #1
    Pete Zerger (MVP) Guest

    Executing SQL queries from Powershell


    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 System SpecsSystem Spec

  2.    01 Nov 2008 #2
    tojo2000 Guest

    Re: Executing SQL queries from Powershell


    On Nov 1, 12:31 am, Pete Zerger (MVP) <pete.zer...@xxxxxx> 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 System SpecsSystem Spec

  3.    01 Nov 2008 #3
    Shay Levy [MVP] Guest

    Re: Executing SQL queries from Powershell


    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 System SpecsSystem Spec

  4.    02 Nov 2008 #4
    Jelly Guest

    Re: Executing SQL queries from Powershell


    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 System SpecsSystem Spec

  5.    02 Nov 2008 #5
    Jelly Guest

    Re: Executing SQL queries from Powershell


    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 System SpecsSystem Spec

  6.    03 Nov 2008 #6
    Jaykul Guest

    Re: Executing SQL queries from Powershell


    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)" <pete.zerger@xxxxxx> wrote in message
    news:265ff311d9298cb09e1117af8d0@xxxxxx

    > 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 System SpecsSystem Spec

Executing SQL queries from Powershell

Similar Threads
Thread Forum
Executing PowerShell Scripts with IIS
Hello, we would like to write a frontend website to manage some of our Exchange Server's administrative tasks (we manage Exchange Server 2007 via...
PowerShell
sql queries turn to powershell
Using sql 2005, and i've admin access. I've a standard sql query in a text file eg select xxx from db where ...
PowerShell
executing powershell scripts on server
Hi , I want to execute powershell script on the server where I am not added as the admin and Powershell "ExecutionPolicy" is set to "Restcrited"...
PowerShell
SQL Queries
Hi All. I need some help querying a SQL database. I have been able to piece together the following: ##### strComputer = "Server01"...
PowerShell
Executing CMD.EXE from PowerShell
I'd like to be able to execute the following command: $p = ::Start("cmd.exe", "/c start /wait c:\Program Files\Microsoft Visual...
PowerShell
Executing Stored Procedure from Powershell
I saw an example of using System.Data.SqlClient.SqlCommand on at http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html I...
PowerShell

Our Sites
  • Ten Forums
  • Eight Forums
  • Seven Forums
  • Help Me Bake
  • Site Links
  • Contact Us
  • Privacy and Cookies
  • About Us
    Windows Vista Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

    Designer Media Ltd
    All times are GMT -5. The time now is 15:48.
    .