SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure with non-qualified objects


Stored Procedure with non-qualified objects

Author
Message
DavidC-WNY
DavidC-WNY
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
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.
RBarryYoung
RBarryYoung
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18984 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18984 Visits: 9518
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."
DavidC-WNY
DavidC-WNY
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
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.
RBarryYoung
RBarryYoung
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18984 Visits: 9518
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search