StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server stored procedure no statements run when from RPC
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload