SQLTeam.com | Weblogs | Forums

What called a stored procedure

sql2008r2
#1

My internet queries are not the best and I only get how to execute a stored procedure when I query.

Anyway, how do I tell what called a stored procedure? I know that OBJECT_NAME(@@PROCID) will return the stored procedure name however I want what called it. Is this possible?

Thanks
DJJ

#2

No, there's no direct way to do that in SQL Server. You'd have to pass in the caller name/id yourself somehow: a parameter, a table (temp or not), CONTEXT_INFO(), or whatever other method.

#3

You could run a trace or profiler (with filters if required to eliminate noise) and look at the various columns in the profiler output - such as Application Name, NTUserName, Login Name etc. to get an idea of what/where the calls are coming from. What you see may depend on how the caller is connecting. For example, the Application Name is something a caller can set in the connection string if they choose to. If they have not set it, it might return you something generic.

If you need to capture/store that information in a production environment regularly, the stored proc would have to pass in the caller name as a parameter.

#4

Thank you for the replies.

What I have is a procedure we know runs, but not what is executing the procedure.

I really hate working with the profiler as if you get it wrong you could have tons of information produced.

Thanks again,
DJJ

#5

DROP / Rename the procedure and see what "breaks" as a consequence ?

... I'll get my coat ... :slight_smile:

#6

My first suggestion.

#7

I thought of one other possible way to pass in the calling app -- setting application name when making the SQL connection and then using APP_NAME() within the proc.

#8

Thanks for the reply.

We actually do that for some of our processes. However, this is one that we did not, and since I do not know what is calling it, I cannot add to the connection.

#9

Drop it and listen for the first, "HEY!!! WHAT THE ???" :grin:

Altering the procedure to add a SELECT USER_NAME() and logging the results might turn up some useful info.

#10

:slight_smile: Its what we call the "Scream test" :sunglasses:

#11

:yak:

All (as in "so close to zero exceptions as makes no odds") of our SProcs start with a SessionID value. Each SProc logs the SessionID and (a concatenated list of) the values of all its parameters.

Makes solving this type of thing a lot easier. Our APPs are all Web Based, so no opportunity to walk round and look over a user's shoulder! and we proactively review logged data for sessions which have encountered an error - we find a lot of good stuff in there :slight_smile: Even in a closed-shop organisation users are inclined to put-up-with issues, rather than reporting them, and then the APP just gets negative-PR ("B****dy thing doesn't work")