Hi

I am trying to automate my leaver process and one of the last things I need to do is remove a record from a SQL table. I have found the following script on the web and I am able to use it to query a SQL database successfully  (as shown in example 1) but it will not delete a record using the syntax as shown in example 2.

Example 1

$Username = "ALFKI"
$SqlServer = "sqlserver"
$SqlCatalog = "Northwind"
$SqlQuery = "Select * from dbo.customers where customerID='$Username'"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
Clear
$DataSet.Tables[0]


Example 2

$Username = "ALFKI"
$SqlServer = "sqlserver"
$SqlCatalog = "Northwind"
$SqlQuery = "Delete from dbo.customers where customerID='$Username'"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
Clear
$DataSet.Tables[0]


The error I recieve is:
Unable to index into an object of type System.Data.DataTableCollection.
At C:\scripts\SQL-TEST.ps1:17 char:17
+ $DataSet.Tables[0 <<<< ]

Does anyone know why one query would work but not another?

Thanks

Lee

I don;t have the time right now to delve into it but try this, it is much simpler:

$Username = "ALFKI"
$SqlServer = "sqlserver"
$SqlCatalog = "Northwind"

$SqlQuery = "Delete from dbo.customers where customerID='$Username'"
$connString = "Data Source=$sqlServer; Initial Catalog=$SqlCatalog; Integrated Security=SSPI"
$conn = New-Object System.Data.SqlClient.SqlConnection $connString
$sqlCommand = New-Object System.Data.SqlClient.sqlCommand $SqlQuery,$conn

$conn.open()
$cmd.ExecuteNonQuery()
$sqlCommand.ExecuteNonQuery()

As to the reason why, the delete query does not return a DataTable (no result set). You would see a similar error message if you referenced an non-existent array element i.e. $z[0]. I've tested your delete statement and it does complete using your original code, the $DataSet.Tables[0] line causes an error, however the delete completes.

As Shay pointed out, the preferred method is to use ExecuteNonQuery for queries which do not return a result set.

Thanks for the suggestions but I still recieve an error.

You cannot call a method on a null-valued expression.
At C:\scripts\sqlv2.ps1:11 char:21
+ $cmd.ExecuteNonQuery( <<<< )

Any other ideas?

There is an error on this line:

$cmd.ExecuteNonQuery()

it should be:

$sqlcommand.ExecuteNonQuery()

 

Thanks Chad, bad paste :-)