June 18, 2008 at 9:30 am
Hello
We're using SQL 2005.
We're experiencing an issue whereby a stored procedure being called from a VB.net application errors, but that error isn't being caught by the TRY CATCH within the stored procedure. This leaves an open transaction for the user and any subsequent transactions they perform don't get committed. Upon closing the application everything they have done since the error wasn't caught is lost.
There are hundreds of stored procedures and testing every one of them will take ages, is there an easy way to detect which procedure is at fault and is there a way to inform the user via the application that something is wrong?
We found the following code simulates the same symptoms (in our DB there is a Users table containing information about our users):
BEGIN TRY
BEGIN TRAN
SET NOCOUNT ON;
SELECT DISTINCT IDENTITY(INT,1,1) AS myIndex, Forename, Surname, Phone, CreatedDate
INTO #myTableForTesting
FROM dbo.Users
SELECT DISTINCT IDENTITY(INT,1,1) AS myIndex, Forename, Surname, Phone
INTO #myTableForTesting2
FROM #myTableForTesting
ORDER BY Email
DROP TABLE #myTableForTesting
DROP TABLE #myTableForTesting2
COMMIT
RETURN 0
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN 999999
END CATCH
When it tries to do the 'ORDER BY Email' it errors with the following message "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" and does not finish executing the TRY block, nor does it process the CATCH block.
Does anyone know how to detect this error and recover from it? Any help would be most appreciated.
June 18, 2008 at 9:33 am
Try/Catch can only catch procedural errors. It can't handle compilation errors. That's what you're running into here.
You'll need to catch the error from the code calling the proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 3:24 am
Thank you for clarifying. We have changed the app to handle this so hopefully will know which SP is causung the issue soon.
June 19, 2008 at 8:18 am
Hi
On a slight tangent... 🙂 i try to avoid using the select.. into #tmptable syntax due to locking on the tempdb while it creates and inserts the rows. I usually either create the temp table explicitly 1st and then run and insert or I add a where clause of 1 = 0 on the original select..into as this retains the actual field lengths and then I run the insert.
KG
June 19, 2008 at 8:28 am
KingGorilla (6/19/2008)
HiOn a slight tangent... 🙂 i try to avoid using the select.. into #tmptable syntax due to locking on the tempdb while it creates and inserts the rows. I usually either create the temp table explicitly 1st and then run and insert or I add a where clause of 1 = 0 on the original select..into as this retains the actual field lengths and then I run the insert.
KG
I wouldn't worry excessively about that. That was a nasty bug that was resolved in 7.0 - I don't think it's been an issue ever since. As a matter of fact - SELECT...INTO tends to be quite a bit faster perf-wise than CREATE TABLE...INSERT..., so I use it when I can get away with it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 19, 2008 at 8:39 am
ahhh ok.... thanks for that. It's good to know 🙂
June 20, 2008 at 8:06 am
I also believe that SELECT INTO #tmp... will be a minimally logged evolution whereas INSERTing into an already created temp table will be fully logged and thus less performant.
You CAN still run into issues with locking tempdb system stuff with very rapid temp object creation/teardown. This is much improved in 2005 but for all database versions where you see this issue you can mitigate it by creating 1/4 to 1 equal sized tempdb file per physical CPU core.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply