Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: 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...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Look up error handling in books online and reference the severity area...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    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.

  6. #6
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mmcdonald
    ...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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Aye...like I haven't heard that one before...

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sure enough...but did you read the link?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Yes thanks. It explains it all quite clearly...I know where I stand with SQL Server now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •