aaaarghh..errors..someone please help

Hi there,

I'm struggling with trapping errors, I've stripped down my stored proc to it's very minimum:

CREATE PROCEDURE MattTest AS

INSERT INTO tblTestr
SELECT
tblTestIn_Field1
FROM tblTestIn

IF @.@.ERROR <> 0
BEGIN
EXEC master..xp_sendmail 'matt.mcdonald@.aapct.scot.nhs.uk', 'Hello'
RAISERROR ('Matt',16,1) WITH LOG
END

GO

I've then deliberatley made a typo above called the table 'tblTestr' instead of 'tblTest'. I want the procedure to pick up the error and e-mail me and write it to the event log but I can't get it to work!!

Someone please help...pretty please...SQL Server has many different type of ways it handles errors...

Other RDBMS's would not let it compile...in this case sql does..

To check for that you need to check for the existance of the table BEFORE you access...then you can trap that type of error...

Sorry...just the way sql server is...|||Hi there,

I can't seem to trap any errors, I have a new test where I try to insert a char field from one table into an int field of another table.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE MattTest2 AS

BEGIN
INSERT INTO tblTest
SELECT tblTestIn_Field1
FROM tblTestIn

IF @.@.ERROR <> 0
PRINT 'TRANSACTION FAILED'
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

The first few records should work ok as they are ints anyway but then it changes to charcaters - e.g. 1,2,3,4,5,w,t,z SQL Server throws up an error when it reaches the first charcater i.e.w. I would like to trap this and write an error to the Error Log but the proc just ends and displays the following stanadard without displaying my custom message:

Server: Msg 245, Level 16, State 1, Procedure MattTest2, Line 7
Syntax error converting the varchar value 'W ' to a column of data type int.

So I must be doing something wrong as it definately sees it as an error??

Thanks
Matt|||Look up error handling in books online and reference the severity area...

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
CREATE TABLE myTable00(Col1 char(1))
GO

INSERT INTO myTable00(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT 'w' UNION ALL
SELECT 't' UNION ALL
SELECT 'z'
GO

CREATE PROC mySproc99
AS
DECLARE @.Error int, @.Rowcount int
INSERT INTO myTable99(Col1) SELECT Col1 FROM myTable00 --WHERE ISNUMERIC(Col1) = 1
SELECT @.Error = @.@.Error, @.Rowcount = @.@.ROWCOUNT
SELECT '@.Error = ' + CONVERT(varchar(5),@.Error) + ', @.Rowcount =' + CONVERT(varchar(5),@.@.ROWCOUNT)
GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

CREATE PROC mySproc99
AS
DECLARE @.Error int, @.Rowcount int
INSERT INTO myTable99(Col1) SELECT Col1 FROM myTable00 WHERE ISNUMERIC(Col1) = 1
SELECT @.Error = @.@.Error, @.Rowcount = @.@.ROWCOUNT
SELECT '@.Error = ' + CONVERT(varchar(5),@.Error) + ', @.Rowcount =' + CONVERT(varchar(5),@.@.ROWCOUNT)
GO

EXEC mySproc99
GO

DROP PROC mySproc99
DROP TABLE myTable00
DROP TABLE myTable99
GO|||In ms-sql the conversion from varchar to integer is done for you, ms-sql just makes the assumption it can be done. It won't matter if you use a cursor or any other type of insert. When sql does the insert it will try to enforce the conversion, which won't work in all cases. I'd check prior the insert and raise an error when necessary.|||Cheers Brett, I've been trying to get it to work like VB but it doesn't - I now know that you can only trap non-fatal errors in this manner...bit of a bummer really.|||...bit of a bummer really.

yup...btw do you have a farm?

and on this farm do have any cows?

and do they go moo moo here and a moo moo there?

Mr. Mist would be proud

Moo

read this article

http://www.sqlteam.com/item.asp?ItemID=2463|||Aye...like I haven't heard that one before...|||sure enough...but did you read the link?|||Yes thanks. It explains it all quite clearly...I know where I stand with SQL Server now.sql