January 26, 2018 at 11:42 am
A long troubleshooting exercise for a IVR web service has led us to resort to capturing data being sent back from the SQL server. The reason is, web service calls are encrypted and tracing on web service side using WireShark and Fiddler are not helping much. Wireshark doesn't reveal encrypted data. Fiddler does not reveal data between web service and SQL server. Fiddler captures data between web service and clients, which does not help.
The issue we are having is, somewhere in the work/call flow of an IVR session of CRM system, an very old application-level session ID is being used over and over. The system is supposed to generate a new session ID every time a call comes in. We have tried restarting the IVR and web service but that did not help. We also searched registry hive but this CRM doesn't use registry at all. To cut a long story short, we are trying to see if this old session ID was being sent from the database to the web service - to determine whether there is hidden business logic at play, or, there is a cache on the web server that is storing this old session ID. Unfortunately this is a leased solution and all stored procedures are encrypted. Vendor is trying their best not to help in this case.
My question is, is there a way to capture all buffer output from a SPID on the SQL Server side? The call flow involves only 8 steps. I have tried to watch the call flow from Profiler and run DBCC Outputbuffer on the SPID at each step. But the output is not showing the value of this session ID. So I thought maybe I should find a way that would guarantee that all output from this SPID is captured. Hence the question.
Thanking in advance.
January 26, 2018 at 11:55 am
Have you tried capturing sql_batch_completed and rpc_completed (maybe even toss in module_complete) using Extended Events? That's usually what I use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2018 at 2:03 pm
Grant Fritchey - Friday, January 26, 2018 11:55 AMHave you tried capturing sql_batch_completed and rpc_completed (maybe even toss in module_complete) using Extended Events? That's usually what I use.
Sorry but I haven't tried that. I didn't think the extended events would reveal data being sent back to the calling client. I'll try it out. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply