Sometimes there will be a need to provide a scripting solution to connect to SQL Server to execute either a TSQL query or Stored-Procedure without the need of installing a SQL Server bits. Here I’m providing two code snippets that gets the job done without the use of SMO (SQL Server Management Object) class.
Executing a T-SQL Query
Use the System.Data.SqlClient namespace from the .NET Framework Data Provider for SQL Server in order to build functions to execute T-SQL statement(s) and/or SQL Stored-Procedure with PowerShell.
Here’s an example in how to execute a T-SQL statement querying a table in its basic form:
[sourcecode language=”powershell”]
## – Set PowerShell variables:
$ConnectionString = ‘server=YourMachineName\MSSQL2K14;database=Master;Integrated
Security=false;User ID=sa;Password=$myPwd!’;
$TSQLQuery = "Select * from AdventureWorks2014.dbo.AWBuildVersion;";
## – Connect and Execute Stored-Procedure:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery, $ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
[/sourcecode]
Executing a T-SQL Stored-Procedure
Now the code for executing SQL Stored-Procedure will be differect as we are going to use the ‘.sqlCommand‘ class to process the T-SQL Statement to run a custom table update procedure in its basic form:
[sourcecode language=”powershell”]
## – Set PowerShell variables:
$ConnectionString = ‘server=YourMachineName\MSSQL2K14;database=Master;Integrated
Security=false;User ID=sa;Password=$myPwd!’;
$TSQLQuery = "Exec [AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion];";
## – Connect and Execute Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open()
$sqlCmd = New-Object Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
$sqlCon.close();
[/sourcecode]
I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.
After executing the non-SMO code then the record did change:
Most important, when using the Data.SqlClient.SqlConnection class, the connection need be ‘.Close()‘ after executing the T-SQL command.
Error Handling
As we are connecting to the SQL Server to execute T-SQL Commands, its important to add some error handling routine to trap errors during the connectivity and/or T-SQL Statement(s) processing. And, we do this by evolving the basic PowerShell code into a function.
Lets create two functions that will accept two parameters: a connection string, and the T-SQL Query.
* Get-TSQLQuery function
[sourcecode language=”powershell”]
function Get-TSQLQuery
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)
Try
{
## – Non_SMO Get SQL query:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery,
$ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
}
Catch
{
Write-Verbose "Error executing T-SQL Query: `r`n[$($error[0])]" -Verbose;
};
};
[/sourcecode]
To test this function copy/paste the code into your PowerShell console, and the execute the following commands:
[sourcecode language=”powershell”]
## – Set PowerShell variables:
$conn = ‘server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User
ID=sa;Password=$Adm1n!’;
$tsql = "Select * from AdventureWorks2014.dbo.AWBuildVersion";
## – Execute the function with its parameters:
(Get-TSQLQuery -ConnectionString $conn -TSQLQuery $tsql) `
| Select-Object `
SystemInformationID, ‘Database Version’, VersionDate, ModifiedDate `
| Format-Table -AutoSize;
[/sourcecode]
* Execute-TSQLStoredProc function
[sourcecode language=”powershell”]
function Execute-TSQLStoredProc
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)
Try
{
## – NON-SMO executing TSQL Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open();
$sqlCmd = New-Object Data.SqlClient.SqlCommand;
$sqlCmd.Connection = $sqlCon;
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
}
Catch
{
Write-Verbose "Error executing T-SQL Stored-Procedure: `r`n[$($error[0])]"
-Verbose;
}; $sqlCon.close();
};
## – Set PowerShell variables:
$Conn = ‘server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User
ID=sa;Password=$Adm1n!’;
$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";
## – Execute the function with its parameters:
Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;
[/sourcecode]
Adding the Try/Catch error handling block helps is trapping and displaying the errors.
This method serves as another alternative to connect to a SQL Server without the need to install an instance on a system.