1

Like many, I can't seem to get the settings right to hit a breakpoint in a MSSQL Stored Procedure! After following instructions from a half-dozen articles, here is my setup:

  • Visual Studio 2015 Enterprise
  • .\BACKEND .. SQL Server 2014 Developer
  • I am sysadmin, as is the user (app pool user) running this content.
  • The database project was added to this solution
  • In database project properties > Debug > the Target Connection String was updated to point to the .\BACKEND database.

In SQL Server Object Explorer, I've created a connection to .\backend, enabled debugging, and set a breakpoint..

In the project from where the sproc is called, project properties > SQL Debugging is set.

enter image description here

enter image description here Breakpoint Set

The call from the project:

Database.ExecuteSqlCommand("EXEC @procResult = [dbo].[spOnlineSearchRegistrationLoad] @RegCode, @RemainingPayments OUTPUT", regCodeParam, remainingPaymentsParam, procResultParam);

On every attempt, the sproc is called but breakpoint is never hit. In Visual Studio, the breakpoint is hollow, indicating it WILL NOT be hit. What else can I try?

enter image description here

Robert Kerr
  • 1,291
  • 2
  • 17
  • 34
  • Why don't you just run it in Enterprise manager? – Liam Mar 24 '16 at 17:12
  • Running it SSMS is always successful. The issue I'm troubleshooting only occurs when calling the sproc from this code. – Robert Kerr Mar 24 '16 at 17:14
  • It appears [there are many, many hoops to jump though](http://stackoverflow.com/a/34182271/542251) to get this working. Likely why I've never bothered... – Liam Mar 24 '16 at 17:15
  • yup that was one of the sources I followed. – Robert Kerr Mar 24 '16 at 17:18
  • If you run it from VS or in SSMS using the same parameters... the only thing that can be different is the User executing the code. Which is alleviated by logging into SSMS as the user in question (the IIDentity of the DotNet code) – granadaCoder Mar 24 '16 at 18:52
  • 1
    Not an answer as such, but consider just hooking up SQL Profiler and tracing `RPC:Starting` events. This will capture the sproc call along with the parameter values, allowing you to replay it on the database end. Obviously this isn't good enough if you really, really need to debug one particular call at one particular instance in the lifetime of the database, but if you're debugging in the first place you should be able to make things reproducible anyway. MS has unfortunately not yet succeeded in making T-SQL debugging a delight to use that just works. – Jeroen Mostert Mar 24 '16 at 20:38
  • 1
    @granadaCoder: the user is, in fact, not the only thing that will be different -- various `SET` options may also not have the same value when you're executing code from .NET as opposed to directly in T-SQL, most notably `ARITHABORT`. This can bite you when code is "fast" in one place but "slow" in the other -- different options generate different execution plans, even if the option has no actual effect on your particular query. – Jeroen Mostert Mar 24 '16 at 21:34

0 Answers0