title: SQL Server stored procedure no statements run when from RPC
body: <p>I have a SQL Server 2000 database with a stored procedure that deletes a row from a specific table, given its id. When I call the stored procedure from VB.NET, it does not delete the row, but running the same script directly on the database via SSMS, it works.</p>
  
  <p>Here's my chain of events:</p>
  
  <ol>
  <li>Start SQL Server Profiler to watch all calls to the database. I have
  it setup to track when stored procedure starts, completes, and even
  on SQL statements start/complete within that stored procedure.</li>
  <li>Call stored procedure via VB.NET dll.</li>
  <li>Stop the profiler trace to avoid excessive data to dig through.</li>
  <li>Select from table, and see that the row still exists.</li>
  <li>View the profiler trace, which only shows RPC:Starting, SP:Starting, RPC:Completed. No inner statements are traced, which verifies why the row wasn't deleted since the delete statement never fired.</li>
  <li>Copy/paste the EXEC call directly from the RPC:Starting trace entry from when it was called via VB.NET, into SQL Server Management Studio query window pointed to the same database with same credentials.</li>
  <li>Start profiler again.</li>
  <li>Execute EXEC statement from bullet 6 in SSMS.</li>
  <li>Stop profiler.</li>
  <li>Select from table, and see that the row GOT DELETED like it should.</li>
  <li>View the profiler trace, which shows SP:Starting, all statements starting/completed including the DELETE statement, and SP:Completed.</li>
  </ol>
  
  <p>Why would running it via RPC make it not execute any of the statements in the proc, but running directly acts as it should?</p>
  
  <p>EDIT: Below is my VB.NET code. This is the same code we use in over 100 other places:</p>
  
  <p><code>Dim paramRowID As New SqlParameter("@RowID", sRowID)</code>
  <code>Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(oConn, "spDeleteRow", paramRowID)</code></p>
  
  <p>See SqlHelper source <a href="http://www.sharpdeveloper.net/source/SqlHelper-Source-Code-cs.html" rel="nofollow">here</a>.</p>
  
  <p><strong>EDIT: I hate myself right now. :) SQL threw an exception "nvarchar is incompatible with image" about another parameter that I was passing NULL to. SSMS didn't worry about the type, but VB.NET did since I didn't explicitly tell it that it was of type image. Once I defined that param, it worked. I wish profiler would have told me there was an error though.</strong></p>
  
  <p>Any help would be appreciated,</p>
  
  <p>Greg</p>