Friday, November 4, 2011
How to debug/troubleshoot a SP in production environment?
Here some usefull SQL functions that you can use in the SPs to achieve the above:
1) APP_NAME() - Returns the application name for the current session if set by
the application. You can set it in the connection string of the application by
including this: "Application Name=AppName".
By using this function you can change a SP to do X if it executed from Server A
and to do Y if it executed by Server Y or if it executed via the SSMS.
Simply set the application name for different value in Server X and Y.
By using this function you can also upload SPs changes without any downtime
and with full backward compatibility.
2) CONNECTIONPROPERTY('client_net_address') - Returns the Client IP address.
3) CONNECTIONPROPERTY('local_net_address') - Returns the IP address of the server.
For example, if you connect to production SQL server from the SSMS in QA/DEV
SQL Server then it will return the IP of that QA/DEV SQL Server machine.
By using these functions listed above you can add logic (If or CASE statements)
to the SP and you can troubleshoot/debug the SP without running the SQL Profiler.
SELECT APP_NAME() AS Application_Name,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('client_net_address') AS client_net_address