Skip to content

Instantly share code, notes, and snippets.

@jdhitsolutions
Created July 21, 2017 20:08
  • Star 4 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jdhitsolutions/4851ae5e3f85d80792f64809caf22fdc to your computer and use it in GitHub Desktop.
A PowerShell script to invoke any T-SQL query against any SQL supported database. This does not use any of the SQL modules.
#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
@mashoaib
Copy link

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

@Richtpt
Copy link

Richtpt commented Jan 30, 2024

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!

@jdhitsolutions
Copy link
Author

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.

@Richtpt
Copy link

Richtpt commented Jan 30, 2024

Thanks much!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment