March 19, 2008 at 2:41 pm
Try Catch Block not working in sqlserver 2005.
can anyone help me explain why its not working
i am getting incorrect syntax error at try
March 19, 2008 at 2:49 pm
Can't do a thing without seeing your code.
😎
March 19, 2008 at 2:52 pm
Lynn Pettis (3/19/2008)
Can't do a thing without seeing your code.😎
That's the CATCH!!!!
😀
----------------------------------------------------------------------------------
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?
March 19, 2008 at 3:04 pm
ALTER PROCEDURE [dbo].[TestingTrans]
@TEMPVAL VARCHARr(255)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
DECLARE @rc VARCHAR(25)
SET @rc=(SELECT RC FROM RootCause WHERE RCID=@TAMPVAL)
UPDATE TABLE1 SET FIELD1="ABC" WHERE FIELD2=@RC
UPDATE TABLE2 SET FIELD1="XYZ" WHERE FIELD2=@RC
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END
March 19, 2008 at 3:06 pm
All right - what error are you expecting to happen?
Also - that doesn't even parse correctly - you're missing a few things.
The code looks to be (without parsing errors):
ALTER PROCEDURE [dbo].[TestingTrans] (@TEMPVAL VARCHARr(255))
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
DECLARE @RC VARCHAR(25)
SET @RC=(SELECT RC FROM RootCause WHERE RCID=@TEMPVAL)
UPDATE TABLE1 SET FIELD1='ABC' WHERE FIELD2=@RC
UPDATE TABLE2 SET FIELD1='XYZ' WHERE FIELD2=@RC
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END
----------------------------------------------------------------------------------
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?
March 19, 2008 at 3:07 pm
ITS SAYING INCORRECT SYNTAX NEAR "TRY"
March 19, 2008 at 3:10 pm
You're doing this on a SQL 2005 database instance - right? What's the database compatibility set to?
If it's still set to 80 - the new syntax won't work.
----------------------------------------------------------------------------------
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?
March 19, 2008 at 3:11 pm
Looks like you are missing something, not sure what. I haven't created any tables to allow me to test, so here is something for you to test:
ALTER PROCEDURE [dbo].[TestingTrans]
@TEMPVAL VARCHARr(255)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
begin
BEGIN TRAN
DECLARE @RC VARCHAR(25)
SET @RC=(SELECT
RC
FROM
RootCause
WHERE
RCID=@TEMPVAL)
UPDATE TABLE1 SET
FIELD1="ABC"
WHERE
FIELD2=@RC
UPDATE TABLE2 SET
FIELD1="XYZ"
WHERE
FIELD2=@RC
COMMIT
end
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END
{Edit}
And, of course, Matt hit on something I didn't and maybe my extra begin end aren't needed.
😎
March 19, 2008 at 3:13 pm
YES,DATABASE COMPATIBILITY IS SET TO 80
THERE ARE ONLY TWO VALUES 70 AND 80
HOW TO MAKE IT 90?
March 19, 2008 at 3:14 pm
Are you running on SQL Server 2005 or SQL Server 2000?
March 19, 2008 at 3:15 pm
madan.25 (3/19/2008)
YES,DATABASE COMPATIBILITY IS SET TO 80THERE ARE ONLY TWO VALUES 70 AND 80
HOW TO MAKE IT 90?
That means you're connecting to a SQL 2000 server. No TRY/CATCH available there. You need 2005 to use that.
----------------------------------------------------------------------------------
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?
March 19, 2008 at 3:16 pm
IM USING SQLSERVER 2005
March 19, 2008 at 3:19 pm
I AM USING SQL SERVER 2005
March 19, 2008 at 3:20 pm
In SSMS, right click on the database, select properties. Select Options under Select a page. On the right side you should see a drop down box with the caption Compatibility Level. If you click on the dropdown button, you should see 3 selections: SQL Server 7.0 (70), SQL Server 2000 (80), and SQL Server 2005 (90). Select SQL Server 2005 (90), then click the OK button.
After that, the TRY CATCH should work.
If you don't see the option for SQL Server 2005 (90), the Matt is right, you are connecting to a SQL Server 2000 instance, and you can't use TRY CATCH.
😎
March 19, 2008 at 3:22 pm
IN THE COMPATIBILITY LEVEL DROPDOWN LIST I COULD SEE ONLY TWO VALUES
SQL SERVER 7.0 (70)
SQL SERVER 2000 (80)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply