BEGIN TRY, BEGIN CATCH PROBLEMS

sabev
sabev used Ask the Experts™
on
I'm trying to add a TRY/CATCH ROUTINE TO SQL Server 2005 Stored Procedure with no luck.  I have now gone back to the very basics, and copied code right from MSDN's examples.

The code below produces the following error -

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'END'.
Msg 195, Level 15, State 10, Line 13
'ERROR_NUMBER' is not a recognized function name.

Any ideas?  I've tried a couple of MSDN samples, but they all produce syntax errors.



USE SDE;
GO
 
BEGIN TRY
    SELECT * FROM SDE.ZIP;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008

Commented:
are you sure your db compatability mode is set to 90 and not 80?

Author

Commented:
I'm affraid I'm not sure what that means.  This is my first Stored Proceedure.

Thanks
Awarded 2008
Awarded 2008

Commented:
no problem.  right click the db, go to properties, then options.  under the compatability section, what does it say there?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
hi,

Why don't you try/catch the potential errors in your code behind that is calling the stored procedure?

because that's working alright, and you know it its for sure.

Author

Commented:
SQL Server 2000(80)

(So it's 2000 and not 2005 that I'm testing this on).  Once I get to the real world application, I will be updateing a linked 2005 server from 2000.  The update code works fine.  Just going to put it in a TRY/CATCH)

The update code that I want to put in a TRY/CATCH is attached below

Thanks
USE [sde]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [domain\me].[update]
 
AS
GO
BEGIN TRY 
BEGIN TRAN TEST
 
SET NOCOUNT ON;
UPDATE permitcrwsql2k5.crw_test.dbo.geo_ownership 
SET zone_code1 = SUBSTRING(t2.zone_planning,0,30),
zone_code2 = zone_buildingDept,
census = census_tract
From permitcrwsql2k5.crw_test.dbo.geo_ownership t1 INNER JOIN dbo.CRWSpatialViewData t2 on t1.site_apn = t2.ppi
 
COMMIT TRAN TEST
END TRY
BEGIN CATCH
ROLLBACK TRAN TEST
END CATCH

Open in new window

Author

Commented:
I'not using any code behind to call it.  The SQL is imbedded in the SP.

Thanks
Awarded 2008
Awarded 2008

Commented:
you'll need to switch it to 90 (from the dropdown) to be able to use TRY...CATCH

Author

Commented:
hmmm....  Doesn't give me that option.  Only (70) and (80).

The 2005 server alows me to go to (90).
Awarded 2008
Awarded 2008
Commented:
right, if you're on a 2000 machine, then you can't use the 2005 features...BEGIN..CATCH is for 2005 and above only.

Author

Commented:
Thanks.  I'll be moving it to 2005 within a month or so.  Not gonna chase this around any more.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial