July 15, 2005 at 1:37 pm
Hello. This mess came up again recently where I needed to get this working once and for all, I figured I pass on the solution and the unsatisfying conclusion
Using SQL Profiler and moving temporary tables into SQL queries I finally was able to get rid of the issue. The new problem was that the proedure was taking about a minue and twenty seconds to run.
Going back to the original code I found that if I moved the temporary table queries into new "sub" stored procedures that the processing time was faster. Unfortunately I received a new error message saying that I couldn't have nested INSERT-EXEC statements (news to me and something that just doesn't sound reasonable). I was able to get around this by moving the sub stored procedure calls into the top stored procedure. This move reduced the overall processing time to about thirty seconds and I'm willing to live with that.
I still have a bitter taste, though, as my generate primary payroll tilte and primary department stored procedures now have prelimiary stored procedures that have to be executed before they can run. This means any call to those two stored procedures needs to know about these pre-steps.
Despite having written multiple web sites in .NET C# and a willingness to work with OO Programming best practices, I haven't had any desire to use anything but T-SQL when Yukon comes out. This experience, unfortunately, is making me rethink my position.
Everett Wilson
ewilson10@yahoo.com
September 13, 2006 at 11:15 am
Hello. Just a quick note that I did find a solution to the problem. Turns out the direct problem is nested stored procedures. The soltuon at:
http://www.sommarskog.se/share_data.html
under sharing a temp table explains how to work around the problem.
Everett Wilson
ewilson10@yahoo.com
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply