A PowerShell script to invoke any T-SQL query against any SQL supported database. This does not use any of the SQL modules.

Invoke-SqlQuery.ps1

#requires -version 5.0

<#
.SYNOPSIS
    Invoke any T-SQL query against any SQL supported database.
.DESCRIPTION
    This command uses the .NET SqlClient classes to connect to a SQL database and execute any type of query. The default behavior is to use integrated Windows authentication, but you can pass a credential object for a username and password. This works well when run on Linux.

    When you run a SELECT query the command will write a custom object for each row to the pipeline.
.PARAMETER Query
Any T-SQL query string
.PARAMETER Database
The database name. It is assumed the account you are using has the necessary permissions.
.PARAMETER ServerInstance
The name of the SQL server instance. 
.PARAMETER Credential
If you need to specify a username and password, use a PSCredential object.
.EXAMPLE
    PS C:\Scripts> .\Invoke-SqlQuery "Select * from diskinfo" -Database scripting

    ComputerName : YPJH10
    DiskSize     : 206266429440
    DriveType    : 3
    FreeSpace    : 27722903552
    DriveID      : C:
    DateAdded    : 6/23/2017 5:24:01 PM    

    ComputerName : YPJH10
    DiskSize     : 26843541504
    DriveType    : 3
    FreeSpace    : 16025034752
    DriveID      : D:
    DateAdded    : 6/23/2017 5:24:01 PM

    This queries the Scripting database in the localhost\SQLExpress instance.

    .EXAMPLE
    PS C:\Scripts> .\Invoke-SqlQuery "Insert Into diskinfo (ComputerName,DiskSize,DriveType,FreeSpace,DriveID,DateAdded) Values ('SERVER01',53687091200,3,41875931136,'C:','7/20/2017 4:12PM')" -Database scripting -credential jane

    Running an INSERT query with alternate credentials. In this situation the user would be prompted for the password.

    .EXAMPLE
    PS C:\Scripts> .\Invoke-SqlQuery "Select * from diskinfo where computername='server01'" -database scripting | format-table

    ComputerName    DiskSize DriveType   FreeSpace DriveID DateAdded
    ------------    -------- ---------   --------- ------- ---------
    SERVER01     53687091200         3 46707769344 C:      7/14/2017 4:02:00 PM
    SERVER01     53687091200         3 41875931136 C:      7/20/2017 4:12:00 PM
    SERVER01     53687091200         3 41811131136 C:      6/2 0/2017 5:12:00 PM    

.INPUTS
    None
.OUTPUTS
    [PSCustomObject]
.NOTES
    Version 1.0
    This function does not rely on any SQL modules.

    Learn more about PowerShell:
    http://jdhitsolutions.com/blog/essential-powershell-resources/
    

    ****************************************************************
    * DO NOT USE IN A PRODUCTION ENVIRONMENT UNTIL YOU HAVE TESTED *
    * THOROUGHLY IN A LAB ENVIRONMENT. USE AT YOUR OWN RISK.  IF   *
    * YOU DO NOT UNDERSTAND WHAT THIS SCRIPT DOES OR HOW IT WORKS, *
    * DO NOT USE IT OUTSIDE OF A SECURE, TEST SETTING.             *
    ****************************************************************
#>
[cmdletbinding(SupportsShouldProcess,DefaultParameterSetName="Default")]
Param(
[Parameter(Position = 0, Mandatory, HelpMessage = "The T-SQL query to execute")]
[ValidateNotNullorEmpty()]
[string]$Query,
[Parameter(Mandatory, HelpMessage = "The name of the database")]
[ValidateNotNullorEmpty()]
[string]$Database,
[Parameter(Mandatory,ParameterSetName='credential')]
[pscredential]$Credential,
#The server instance name
[ValidateNotNullorEmpty()]
[string]$ServerInstance = "$env:computername\SqlExpress"
)

