May 8, 2009 at 1:59 pm
Hello,
We are running SQL Server 2008 using Windows Authentication. I have a couple of stored procedures created in the dbo schema for reporting. The users are executing the SPs from a VB front end. The first sp executed is to create a tmp table in the logged on users default schema. No problem, the table is created. The next stored procedure is called to insert into that table and an error is thrown stating it can't find the object of the tmp table. Looking in SMS, the table is there in the correct database, qualified by the correct username. The stored procs just cannot insert into or select from that table. I found a small blurb about non qualified objects referenced in a stored proc are only searched for in the stored proc owners schema. We've tried Execute AS with various options without success. We can't hardcode the qualifier in the stored procs because we want several users to run the report at the same time. Is there anyway around this situation other than rewriting the report process?
Thanks in advance for your help.
May 8, 2009 at 2:12 pm
The problem is that for the most part, objects get bound at compile-time, not run-time. True #temp tables get some kind of workaround for that, but a table like YourSchema.tmp, is probably not bindable at run-time from static SQL. One possible solution that will probably work is Dynamic SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 2:31 pm
Hmmm, actually. now I am not sure that my compile-time/run-time binding explanation was correct. However, I still do think that dynamic SQL would work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 2:33 pm
Dynamic SQL does indeed work. However, the report stored proc cannot be executed in that manner. It has too many statements. We were hoping for a system solution rather than a programming solution.
May 8, 2009 at 3:39 pm
davidc (5/8/2009)
Dynamic SQL does indeed work. However, the report stored proc cannot be executed in that manner. It has too many statements.
Why do you say that? I've done reports in dynamic SQL with over 100,000 lines without any real problem.
We were hoping for a system solution rather than a programming solution.
I don't know of any non-code solution and I doubt that there is one. T-SQL just isn't very flexible about this, post-compile.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply