title: Calling a stored procedure from a third party app but it does not execute, but no errors are returned. What can I do to troubleshoot?
body: <p>I have a SQL Server stored procedure being called remotely from a third party application. The stored procedure executes properly when called locally, but does not execute when called remotely. By locally I mean, if I right click and choose Execute; or if I enter <code>exec sp_EBRRecipeMove</code> from a query window.  Even though it doesn't execute when called remotely, no errors are returned. </p>
  
  <p>Backstory: this stored procedure is part of a sequence of stored procedures that gets called from a customer's API. API calls <code>sp_1</code>. <code>sp_1</code> calls <code>sp_2</code>, <code>sp3</code>, and <code>sp_4</code>. If there are no errors returned, <code>sp_1</code> calls <code>sp_EBRRecipeMove</code>. All of this in an effort to error check and move setpoints from one database to another.   </p>
  
  <p>I have sprinkled in <code>RAISERROR</code>'s throughout this script to help me understand how far it gets through the script. SQL Log indicates that it is progressing through the script, yet the values never update and the values from the original tables never migrate to the destination tables.  </p>
  
  <p>Thus far, the end user's application (PHP I believe) causes this behavior, and a simple Java app to call the stored procedure causes it also. I can execute this manually/locally without issue. I won't have access to the PHP code.</p>
  
  <p>The end user's application does call other stored procedures from the same database with success.</p>
  
  <p>In an effort to reduce the chance of a DB permissions issue, I've given the user that is used to connect remotely sysadmin access.</p>
  
  <p>I have verified that all of instances of the <code>RecipeID</code> column in the database and associated variables are all <code>INT</code>. In the destination table, this column is <code>Int NOT NULL</code>, but in the other (EBR) tables I allow Null values since the value may not be populated.</p>
  
  <p>UPDATE: I've since verified that the third party app is not calling the stored procedure via transaction.  From the Oracle Database they are using, it is a simple Call.</p>
  
  <p>Using the hints already posted, I did significant troubleshooting as was able to successfully execute the stored procedure by calling it from a third party Java App.  However, when implementing the changes on the customer's setup, the commands do not execute.  I commented out the code and tried executing 1 command at a time with the Try... Catch around each.  Try... Catch is not returning any errors, but the commands are not executing.  Is there a special permission or setting in the tables that will allow remote execution of Insert, Delete, and Update commands?  Any help or hints is greatly appreciated!</p>
  
  <p>UPDATE2:  From another stackoverflow post related to Deletes by @Remus Rusanu, I can provide the following information regarding the cases why Deletes or other commands might not execute and hopefully post my amended code tomorrow - </p>
  
  <p>•the DELETE does not execute (logic flow control issue, it takes the other IF branch...)
  [This is not the case, I have RAISERRORs just before and just after the command that log reasonable information given the inputs (except that the Delete didn't happen)]</p>
  
  <p>•the DELETE executes but does not affect any rows (WHERE clause or JOIN problem)
  [This is not the case, or at least seemingly, since I can take the Delete...Where and put it in a query and execute.  I'm not using any Joins.]</p>
  
  <p>•the DELETE executes and errors and the error is silently suppressed (error handling problem)
  [This should not be the problem as I commented out the entire code except 1 Delete...Where statement and put a Try...Catch directly around it]</p>
  
  <p>•the DELETE executes but the effect is rolled back (transaction management problem)
  [This is not the case because I am not using transactions and the calling client is not using a transaction.]</p>
  
  <p>•the DELETE executes but the effect is compensated by app (application logic problem)
  [I may not understand this properly, but I'll offer this: This shouldn't be the case since I commented out all surrounding code.  Also, the client is using a simple CALL command from Oracle.]</p>
  
  <p>•the DELETE does not execute because a previous error stops the request (again error handling problem)
  [This shouldn't be the case because I can see that the RAISERROR logs reasonable information just before and just after the Delete statement.]</p>
  
  <p>•the DELETE executes but your verification is incorrect (PBCAK)
  [This is not the case since the row is not deleting.  A Select statement of the table indicates the row is still intact after executing the stored procedure.]</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_EBRRecipeMove]
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      Declare @Version smallint;
      Declare @CheckedOutID tinyint;
      Declare @AbleToSave bit;
      Declare @EquipName varchar(80);
      Declare @RcpName varchar(80);
      Declare @EquipID tinyint;
      Declare @status int;
      Declare @prRcpName varchar(80);
      Declare @prVersion smallint;
      Declare @tmprows int;
      Declare @recipeid int;
      Declare @tmpid int
      Declare @groupid tinyint;
      Declare @rcpcomment varchar(200);
      Declare @prrcpcomment varchar(200);
      Declare @savedatim datetime;
      Declare @saveuser varchar(80);
      Declare @approvaldatim datetime;
      Declare @approvaluser varchar(80);
      Declare @deactdatim datetime;
      Declare @deactuser varchar(80);
      Declare @existingRecID int;
      DECLARE @ErrorSave int
          SET @ErrorSave = 0
  
  
      -- Select recipe info from EBRRecipeHeader table
      SELECT @RcpName=recipename
      FROM EBRRecipeHeader
  
      -- Select recipe info from prRecipeHeader table
      SELECT @prRcpName=RecipeName
      FROM prRecipeHeader
      WHERE RecipeName = @RcpName 
  
  
          set @Version = 1;       
          set @status = 3; 
          set @groupid = 1;
          set @savedatim = CURRENT_TIMESTAMP;
          set @saveuser = 'EBR'
          set @approvaldatim = CURRENT_TIMESTAMP;
          set @approvaluser = 'EBR'
          set @CheckedOutID = 0;     
  
  
          if @@ROWCOUNT = 0
              begin
                  INSERT INTO dbo.prRecipeHeader(
                      GroupID,
                      Status,
                      RecipeName,
                      Version,
                      RecipeComment,
                      SaveDateTime,
                      SaveUser,
                      ApprovalDateTime,
                      ApprovalUser,
                      DeactivateDateTime,
                      DeactivateUser,
                      CheckedOutID
                      ) VALUES (
                      @groupid,
                      @status,
                      @RcpName,
                      @Version,
                      @rcpcomment,
                      @savedatim,
                      @saveuser,
                      @approvaldatim,
                      @approvaluser,
                      @deactdatim,
                      @deactuser,
                      @CheckedOutID        
                      )
  
                  end 
  
          ELSE 
              begin
  
                  DELETE FROM prRecipeHeader
                  WHERE RecipeName = @RcpName 
  
                  INSERT INTO dbo.prRecipeHeader(
                      GroupID,
                      Status,
                      RecipeName,
                      Version,
                      RecipeComment,
                      SaveDateTime,
                      SaveUser,
                      ApprovalDateTime,
                      ApprovalUser,
                      DeactivateDateTime,
                      DeactivateUser,
                      CheckedOutID
                      ) VALUES (
                      @groupid,
                      @status,
                      @RcpName,
                      @Version,
                      @rcpcomment,
                      @savedatim,
                      @saveuser,
                      @approvaldatim,
                      @approvaluser,
                      @deactdatim,
                      @deactuser,
                      @CheckedOutID        
                      )
  
              end --if &lt;&gt;0
  
  
      select @recipeid = RecipeID from prRecipeHeader where prRecipeHeader.RecipeName = @RcpName;
  
  
  declare @recipeidText as varchar(8)
  set @recipeidText = convert(varchar(8), @recipeid)
  RAISERROR (@recipeidtext,10,1) WITH LOG     
  RAISERROR ('RecipeID before Globals',10,1) WITH LOG  
  
  
  
      UPDATE  EBRRecipeGlobals    SET EBRRecipeGlobals.recipeid = @recipeid;
  
  set @recipeidText = convert(varchar(8), @recipeid)
  RAISERROR (@recipeidtext,10,1) WITH LOG     
  RAISERROR ('RecipeID before global insert',10,1) WITH LOG  
  
  
  
      INSERT INTO prRecipeGlobals SELECT * FROM EBRRecipeGlobals where EBRRecipeGlobals.recipeid = @recipeid;
      DELETE From EBRRecipeGlobals Where EBRRecipeGlobals.recipeid = @recipeid;
  
  set @recipeidText = convert(varchar(8), @recipeid)
  RAISERROR (@recipeidtext,10,1) WITH LOG     
  RAISERROR ('RecipeID after globals insert, before steps',10,1) WITH LOG  
  
  
      UPDATE EBRRecipeSteps SET EBRRecipeSteps.RecipeID = @recipeid;
  
  set @recipeidText = convert(varchar(8), @recipeid)
  RAISERROR (@recipeidtext,10,1) WITH LOG     
  RAISERROR ('RecipeID before steps insert',10,1) WITH LOG  
  
      INSERT INTO prRecipeSteps SELECT * FROM EBRRecipeSteps where EBRRecipeSteps.RecipeID = @recipeid;
      DELETE From EBRRecipeSteps Where EBRRecipeSteps.RecipeID = @recipeid;
  
  
  
      DELETE FROM EBRRecipeHeader 
  
  end
  </code></pre>