ExecuteNonQuery

• jshurak

  SSCrazy

  Points: 2763

  More actions

  April 11, 2012 at 11:47 am

  #253895

  I'm trying to write a script to loop through a series of sql instances, execute a t sql statement. (this on in particular is adding operators)

  function Get-SqlCommand

  {

  param($serverName,$databaseName,$query)

  $SqlConnection = New-Object System.Data.SqlClient.SQLConnection

  $SqlConnection.ConnectionString = "Server=$servername;Database=$databasename;Integrated Security=SSPI;"

  $SqlConnection.Open()

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $query

  $SqlCmd.ExecuteNonQuery()

  $SqlConnection.Close()

  } #Get-SqlCommand

  However, I'm receiving this error message:

  Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Connection property has not been initialized."

  At C:\Documents and Settings\jds015\My Documents\operatorcreator.ps1:46 char:28

  + $SqlCmd.ExecuteNonQuery <<<< ()

  + CategoryInfo : NotSpecified: (:) [], MethodInvocationException

  + FullyQualifiedErrorId : DotNetMethodException

  Stepping through the code, I can verify that my parameters are correct. What else am I missing for this?

• jshurak

  SSCrazy

  Points: 2763

  More actions

  April 11, 2012 at 1:30 pm

  #1472188

  Got it:

  function Get-SqlCommand

  {

  param($serverName,$databaseName,$query)

  $SqlConnection = New-Object System.Data.SqlClient.SQLConnection

  $SqlConnection.ConnectionString = "Server=$servername;Database=$databasename;Integrated Security=SSPI;"

  $ConnectionString = "Server=$servername;Database=$databasename;Integrated Security=SSPI;"

  $SqlConnection.Open()

  $SqlCmd = New-Object "System.Data.SqlClient.SqlCommand" ($query,$SqlConnection)

  $SqlCmd.ExecuteNonQuery()

  $SqlConnection.Close()

  } #Get-SqlCommand