1

Is there something like a "debug attach" for the SQL debugger? I have a middleware tool (BizTalk) that is calling a stored procedure and would like to debug it.

As alternative I tried adding statement to insert into a trace table, but BizTalk rolls back the transaction when it fails. I've added print statements, but no way to see them when running externally. I've been using SQL_profiler which helps a little, but cannot see values of variables.

Per @Jeroen comment, I tried sp_trace_generatedevent, but only see this in the profiler. I have filter on my DBName, and have all userConfigureable events turned on. enter image description here

NealWalters
  • 17,197
  • 42
  • 141
  • 251
  • Why not just create a new query in SQL Server, EXEC the stored proc inside the query and pass in the values which you would pass from BizTalk, then run it in debug mode and step into your stored proc? – Ryan Wilson Apr 18 '18 at 14:00
  • 1
    Debugging a live SQL Server is close to black magic. I'm sure it can be done... but I've never seen anyone do it and live to tell the tale. Consider hooking up a profiler and capturing the `RPC:Completed` events, which will give you the parameter values. That and a backup get you a long way towards debugging a tricky situation. – Jeroen Mostert Apr 18 '18 at 14:00
  • Is there a way to store the variables in a temp table? – JosephDoggie Apr 18 '18 at 14:00
  • The parameters are 5 user defined table types, each with maybe 10-25 columns, so I think it would take quite a will to set up with my own Exec statement. Temp table gets rolled back when BizTalk detects abort, as I'm getting a cast error somewhere. – NealWalters Apr 18 '18 at 14:04
  • Can't you catch that in a profiler trace? – MJH Apr 18 '18 at 14:05
  • I have a profiler trace now with RPC:Compelted SP:StmtCompleted & SQL:BatchCompleted. But I can't figure out how to show variable values, tried Print AtDebugMessage, Select AtDebugMessage (couldn't put at-sign in comment) – NealWalters Apr 18 '18 at 14:08
  • Include SP:StmtStarting (if the statement errors, it will not complete) and whatever error events you think will help. – MJH Apr 18 '18 at 14:11
  • Profiler cannot capture variable values. The idea is that you capture the way the stored procedure is invoked, and the database state, and use that to replay what's going on. If you really want to capture it on the fly, and you have the freedom to change the sprocs, you can use [`sp_trace_generateevent`](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-trace-generateevent-transact-sql) to produce a custom trace event (capture it as a `UserConfigurable:x` event). – Jeroen Mostert Apr 18 '18 at 14:12
  • I can change stored proc; added the sp_trace_generateevent, and checked UserConfigureable:0 but don't see any UserConfigureable events in the profiler. One at the top should always run. – NealWalters Apr 18 '18 at 14:20
  • Cannot reproduce; check your event filters (a stray column filter could exclude it) and/or your syntax. If I run `sp_trace_generateevent 82, N'Hello, world!'` in a separate window, it's captured as a `UserConfigurable:0` event. – Jeroen Mostert Apr 18 '18 at 14:24
  • Added screen shot; not seeing UserConfigureable:0 event, only SP:StmtStarting and Completed for the sp_trace_generateevent – NealWalters Apr 18 '18 at 14:43
  • The problem is that you are invoking `master..sp_trace_generateevent`, which registers as being in the `master` database. Never use the `master` prefix for invoking system stored procedures; the `sp_` prefix uses special logic that allows them to resolve in any database for a reason (and for that same reason, user procedures should never use `sp_`). You can use `sys.sp_trace_generateevent` if it makes you feel better, but don't include `master`. – Jeroen Mostert Apr 18 '18 at 14:50
  • I later tried it without master. And same result. – NealWalters Apr 18 '18 at 14:52
  • Then I have to fall back on "can't reproduce/#worksforme". :-P I could reproduce not seeing the event when invoked through `master`, but not with the database name removed. – Jeroen Mostert Apr 18 '18 at 15:33
  • Starting a new thread to get sp_generateevent to work: https://stackoverflow.com/questions/49923263/getting-sp-generateevent-to-work-in-a-stored-procedure – NealWalters Apr 19 '18 at 14:11
  • I tried Debugging in Visual Studio 2013, and attaching to BizTalk Processes, but it didn't walk into the stored proc at all. https://support.microsoft.com/en-us/help/316549/how-to-debug-stored-procedures-in-visual-studio-net – NealWalters Apr 19 '18 at 20:08
  • The plot thickens - I think try/catch is lying to me: https://stackoverflow.com/questions/49930243/can-sql-begin-try-catch-be-lying-to-me – NealWalters Apr 19 '18 at 21:10

1 Answers1

0

sp_TraceGenerateEvent is useful to write data to the profiler. (Example usage here: https://blogs.technet.microsoft.com/fort_sql/2012/06/21/capture-custom-events-in-profiler-for-troubleshooting/)

Just beware that when BizTalk calls the stored proc, it has two passes, once with FmtOnly=On, and the second (the real execution) with FmtOnly=Off. You might not reach the second pass if you have certain errors on the first pass. Getting sp_tracegenerateevent to work in a stored procedure

When FmtOnly=On, the Trace Profile can be confusing, leading you to believe it's running when it's really just checking for what data/columns will be returned. Can SQL Begin Try/Catch be lying to me (in the profiler)?

NealWalters
  • 17,197
  • 42
  • 141
  • 251