Help, SQL reporting wrong line number for error.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help, SQL reporting wrong line number for error.

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-07-15 : 09:17:43

I am trying to debug a script but it keeps saying that an error occured on line 46 which does not make any sense since this is part of the RAISE ERROR part of the script and the error messages says it is a NULL INSERT error. Why is it giving me the wrong error number and how can I fix that?

CREATE PROC dbo.spFiles
    @UpdateType AS char(6),
    @FileID AS int,
    @FilePath AS varchar(250),
    @Name AS varchar(50)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

    --Remove existing file entries.
    IF (@UpdateType = 'Update' OR @UpdateType = 'Delete')
    BEGIN
        DELETE FROM dbo.tblFiles
        WHERE FileID = @FileID;
    END

    --Process a new file.
    IF (@UpdateType = 'Insert' OR @UpdateType = 'Update')
    BEGIN
        INSERT INTO dbo.tblFiles (FilePath, Name)
        VALUES (@FilePath, @Name);

        --Get the FileID and return it to the calling proc.
        SELECT @FileID = SCOPE_IDENTITY();
    END
    RETURN @FileID;
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage nvarchar(4000);
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DeCLARE @ErrorLine int;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState,
               @ErrorLine);

    IF ((XACT_STATE()) = -1)
    BEGIN
        ROLLBACK;
    END
    ELSE IF ((XACT_STATE()) = 1)
    BEGIN
        COMMIT;
    END

END CATCH

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-15 : 20:28:07

When you see a message like below, the first line number on the first line is the line number on which the error was raised.

Msg 50000, Level 16, State 1, Line 26
Divide by zero error encountered.

The second line is the error message. That does not contain the error line by default. So you need to provide it. So make the change shown in red to your catch block:

BEGIN CATCH
    DECLARE @ErrorMessage nvarchar(4000);
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DeCLARE @ErrorLine int;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE()  +N' and that happened on %d',
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState,
               @ErrorLine);

    IF ((XACT_STATE()) = -1)
    BEGIN
        ROLLBACK;
    END
    ELSE IF ((XACT_STATE()) = 1)
    BEGIN
        COMMIT;
    END

END CATCH

In this, the %d is the first token used for string formatting, which you already are providing (as the fourth parameter to the RAISERROR function. The way you have it, the fourth parameter is not even used.

Edit: Reading back on what I wrote above, I know I am saying it in the most confusing manner. Take a look at the RAISERROR page and pay particular attention to the first parameter and how it relates to the fourth and subsequent parameters: http://msdn.microsoft.com/en-us/library/ms178592(v=sql.105).aspx

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-07-15 : 21:57:42

What you wrote made sense to mee. It is only printing out the first two @Variables in my Raise error. %d makes it print out my fourth variable, which is the line error. thanks

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources