Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Invoke a T-SQL batch file or string using ADO.NET and Power Shell
$ErrorActionPreference = "Stop"
Set-StrictMode -Version 3.0
<#--------------------------------------------------------------------------
https://gallery.technet.microsoft.com/scriptcenter/The-PowerShell-script-for-2a2456c4
.SYNOPSIS
Script for running T-SQL files in MS SQL Server
Andy Mishechkin
.DESCRIPTION
runsql.ps1 has a next command prompt format:
Invoke-AdoNetSqlCmd -server MSSQLServerInstance -dbname ExecContextDB -file MyTSQL.sql [-go] [-u SQLUser] [-p SQLPassword]
Mandatory parameters:
-server - name of Microsoft SQL Server instance
-dbname - database name for T-SQL execution context (use the '-dbname master' for creation of new database)
Optional parameters:
-sqlfile - name of .sql file, which contain T-SQL code for execution
-sql - T-sql to execute
-go - parameter-switch, which must be, if T-SQL code is contains 'GO' statements. If you will use the -go switch for T-SQL script, which is not contains 'GO'-statements - this script will not execute
-u - the user name if using Microsoft SQL Server authentication
-p - the password if using Microsoft SQL Server authentication
Examples.
1) Execute on local SQL Server the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and contains 'GO' statements, using
Windows credentials of current user:
Invoke-AdoNetSqlCmd -server local -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go
2) Execute on remote SQL Server Express with
machine name 'SQLSrvr' the script CreateDB.sql, which is placed in C:\MyTSQLScripts\ and
contains 'GO' statements, using SQL Server user name 'sa' and password 'S@Passw0rd':
Invoke-AdoNetSqlCmd -server SQLSrvr\SQLEXPRESS -dbname master -file C:\MyTSQLScripts\CreateDB.sql -go -u sa -p S@Passw0rd
---------------------------------------------------------------------------#>
Function Invoke-AdoNetSqlCmd
{
#Script parameters
param(
#Name of MS SQL Server instance
[parameter(Mandatory=$true,
HelpMessage="Specify the SQL Server name where will be run a T-SQL code",Position=0)]
[String]
[ValidateNotNullOrEmpty()]
$server = $(throw "sqlserver parameter is required."),
#Database name for execution context
[parameter(Mandatory=$true,
HelpMessage="Specify the context database name",Position=1)]
[String]
[ValidateNotNullOrEmpty()]
$dbname = $(throw "dbname parameter is required."),
#Name of T-SQL file (.sql)
[parameter(Mandatory=$false,
HelpMessage="Specify the name of T-SQL file (*.sql) which will be run",Position=2)]
[String]
$sqlFile,
[parameter(Mandatory=$false,
HelpMessage="Specify the name of T-SQL file (*.sql) which will be run",Position=3)]
[String]
[AllowEmptyString()]
$sql,
#The GO switch. Must be specified if T-SQL code is contain the GO instructions
[parameter(Mandatory=$false,Position=4)]
[Switch]
[AllowEmptyString()]
$go,
#MS SQL Server user name
[parameter(Mandatory=$false,Position=5)]
[String]
[AllowEmptyString()]
$u,
#MS SQL Server password name
[parameter(Mandatory=$false,Position=6)]
[String]
[AllowEmptyString()]
$p
)
if ([string]::IsNullOrWhitespace($sqlFile))
{
if ($sql -eq $null)
{
throw "sql or file must be specified."
}
$file = [System.IO.Path]::GetTempFileName()
$sql | Out-File -Encoding utf8 -FilePath $file
}
else
{
$file = $sqlFile
}
#Connect to MS SQL Server
try
{
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
#The MS SQL Server user and password is specified
if($u -and $p)
{
$SQLConnection.ConnectionString = "Server=" + $server + ";Database=" + $dbname + ";User ID= " + $u + ";Password=" + $p + ";"
}
#The MS SQL Server user and password is not specified - using the Windows user credentials
else
{
$SQLConnection.ConnectionString = "Server=" + $server + ";Database=" + $dbname + ";Integrated Security=True"
}
$SQLConnection.Open()
}
#Error of connection
catch
{
throw $Error[0]
}
#The GO switch is specified - parsing T-SQL code with GO
if($go)
{
$SQLCommandText = @(Get-Content -Path $file)
$SQLCommandText += "GO"
foreach($SQLString in $SQLCommandText)
{
if($SQLString -ne "GO")
{
#Preparation of SQL packet
$SQLPacket += $SQLString + "`n"
}
else
{
$IsSQLErr = $false
#Execution of SQL packet
try
{
if ([string]::IsNullOrWhiteSpace($SQLPacket) -eq $false)
{
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLPacket, $SQLConnection)
$SQLCommand.ExecuteScalar()
}
}
catch
{
$SQLPacket | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
$Error[0] | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
"----------" | Out-File -FilePath ($PWD.Path + "\SQLErrors.txt") -Append
throw $Error[0]
}
$SQLPacket = ""
}
}
}
else
{
#Reading the T-SQL file as a whole packet
$SQLCommandText = @([IO.File]::ReadAllText($file))
#Execution of SQL packet
try
{
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLCommandText, $SQLConnection)
$SQLCommand.ExecuteScalar()
}
catch
{
throw $Error[0]
}
}
#Disconnection from MS SQL Server
$SQLConnection.Close()
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment