Problem description:
An application (programming code) that uses SQL procedure with parameters. The procedure manipulates data on some procedure’s conditions depending on input parameters. At the same time, there is no option to debug the code to check the input parameters and which conditional part of the procedure will be executed for a problematic case.
Solution:
Microsoft SQL Server Management Studio and its tool ‘SQL Server Profiler’ can help with identifying the procedure’s parameters at the execution time.
Go to Tools -> SQL Server Profile in SSMS
Connect to SQL Server (Database)
In ‘Trace Properties’ window, ‘General’ tab, update ‘Trace name:’. Or leave as default.
In ‘Trace Properties’ window, ‘Events Selection’ tab, update filters. Pay attention to Column Filters (Edit Filter), you may need to remove filters if they were defined before.
Once ready – click ‘Run’
it starts running …
Go to the application that uses the procedure and perform some actions where the issue happens (which starts the sql procedure). Then Pause the Profiler.
In our case there is a SQL Procedure below. It has 19 parameters and we are not sure what are values at execution time.
Let’s go to SQL Server Profile (!! the top of the profiler window, click on the first row in the window) and find our procedure (just CTRL + F)
result
This approach helps me much when I’m working with legacy code/environment and there are no debug options.
Cheers.
