Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure with non-qualified objects Expand / Collapse
Author
Message
Posted Friday, May 8, 2009 1:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Friday, May 8, 2009 2:12 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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
Posted Friday, May 8, 2009 2:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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
Posted Friday, May 8, 2009 2:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Friday, May 8, 2009 3:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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 »

Add to briefcase

Permissions Expand / Collapse