PowerShell connecting to SQL Server without SMO

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:

## - 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;

 

nonSMO_01_10-16-2015

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:

## - 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();

I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.

nonSMO_dbForge_01_10162015

After executing the non-SMO code then the record did change:

nonSMO_01a_10-16-2015

nonSMO_dbForge_02_10162015

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

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;
};
};

To test this function copy/paste the code into your PowerShell console, and the execute the following commands:

## - 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;

nonSMO_02_10-16-2015

* Execute-TSQLStoredProc function

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;

nonSMO_03_10-16-2015

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.

 

 

This entry was posted in PowerShell, SQL Server. Bookmark the permalink.