none
Windows Powershell script running multiple SQL statements RRS feed

  • Question

  • Hey, Scripting Guys,

    Bit of a novice (and a hack, I guess), but I had a question regarding passing multiple SQL statements in the same Powershell script.  Here's the basic script:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=Server1;Database=DB1;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlConnection.Close()

    My question is: How do I insert multiple SQL statements for the $SqlCmd.CommandText value?  I've tried, but only the first statement runs (even when I use semicolons to separate them.)  Do I need to define multiple "$SqlCmd" variables for each SQL statement (e.g., $SqlCmd1, $SqlCmd2, etc.) and then pass them through the connection one by one?  The idea is to use this script to make a connection to the SQL database, then run multiple DELETE statements based on specific status message IDs.  I'm running SQL Server 2005 on a Windows Server 2003 R2 host with SCCM 2007 R2. (We get some unavoidable "noise" due to some excessive status messages and I need to periodically weed them out to do some troubleshooting.)

    The hack approach would be to run a separate script for each message ID I want to delete and set up a status filter rule through SCCM, but I've been searching for a week on how to get it condensed into one script, and I'm just lost.  (Can't see the forest for the trees, I guess.)  If I could just get pointed in the right direction on how to set up more than one SQL statement to run via the script, I think I could get over the hump.  Any help would be appreciated!

    Thanks,

    Gene

    Tuesday, March 6, 2012 2:14 AM

Answers

  • Thanks, JB, this looks like it'll work, but I think I'm doing something stupid because the records aren't deleting.  Is my syntax off?  The statements don't appear to be executing for some reason.

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=Server1;Database=DB1;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCommands = "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=2703) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=5447) AND component='SMS_MP_CONTROL_MANAGER'"

    foreach ($SqlCommand in $SqlCommands)
    {
     $SqlCmd.CommandText = $SqlCommand
     $SqlCmd.Connection = $SqlConnection
     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     $SqlAdapter.SelectCommand = $SqlCmd
    }

    $SqlConnection.Close()

    YOu are not using this at all correctly.

    Look at you statement -

    $SqlAdapter.SelectCommand = $SqlCmd

    This just assigns a command to the object.  It does not execute a command.

    A delete command is not a 'select' commend.

    Assigning a command does not execute the commend.

    I recommend that you load and use teh PowerShell extrension for sqlserver as they will be easier to use.  Using the data adapter you are trying to use a client dataset tool to run backend management commands.

    Batch scripts are better executed with the SQL support for PowerShell or with the command line utility: SQLCMD


    ¯\_(ツ)_/¯

    • Marked as answer by gramoscscs Tuesday, March 6, 2012 7:21 PM
    Tuesday, March 6, 2012 4:51 PM
  • Jeez, jv, that's lot of typing in reply to "`&".  The OP said he was having trouble with the ampersand.  I simply pointed out that it could be escaped.  I don't care about the rest, because AFAIAC, you should be using the SQL provider, and Invoke-SqlCmd.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    • Marked as answer by gramoscscs Tuesday, March 6, 2012 7:21 PM
    Tuesday, March 6, 2012 7:01 PM

All replies

  • Any reason in particular you would not use a simple loop to achieve your goal eg:

    $SqlCommands = "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'",`
    				"DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=111) AND component='SMS_DISCOVERY_DATA_MANAGER'",`
    				"DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=222) AND component='SMS_DISCOVERY_DATA_MANAGER'"
    foreach ($SqlCommand in $SqlCommands)
    {
    	#$SqlCmd.CommandText = $SqlCommand
    	#$SqlCmd.Connection = $SqlConnection
    	#$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    	#$SqlAdapter.SelectCommand = $SqlCmd
    }
    This would allow you to execute multiple commands in a serial fashion. If you want to execute them simultaneously you could run them as multiple background jobs. This is how I would execute this.

    • Proposed as answer by Bigteddy Tuesday, March 6, 2012 8:53 AM
    • Unproposed as answer by Bigteddy Tuesday, March 6, 2012 9:15 AM
    Tuesday, March 6, 2012 7:26 AM
    Moderator
  • Each statement gets separated by a new line and 'GO'.

    $sqltext=@"
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'
    go
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=111) AND component='SMS_DISCOVERY_DATA_MANAGER'
    go
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=222) AND component='SMS_DISCOVERY_DATA_MANAGER'
    go
    "@


    ¯\_(ツ)_/¯

    • Proposed as answer by Bigteddy Tuesday, March 6, 2012 9:15 AM
    Tuesday, March 6, 2012 8:58 AM
  • Interesting:  Using "go" on genuine Invoke-SQLCmd works in an SQL script, but Invoke-SQLCmd2 fails with the same script.  So, jv, you are right.


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 9:15 AM
  • Thanks, JB, this looks like it'll work, but I think I'm doing something stupid because the records aren't deleting.  Is my syntax off?  The statements don't appear to be executing for some reason.

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=Server1;Database=DB1;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCommands = "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=2703) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=5447) AND component='SMS_MP_CONTROL_MANAGER'"

    foreach ($SqlCommand in $SqlCommands)
    {
     $SqlCmd.CommandText = $SqlCommand
     $SqlCmd.Connection = $SqlConnection
     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     $SqlAdapter.SelectCommand = $SqlCmd
    }

    $SqlConnection.Close()

    Tuesday, March 6, 2012 2:02 PM
  • Replace DELETE with SELECT, and see what the query returns in SQL Server Management Studio.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 2:16 PM
  • Thanks, JB, this looks like it'll work, but I think I'm doing something stupid because the records aren't deleting.  Is my syntax off?  The statements don't appear to be executing for some reason.

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=Server1;Database=DB1;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCommands = "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=2703) AND component='SMS_INVENTORY_DATA_LOADER'",
    "DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=5447) AND component='SMS_MP_CONTROL_MANAGER'"

    foreach ($SqlCommand in $SqlCommands)
    {
     $SqlCmd.CommandText = $SqlCommand
     $SqlCmd.Connection = $SqlConnection
     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     $SqlAdapter.SelectCommand = $SqlCmd
    }

    $SqlConnection.Close()

    YOu are not using this at all correctly.

    Look at you statement -

    $SqlAdapter.SelectCommand = $SqlCmd

    This just assigns a command to the object.  It does not execute a command.

    A delete command is not a 'select' commend.

    Assigning a command does not execute the commend.

    I recommend that you load and use teh PowerShell extrension for sqlserver as they will be easier to use.  Using the data adapter you are trying to use a client dataset tool to run backend management commands.

    Batch scripts are better executed with the SQL support for PowerShell or with the command line utility: SQLCMD


    ¯\_(ツ)_/¯

    • Marked as answer by gramoscscs Tuesday, March 6, 2012 7:21 PM
    Tuesday, March 6, 2012 4:51 PM
  • This is closer to what you are trying to do.  It uses the SQLClient 'command' object to create, connect and execute 'non-query' commands.

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection=New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd.Connection.ConnectionString='Server=Server1;Database=DB1;Integrated Security=True'
    $SqlCmd.Connection.Open()
    $SqlCmd.CommandText="DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER'"
    $SqlCmd.ExecuteNonQuery()

    This is fundamental ADO.NET methodology.  The code you were using is what we would use in a form a dataset to manage the events of something like a grid or to manipulate data through a form.  It is not how we would run scripts or do maintenance.  As you have posted the code it cannot be made to work and would not even be a good way to approach this.

    Notice that the above code template is very basic.  It just creates a command object and runs it.  This can be set up to manage multiple commands and can take any script that does not return a rowset.

    To return rows from this SQLClient we would just call $SqlCmd.ExecuteReader() which returns a reader object.  A reader can used to access a rowset or to populate a dataset.

    Scripts in SQLServer are always accessed and executed by using a 'go' except in the case where the 'Execute' methods do not require a 'go'

    Execute Methods on SqlCommand:

    ExecuteNonQuery
    ExecuteReader
    ExecuteScaler
    ExecuteXmlReader


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, March 6, 2012 5:17 PM
    Tuesday, March 6, 2012 5:06 PM
  • I manged to quickly find what appears to be a very good and basic blog on how to use the SQLClient for CRUD operations.

    http://www.powershell.nu/2009/01/26/sql-through-powershell/

    Try all of the examples until you are familiar with how this is intended to work. 

    I still think that using the SQL Cmdlets for POwerShell is much easier for non-programmers.  ADO.NET is designed to support very sophisticated GUI operations as well as very complex and high speed database operations.  It assumes a complete working knowledge of ADO.NET and SQLServer.  If you are just doing simple maintenance the SQL CmdLets are much easier to learn.


    ¯\_(ツ)_/¯

    Tuesday, March 6, 2012 5:15 PM
  • That's weird.  It doesn't like when I use the SELECT statement.  Errors out with Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'FROM'.  And when I use this syntax in SSMS, it works (no double quotes and just adding a ';'):

    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=2703) AND component='SMS_INVENTORY_DATA_LOADER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=5447) AND component='SMS_MP_CONTROL_MANAGER';

    Got to be something peculiar in the way Powershell processes SQL statements differently in SSMS vs. via script because the script fails with this syntax (doesn't like the ampersand).

    I'm sure there's a way to pass those statements in serial via the script, I just can't get it to work.

    Tuesday, March 6, 2012 6:15 PM
  • That's weird.  It doesn't like when I use the SELECT statement.  Errors out with Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'FROM'.  And when I use this syntax in SSMS, it works (no double quotes and just adding a ';'):

    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_INVENTORY_DATA_LOADER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=2703) AND component='SMS_INVENTORY_DATA_LOADER';
    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=5447) AND component='SMS_MP_CONTROL_MANAGER';

    Got to be something peculiar in the way Powershell processes SQL statements differently in SSMS vs. via script because the script fails with this syntax (doesn't like the ampersand).

    I'm sure there's a way to pass those statements in serial via the script, I just can't get it to work.

    What yo are trying to d ocannot be made to worl.

    See my previous post for instruncions on how the use the SQLClient.


    ¯\_(ツ)_/¯

    Tuesday, March 6, 2012 6:18 PM
  • You can escape the ampersand, like so:

    `&


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 6:31 PM
  • You can escape the ampersand like so:

    `&


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 6:32 PM
  • Here's what I put in my profile to give me an SQL provider in my shell:

    #
    # Add the SQL Server Provider.
    #
    
    $ErrorActionPreference = "Stop"
    
    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
    
    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
    {
        throw "SQL Server Provider for Windows PowerShell is not installed."
    }
    else
    {
        $item = Get-ItemProperty $sqlpsreg
        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
    }
    
    
    #
    # Set mandatory variables for the SQL Server provider
    #
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
    
    #
    # Load the snapins, type data, format data
    #
    Push-Location
    cd $sqlpsPath
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
    update-FormatData -prependpath SQLProvider.Format.ps1xml 
    Pop-Location


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 6:34 PM
  • You can escape the ampersand like so:

    `&


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    With a single quoted string you need to escape teh single quotes.

    Please look closely at what is being posted:

    DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'

    Note tha it has embedded single quotes

    $text="DELETE TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'"

    Would work or:

    $text='@
    DELETE
    TOP(100000) FROM StatusMessages WHERE (ID & 0x0000FFFF=682) AND component='SMS_DISCOVERY_DATA_MANAGER'
    '@

    Use a @here string which will take the single quotes and hide the ampersand.


    ¯\_(ツ)_/¯

    Tuesday, March 6, 2012 6:39 PM
  • Jeez, jv, that's lot of typing in reply to "`&".  The OP said he was having trouble with the ampersand.  I simply pointed out that it could be escaped.  I don't care about the rest, because AFAIAC, you should be using the SQL provider, and Invoke-SqlCmd.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    • Marked as answer by gramoscscs Tuesday, March 6, 2012 7:21 PM
    Tuesday, March 6, 2012 7:01 PM
  • Much appreciated, guys!  I'm going to digest all this info and go ahead and load the SQL provider.  Seems like the best way to go and you're both in agreement.

    Sorry this took so long!  Many thanks for your help!

    G.

    Tuesday, March 6, 2012 7:22 PM
  • Using Invoke-SQLCmd, you can specify a script file, which can contain any valid T-SQL script.  It is not limited to running one command at a time.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Tuesday, March 6, 2012 7:25 PM
  • any insights on how do we seperate ORACLE SQL statements. Theabove suggestion using "GO" doesnt work in ORACLE SQL
    Tuesday, January 14, 2020 2:35 PM
  • any insights on how do we seperate ORACLE SQL statements. Theabove suggestion using "GO" doesnt work in ORACLE SQL

    Please do not add unrelated questions to another users topic and do not add to very old and answered topics.

    Your question needs to be asked in an Oracle forum.  It is not a Microsoft product.

    Note that oracle does not have a true "batch" command.


    \_(ツ)_/

    Tuesday, January 14, 2020 2:44 PM