Prev: Moving SSRS Server not the database
Next: Backup Rights on All Future Databases
From: aspfun on 9 Feb 2010 10:24
I used SPtrantest below to test transaction.
How to create an error to test and print error code?
I tried to rename a column name from MYNAME to MYNAMEX and run "exec
sptrantest 1" but it only displays another error message without eroor code.
---------------
ALTER PROCEDURE [dbo].[SPtrantest]
@id INT
AS
-- STEP 1
BEGIN TRANSACTION

DECLARE @myerror INT

UPDATE _aaa
SET myname = 'QQQ'
WHERE id = @id

SET @myerror = @@ERROR

PRINT @myerror

IF @@ERROR <> 0
BEGIN
PRINT @myerror

ROLLBACK TRANSACTION

RAISERROR('ERROR FROM SP',
16,
1)

RETURN
END

COMMIT
--------------- TABLE _AAA

ID MYNAME CODE
----------------------------
1 AAA 111
2 BBB 222

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1

From: Scott Morris on 9 Feb 2010 10:54
Start here - and always indicate which version of sql server you are using.

http://www.sommarskog.se/error_handling_2005.html


From: Erland Sommarskog on 9 Feb 2010 18:02
aspfun (u53138(a)uwe) writes:
> I used SPtrantest below to test transaction.
> How to create an error to test and print error code?

For instance:

> UPDATE _aaa
> SET myname = 'QQQ'
> WHERE id = @id

Make this

UPDATE _aaa
SET myname = replicate('QQQ', 8000)
WHERE id = @id

This will cause an error unless myname is varchar(MAX) or varchar(8000).

If you are on SQL 2005, you should use TRY/CATCH for your error handling.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Moving SSRS Server not the database
Next: Backup Rights on All Future Databases