none
sql stored procedure works but myC->ExecuteNonQuery(); returns -1

    Question

  • I have a call to a stored procedure.

    All this "used to" work.  SOmething changed, like some MS auto-update.

    the calling routine:

    extern int VCISQLUpdatePoint( ANYTYPE *pt_ripper, ANYTYPE *sy_rec, int itypesize )
    {
    	int iret = 1;
    	char acType[16];
    	char *s = acType;
    	
    	SqlConnection ^myConnection = ConnectToSQLServer(NULL);
    
    	String ^myUpdatePointQuery = "vciWriteDataCS";
    	SqlCommand  ^myC = gcnew SqlCommand(myUpdatePointQuery );
    	myC->CommandType = CommandType::StoredProcedure;
    	myC->Connection = myConnection;
    
    	strcpy_s( acType, DB_bintostr( pt_ripper->id.type ) );
    	strcat_s( acType, "s" );
    
    	String ^sType = gcnew String(acType );
    	String ^sPtName = gcnew String(pt_ripper->id.label );
    	String ^sVECName = gcnew String(sy_rec->id.label );
    	
    	int iBinaryLength = itypesize - sizeof( RECID );
    	Byte ^binarydata = gcnew  Byte[iBinaryLength];
    	//if ( pt_ripper->id.type == AV )
    	//{
    	//	iret = 1;
    	//}
    
        MemoryStream^ memStream = gcnew MemoryStream( iBinaryLength );
    	// CAN'T DO THIS!!!!!!!!!!!!!!
    	// CLR REQUIRES 4 BYTE ALIGNMENT - RECORD TYPES DEPEND ON 2 BYTE ALIGNMENT!!!!!!
    	//unsigned char *ac = (unsigned char *)&pt_ripper->flags;
    	//
    	// SO DO THIS INSTEAD:
    	//
    	unsigned char *ac = (unsigned char *)pt_ripper;
    	ac += sizeof( RECID );
    
    	int i;
    	for ( i = 0; i < iBinaryLength; i++ )
    		memStream->WriteByte(*ac++);
    	memStream->Seek(0,SeekOrigin::Begin);
    
    	BinaryReader ^rdr = gcnew BinaryReader(  memStream );
    
    	myC->Parameters->AddWithValue( "@acPointType", sType );
    	myC->Parameters->AddWithValue( "@acPointName", sPtName );
    	myC->Parameters->AddWithValue( "@acVECName",   sVECName );
    
    	myC->Parameters->Add("@acDataBuf", SqlDbType::Binary, iBinaryLength);
    	myC->Parameters["@acDataBuf"]->Value =  rdr->ReadBytes(iBinaryLength);
    
    	myC->Parameters->Add( "@iLen",   SqlDbType::Int );
    	i =  iBinaryLength; //DB_pcutypsiz(pt_ripper->id.type);
    	myC->Parameters["@iLen"]->Value =   i;
    
    	myC->Parameters->Add( "@uiOffset",   SqlDbType::Int );
    	myC->Parameters["@uiOffset"]->Value =    sizeof( RECID );
    
    //	int RETURN_VALUE = 3;
    //	myC->Parameters->Add("@RETURN_VALUE", SqlDbType::Int);
    	SqlParameter RowCount;
    	myC->Parameters->Add( "@RowCount", SqlDbType::Int);
    	myC->Parameters["@RowCount"]->Direction =  ParameterDirection::ReturnValue;
        //myC->Parameters["@RETURN_VALUE"]->Value = RETURN_VALUE;
    
    
    
    		
    	try
    	{
    		myC->Connection->Open();
    		iret = myC->ExecuteNonQuery();
    		if ( iret > 0 )
    		   iret = (int)myC->Parameters["@RowCount"]->Value;
    		myC->Connection->Close();
    
    	}


    Oddly, myC->ExecuteNonQuery(); is supposed to return the number of rows affected.  My stored procedure returns a value that it calculates, that, by application determination, returns a success/failure code.

    But myC->ExecuteNonQuery(); returns -1.

    Where does that come from?

    The stored procedure:

        [Microsoft.SqlServer.Server.SqlProcedure]
        public static int vciWriteDataCS
            ( 
    		String acPointType,
            String acPointName,
    		String acVECName,
    		byte[] acDataBuf,
    		int iLen,
    		int uiOffset			
            )
        {
    
            int  ioffset;
            int iRet = 1;
            int iSucceeded = 0;
            pt_type_table_struct FieldDef;
    
            pt_type_table_struct[] ts_whole_table;
            ts_whole_table = GetAllFields(acPointType);
    
    
            return (5);
    

    Even when not stubbed out with the return( 5 ); the stored procedure does execute, the required fields are updated.

    So where does the -1 come from?


    BDM

    Monday, May 14, 2012 7:28 PM

All replies

  • There’s a couple of things here....
     
    Is the calling procedure calling from a .NET client or some other ..NET stored procedure? if its being called from a client, the client is in one process, the stored procedure is inside SQL Server. The variables are not local to one another. This is true even if you’re calling from one ..NET stored procedure to another. Was this code ported from a piece of all-in-one-module client-side code to a .NET client calling .NET stored procedure, maybe?
     
    Since SQL Server input parameters = pass-by-value, changes to input parameters will never change the value of “outer” variables in the calling program. In order to return the values to the caller, the parameters must be defined as pass-by-reference. And defined as OUTPUT parameters in the SQL that you use to deploy the .NET stored procedure. And defined as ParameterDirection.InputOutput in the client.
     
    if you don’t return anything in code that expects an integer to be returned, that’s a syntax error and produces a compile-time error. What *were* you returning before you stubbed in the value “5”.
     
    Cheers,
    Bob
    Monday, May 14, 2012 8:20 PM
    Moderator
  • Hi Bob,

    1. This has been working for years, and I didn't change anything.  Honest.
    2. The calling routine is a CLR DLL; Targeted framework: .NET Framework, Version=v4.0
    3. Every parameter is an input to the stored procedure
    4. Note:  <myC->Parameters["@RowCount"]->Direction =  ParameterDirection::ReturnValue;> to cature the return value.
    5. As per code above, the caller receives -1 as a return from <iret = myC->ExecuteNonQuery();> It does not get far enough to look at the return value <iret = (int)myC->Parameters["@RowCount"]->Value;

    Boyd


    BDM


    • Edited by Boyd1 Monday, May 14, 2012 8:47 PM
    Monday, May 14, 2012 8:36 PM
  • Just for yucks, I commented out the condition so I could get the application returned value:

    		myC->Connection->Open();
    		iret = myC->ExecuteNonQuery();
    		//if ( iret > 0 )
    		   iret = (int)myC->Parameters["@RowCount"]->Value;
    		myC->Connection->Close();
    

    So the debugger shows <ret = myC->ExecuteNonQuery();>  results in -1,

    and <iret = (int)myC->Parameters["@RowCount"]->Value; > reults in 5.

    THIS SHOULD NOT HAPPEN!

    First off, <ret = myC->ExecuteNonQuery();>   never return a negative value

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

    As per the above link, does this mean that a call to a stored procedure as is the case here will always have myC->ExecuteNonQuery(); return -1?  If so, when did this happen?  I have always looked for iret >= 0.

    I also looked at the following blog

    http://aspsoft.blogs.com/jonas/2006/10/executenonquery.html

    ad set NOCOUNT OFF in an sql command.

    Does this help?


    BDM

    Monday, May 14, 2012 9:25 PM
  • OK, I get it. You're not expecting the value of the input parameters to change. And you only look at the return code if the value returned by ExecuteNonQuery returns a positive value to start with.

    Looking at the page of documentation in your link for ExecuteNonQuery (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx), it says the following:

    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.

    So in your original code, I don't see the stored procedure doing any UPDATE, INSERT or DELETE statements, unless these statements appear in the subroutine that you call (no source for it in your problem description) named GetAllFields (as in "ts_whole_table = GetAllFields(acPointType);").  If GetAllFields doesn't do SQL UPDATE, INSERT or DELETE statements, -1 is the documented value that ExecuteNonQuery returns. What does this call to GetAllFields do? If you replace GetAllFields by a call to SQL Server that does a SQL INSERT/UPDATE/DELETE statement, it should return a value other than -1.

    Hope this helps, sorry about reading too much into the original problem report.

    Cheers, Bob


    Tuesday, May 15, 2012 4:26 AM
    Moderator
  • Odd,

    I posted a response before leaving work last night but it is not here!

    Bob,

    Listen.  Honestly.  My source code file is dated 2006.  It hasn't changed in 6 years!

    		myC->Connection->Open();
    		iret = myC->ExecuteNonQuery();
    		if ( iret > 0 )
    		   iret = (int)myC->Parameters["@RowCount"]->Value;
    		myC->Connection->Close();

    Used to work!!!!!!!!!!!!!

    The calling code and the stored procedure have not changed!!

    There must have been some SQL automated update that changed things!!

    And yes, the stored procedure does multiple UPDATE commands.


    BDM

    Tuesday, May 15, 2012 1:01 PM
  • If you can trace into the called GetAllFields(acPointType); function, confirm that it does updates, confirm that those updates do update rows, I'd report it as a bug on Connect. Make sure you have the version of SQL Server (old and new , if possible), and CLR (on both client and server machines) if you think the problem stems from a system update.

    In the meantime, you might have better results by moving the code for GetAllFields (that does the updates) inside the main SQLCLR stored procedure, vciWriteDataCS, and see if that helps.

    Cheers, Bob

    Tuesday, May 15, 2012 8:29 PM
    Moderator
  • Hi Bob,

    GetAllFields is inside the main SQLCLR stored procedure, vciWriteDataCS.

    As stated above, all the UPDATES do get performed.

    It is just that the ExecuteNonQuery() returns -1 as per:

    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.

    It is just that it USED to return a positive number ( other than the parameter passed by the "return" function ).

    I would say it now works as it is documented. But when I started this post, I hadn't yet found the documentation.

    Anyway, my code no longer cares what  ExecuteNonQuery()  returns, just looks at the "return" paramter.

    Boyd


    BDM

    Tuesday, May 15, 2012 8:36 PM
  • Boyd

    To confirm you are not crazy, we just ran into the exact same problem. Code that has been working for the last 6 months suddenly stopped working last week when we recompiled the application.

    It turns out that all of our stored procedures specified "SET NOCOUNT ON". This seemed to have no affect on our Insert, Update and Delete procedures until last week. Now all of a sudden every procedure called by ExecuteNonQuery that specifies SET NOCOUNT ON returns a -1 under all circumstances.

    The strange thing is calling the stored procedure through SQL in Management Studio returns the expected results WITH or WITHOUT "SET NOCOUNT ON".

    The fix is to simply remove the NOCOUNT setting in the procedure and ExecuteNonQuery works as expected. The problem seems to have been caused by a recent ADO update. Mere speculation as we are still trying to confirm this.

    UPDATE:

    Apparently a bug in ADO was allowing an "undocumented" use of ExecuteNonQuery that would return a value from a stored procedure. The result value of calling ExecuteNonQuery is supposed to be the rows affected. Using ExecuteNonQuery to call a stored procedure should always result in a return value of -1 (as we are seeing now). The only way to get values back from a stored procedure (any value not just declared output parameters) is through the parameter list. Set the parameter Direction property to ParameterDirection.ReturnValue to obtain the return result of the stored procedure.


    • Edited by Todrich Wednesday, May 30, 2012 7:50 PM
    Wednesday, May 30, 2012 3:51 PM
  • If we remove  the "SET NOCOUNT ON" then the stored procedure will return the row count of the entire result set instead of the inserted values ( in our case we have some more statements whose result was also added to the count of inserted rows) and hence was returning wrong count of rows.
    Tuesday, June 26, 2012 3:43 PM