August 22, 2006 at 2:42 pm
I've built some procs that built parameterized queries using sp_executesql. Essentially:
EXEC dbo.spCallDataProcs --> calls one of many sp_executesql statement based on input paramters. All sp_executesql strings fully qualify tables (well, kinda...I use the owner name but not server or database) as well as the calling proc is always prefixed with "dbo."
I was expected the UID for the compiled plans in syscacheobjects to show (-2) - indicating that all users can use the plan. Instead the value is (1), for "dbo" - which is me of course.
I was hoping that multiple apps and multiple users would be able to execute "EXEC dbo.spCallDataProcs @parm1, etc.", thus getting individual execution contexts, but otherwise ALL using the same compiled plans associated with the sp_executesql queries.
Are my expectations incorrect?
August 23, 2006 at 4:28 am
I never quite got my head around the -2 vs 1 , but if all your object calls are prefixed dbo. it should be fine. My testing showed dbo owned objects were fine.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply