PoshCode Logo PowerShell Code Repository

Execute-SQLCommand by Justin Dearing 6 years ago (modification of post by dragonmc77 view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2579"></script>download | new post

Simple function that executes a command (stored procedure) against an SQL database.

  1. function Execute-SQLCommand {param(     [string]$Server,                                #the host name of the SQL server
  2.                                         [string]$Database,                              #the name of the database
  3.                                         [System.Data.SqlClient.SqlCommand]$Command)     #the command to execute (name of stored procedure)
  4.  
  5.         $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
  6.         $sqlConnection.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=user;Initial Catalog=$Database;Data Source=$Server"
  7.        
  8.         $Command.CommandType = [System.Data.CommandType]::Text
  9.         $Command.Connection = $sqlConnection
  10.        
  11.         $sqlConnection.Open()
  12.         $Result = $Command.ExecuteNonQuery()
  13.         $sqlConnection.Close()
  14.        
  15.         if ($Result -gt 0) {return $TRUE} else {return $FALSE}
  16. }

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me