2

I have a huge SQL server database. I want to re-write an application. For that I want to reuse the database which has many tables, views and stored procedure. I am using SQL Profiler tool to map the application functionality to database. In Profiler, I am capturing 

SP:Starting 
SP:Completed 
SP:StmtStarting 
RPC:Completed
SQL:BatchCompleted 
SQL:BatchStarting

In my Profiler trace I have captured an RPC:Completed event class and the textdata contains

exec sp_executesql N'Select Some query' @var=N'value'".

What is sp_executesql and how can I know the exact name of the stored procedure called?

EzLo
  • 13,780
  • 10
  • 33
  • 38
richa verma
  • 247
  • 2
  • 13
  • 1
    *"What is sp_executesql"*: [sp_executesql](https://www.google.com/search?q=sp_executesql). *"how can I know the exact name of the stored procedure called"* In your example it wasn't an SP, it was a `SELECT` statement: `N'Select Some query` – Thom A Nov 13 '19 at 11:18
  • `RPC:Completed` fires not only for stored procedures but for any query parameterized by the client. Note that the `exec sp_executesql` is synthesized by the profiler: the client need not actually call `sp_executesql` explicitly for this to appear, it rewrites the RPC call to a form you could execute in your query window. Note also that there's a difference between the client issuing a (parameterized) `EXEC procname` query, and the client directly calling the stored procedure with parameters (the former will show `sp_executesql`, the latter will not). – Jeroen Mostert Nov 13 '19 at 11:25
  • @Larnu, So if it wasn't a SELECT statement then SP name would have come? – richa verma Nov 14 '19 at 04:28
  • @JeroenMostert, So the clients' parameterized query will not be stored in the database, It is only in their Application code? – richa verma Nov 14 '19 at 04:34
  • @JeroenMostert, _Also that there's a difference between the client issuing a (parameterized) EXEC procname query, and the client directly calling the stored procedure with parameters (the former will show sp_executesql, the latter will not)._ How will I capture the latter SP – richa verma Nov 14 '19 at 04:37
  • Yes, query texts are only in the application's code. And you capture the SP with `RPC:Completed`, but the client may never issue a sproc call (`CommandType = CommandType.StoredProcedure`, or whatever the moral equivalent on their end is). Try filtering out `sp_executesql` for the object name to see if any direct calls are made. – Jeroen Mostert Nov 14 '19 at 07:40

1 Answers1

2

In your trace you need to add additional column ObjectName which show exact sproc name when you click on RPC:Completed event. ObjectName can be seen when you check 'Show all columns'

enter image description here

Harshad Vekariya
  • 972
  • 1
  • 7
  • 28