Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1709
  • Last Modified:

Powershell ExecuteNonQuery Returns -1

Hi,
I have a powershell script that kicks off a store procedure.
The ExecuteNonQuery does not execute the stored procedure and returns a -1
I am using a domain account and I can launch the sp successfully from sql mgmt studio
Need assistance on what -1 means

Note:
The script works fine referencing same database but on another dev sql server.
 
I'll add the powershell script shortly.
0
Mauro Cazabonnet
Asked:
Mauro Cazabonnet
  • 3
  • 2
1 Solution
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
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
                                                }
                                }
0
 
lcohanDatabase AnalystCommented:
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
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
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
0
 
lcohanDatabase AnalystCommented:
"It looks like it working ok, the time differences between SSMS and the powershell are a bit wierd"

how must data does the SP returns?
is the powershell allways executed AFTER the SP call via SSMS - if yes, cold vs "warm" code and data cache could be your answer.

Either way you should use SET EXECUTION TIME ON in SSMS prior to exec the SP then you will see the actual execution time of that SP.
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Thx,
I'm going to keep an eye on it and run a few more test

Thx Again!
0

Featured Post

 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now