Begin {
    Write-Verbose "[BEGIN  ] Starting: $($MyInvocation.Mycommand)"  

    if ($PSCmdlet.ParameterSetName -eq 'credential') {
        $username = $Credential.UserName
        $password = $Credential.GetNetworkCredential().Password
    }

    Write-Verbose "[BEGIN  ] Creating the SQL Connection object"
    $connection = New-Object system.data.sqlclient.sqlconnection
    
    Write-Verbose "[BEGIN  ] Creating the SQL Command object"
    $cmd = New-Object system.Data.SqlClient.SqlCommand
 
} #begin

Process {
    Write-Verbose "[PROCESS] Opening the connection to $ServerInstance"
    Write-Verbose "[PROCESS] Using database $Database"
    if ($Username -AND $password) {
        Write-Verbose "[PROCESS] Using credential"
        $connection.connectionstring = "Data Source=$ServerInstance;Initial Catalog=$Database;User ID=$Username;Password=$Password;"
    }
    else {
        Write-Verbose "[PROCESS] Using Windows authentication"
        $connection.connectionstring = "Data Source=$ServerInstance;Initial Catalog=$Database;Integrated Security=SSPI;"
    }
    $connection.open()

    #join the connection to the command object
    $cmd.connection = $connection
    $cmd.CommandText = $query
    
    Write-Verbose "[PROCESS] Invoking $query"
    if ($PSCmdlet.ShouldProcess($Query)) {
        
        #determine what method to invoke based on the query
        Switch -regex ($query) {
         "^Select (\w+|\*)|(@@\w+ AS)" { 
         
                $reader = $cmd.executereader()
                $out=@()
                #convert datarows to a custom object
                while ($reader.read()) {
                
                $h = [ordered]@{}
                for ($i=0;$i -lt $reader.FieldCount;$i++) {
                  $col = $reader.getname($i)
                  
                  $h.add($col,$reader.getvalue($i))
                } #for
                  $out+=new-object -TypeName psobject -Property $h 
                } #while

                $out
                $reader.close()
                Break
         }
         "@@" { 
                $cmd.ExecuteScalar()
                Break
         }
         Default {
            $cmd.ExecuteNonQuery() | Out-Null
         }
        }
    } #should process

}

End {
    Write-Verbose "[END    ] Closing the connection"
    $connection.close()

    Write-Verbose "[END    ] Ending: $($MyInvocation.Mycommand)"
} #end

Hi,
Please help me to get to the PowerShell world. I am a DBA (I use, oracle and SQL Server).
I need to write a script or create a job to refresh a dev database from production.

• We use DDBoost for backup and restore dbs
• We want that a developer can kick off the script or job to refresh his/her DB without asking me or other DBA.
• We want to take input from him/her (Target and Source databases names) before kicking off the script or job.

So how can I take input from a user for Target and Source database then pass those values to T-SQL or CLI or even PowerShell scripts to restore a dev database from most recent backup of production DB.

Thanks.

Shoaib

Does this work for EXEC sproc? I have tried and it returns nothing. My test sproc has a parameter and if I leave the parameter off, I get an error message that the sproc expects the parameter. But then when I add the parameter, I get no results or errors, it just runs with no output.
However, if I execute the sproc in SSMS it works fine. I'm doing this:
$Query = "EXEC dbo.sp_Test_Sproc @param1='FRED'"

My query just does a "SELECT * FROM table WHERE column = @param1", nothing complex.

I also tried creating another sproc with the same SELECT but no parameters. Again, works fine in SSMS, but I don't get any results from Powershell. If I put the sproc code in $Query, it works fine. I added -verbose and everything looks the same as when my query is just the SELECT code.

Am I missing something to get this to work with EXEC or is it not written to handle that? Thanks!

This script isn't designed to invoke stored procedures. It is intended to run T-SQL queries against a named database. I wrote this script to simplify SQL commands for an IT Pro. Data professionals who want to use PowerShell should look at DBATools which you can install from the PowerShell Gallery.

Thanks much!