<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Programming
»
General
»
Stored Procedure with non-qualified objects
Stored Procedure with non-qualified objects
Rate Topic
Display Mode
Topic Options
Author
Message
DavidC-WNY
DavidC-WNY
Posted Friday, May 08, 2009 1:59 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, October 26, 2012 9:28 AM
Points: 82,
Visits: 27
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.
Post #713327
RBarryYoung
RBarryYoung
Posted Friday, May 08, 2009 2:12 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #713334
RBarryYoung
RBarryYoung
Posted Friday, May 08, 2009 2:31 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #713352
DavidC-WNY
DavidC-WNY
Posted Friday, May 08, 2009 2:33 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, October 26, 2012 9:28 AM
Points: 82,
Visits: 27
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.
Post #713353
RBarryYoung
RBarryYoung
Posted Friday, May 08, 2009 3:39 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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.
-- RBarryYoung
,
(302)375-0451
blog:
MovingSQL.com
, Twitter:
@RBarryYoung
Proactive
Performance Solutions, Inc.
"Performance is our middle name."
Post #713396
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
