Powershell ExecuteNonQuery Returns -1

Here's the script
$OutputEncoding = [Text.Encoding]::ASCII
$SQLServer = "server1,2431"
$SQLDBName = "RAS"
 
Write-EventLog "Windows PowerShell" -source PowerShell -eventid 1616 -message "ROusage.ps1 has started"
 
$SQLConn = New-Object System.Data.SqlClient.SqlConnection
$SQLConn.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=TRUE;"
$SQLConn.Open()
 
Write-Host "SQL Connection Opened..."
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $SQLConn
 
#region Variables
 
#Inputs/Archive Directory
$errlog = "d:\inetpub\ftproot\daily\rousage.error"
#endregion
 
Write-Host "Gathering ROusage data..."
Write-Host "Execute RO Usage Collection"
                                                $CommandText = "RPT_ROUsageNew"
                                               $Command.CommandText = $CommandText
                                               $Command.CommandTimeOut = 0
                                               try
                                {
                                                $Return = $Command.ExecuteNonQuery()
                                }
                                catch [System.Data.SqlClient.SqlException]
                                {
                                               
                                                $Return = 0
                                }
                                Finally
                                {
                                                If ($Return -eq "1") {} else
                                                {
                                                                "Error collecting ROusage data :: " + $CommandText | Out-File $errlog -Append -Encoding ASCII
                                                }
                                }

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx


"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1."

I think you must double check what happens if you run the SQL SP using the EXACT NT login via SSMS and check the underlying tables for triggers, FKeys, etc...

Also make sure to put a SET NOCOUNT ON on the top of the code inside your SQL SP

Yes I do have SET NOCOUNT ON in the sp
This is what I'm observing
When I kick off sp from SSMS it takes about 14 minutes to complete
Running the same sp from powershell on a remote machine it run's in less than 10 seconds and the data collected from the sp is populated correctly in the tables.

It looks like it working ok, the time differences between SSMS and the powershell are a bit wierd

Thx,
I'm going to keep an eye on it and run a few more test

Thx Again!