PowerShell connecting to SQL Server without SMO

来源:互联网 时间:1970-01-01

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;IntegratedSecurity=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;

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;IntegratedSecurity=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.

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

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;UserID=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;

* 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;UserID=sa;Password=$Adm1n!';$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";## - Execute the function with its parameters:Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;

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.