Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Error Checking Issue

    Hi All,

    I have a stored procedure to which I am adding an error checking. Here is my stored procedure.

    CREATE PROCEDURE usp_DBGrowth

    AS

    DECLARE @dbsize DEC(15,2)
    DECLARE @logsize DEC(15,2)
    DECLARE @dbname SYSNAME
    DECLARE @dbsizestr NVARCHAR(500)
    DECLARE @logsizestr NVARCHAR(500)
    DECLARE @totaldbsize DEC(15,2)
    DECLARE @dbid SMALLINT



    DECLARE dbnames_cursor CURSOR
    FOR
    SELECT name, dbid
    FROM dbo.sysdatabases

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    WHILE @@FETCH_STATUS = 0
    BEGIN


    SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 1'

    EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
    PRINT @dbsize

    SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 2'


    EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
    PRINT @logsize

    SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
    PRINT @totaldbsize

    BEGIN TRANSACTION

    IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
    @dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

    INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

    IF @@ERROR <> 0
    ROLLBACK TRANSACTION
    ELSE

    COMMIT TRANSACTION

    BEGIN TRANSACTION

    INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
    IF @@ERROR <> 0
    ROLLBACK TRANSACTION
    ELSE

    COMMIT TRANSACTION


    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid

    END

    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor


    dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
    Can you tell me what I am doing wrong?
    Also, I need to add error checking after execute sp_executesql statement. Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Take a look at the sp_executesql entry in Books Online for detailed info, but there is an error code returned that can be used as a start for error handling after the call [Return Code Values 0 (success) or 1 (failure)]

    Also, I am confused about dbid - if it is, in fact, a primary key, you should not be able to add a duplicate row.

    Do you actually have the column DEFINED as a primary key, or are you just saying that it IS a "primary key" in concept?

    *edit*
    Keep in mind that the return code values (success/failure) are pretty much (as you might expect) binary. The statement executed or it did not. If you need more robust error reporting, consider using a stored procedure and return codes/variables from it that may be more descriptive regarding the error encountered. If you go the stored proc route, heavily consider the use of the RAISERROR logic so that the calling procedure (or stack thereof) will handle the lower-level failure correctly.
    Last edited by TallCowboy0614; 03-12-07 at 17:37.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    It is defined as a primary key.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    So what you are saying then, is that in a table with a defined primary key of the dbid, SQL Server is allowing you to insert a row with a duplicate key??? And not one, but many???

    Hmmmm...never heard of that one before...did not think it was possible (in fact, I still don't - there's gotta be something else we are miscommunicating about here)...though I inadvertently have tried it on many, many occasions.

    The very definition of a primary key precludes the situation you describe.

    Humor me and check and make sure that column is defined in the table definition as a primary key.

    The reason I ask is because if I understand your situation and question correctly, your error handling should not be an issue of success or failure. If you define a column in a table as belonging to the table's primary key, you CANNOT enter either a duplicate nor NULL value in that (or those) column(s). This is enforced at the SQL Server level, not in error handling code.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Jul 2004
    Posts
    268
    Sorry, my fault. This table has a composite primary key - updatedate and dbsize columns.

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    ok, clearing that up then, you now know why you have your duplicate dbid entries, correct? If it's not part of the primary key and not part of a UNIQUE-constrained index, then you won't get an error on the insert.

    I am still confusicated by how you are getting a duplicate record error on the insert to the databasesize table anyway. Your GETDATE() used in the insert statement should pretty much always return a different, unique value (at least the milliseconds should make it so - unless you are looping VERY, VERY quickly )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Jul 2004
    Posts
    268
    They are not really duplicates, I've realized it now. The difference is in the milliseconds. So technically these records are not duplicates.

    I modified the error checking a little bit and now I am getting this error message:

    SP
    ALTER PROCEDURE usp_DBGrowth

    AS

    DECLARE @dbsize DEC(15,2)
    DECLARE @logsize DEC(15,2)
    DECLARE @dbname SYSNAME
    DECLARE @dbsizestr NVARCHAR(500)
    DECLARE @logsizestr NVARCHAR(500)
    DECLARE @totaldbsize DEC(15,2)
    DECLARE @dbid SMALLINT
    declare @myerror int


    DECLARE dbnames_cursor CURSOR
    FOR
    SELECT name, dbid
    FROM dbo.sysdatabases

    OPEN dbnames_cursor

    BEGIN TRANSACTION

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    WHILE @@FETCH_STATUS = 0
    BEGIN


    SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 1'

    EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
    PRINT @dbsize

    SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 2'


    EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
    PRINT @logsize

    SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
    PRINT @totaldbsize

    IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
    @dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

    INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

    set @myerror = @@error
    print @myerror
    IF @@ERROR <> 0 goto handle_errors
    return(1)

    --INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (@dbid, getdate(), @totaldbsize)
    INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 45.0)

    set @myerror = @@error
    print @myerror
    IF @@ERROR <> 0 goto handle_errors
    return(1)

    commit transaction

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid


    handle_errors:
    rollback transaction
    print 'error occured'

    END



    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor



    Error message:

    7328.00
    648.00
    62.31
    0
    Server: Msg 266, Level 16, State 2, Procedure usp_DBGrowth, Line 60
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.


    How can I fix it?


    Also, Do you have any examples of sp with cursors, dynamic sql and error checking all in one sp? That would really help me.

    Thanks.

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    IF @@ERROR <> 0 goto handle_errors
    return(1)
    If everything is OK you just return, without commiting the transaction. You should move the return statement a bit down in your code.

    Futhermore I would youse a begin...else...end structure instead of your label and goto. Kind of:
    IF @@ERROR = 0
    BEGIN
    -- Handle successful condition
    ...
    COMMIT TRANSACTION
    END ELSE
    BEGIN
    -- Handle error condition
    ...
    ROLLBACK TRANSACTION
    END
    RETURN()

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  10. #10
    Join Date
    Jul 2004
    Posts
    268
    roac,
    I've tried what you have suggested and I am getting the following error:

    7328.00
    648.00
    62.31
    Server: Msg 3902, Level 16, State 1, Procedure usp_DBGrowth, Line 58
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    Server: Msg 3903, Level 16, State 1, Procedure usp_DBGrowth, Line 75
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Here is the sp:

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO





    ALTER PROCEDURE usp_DBGrowth

    AS

    DECLARE @dbsize DEC(15,2)
    DECLARE @logsize DEC(15,2)
    DECLARE @dbname SYSNAME
    DECLARE @dbsizestr NVARCHAR(500)
    DECLARE @logsizestr NVARCHAR(500)
    DECLARE @totaldbsize DEC(15,2)
    DECLARE @dbid SMALLINT



    DECLARE dbnames_cursor CURSOR
    FOR
    SELECT name, dbid
    FROM dbo.sysdatabases

    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    WHILE @@FETCH_STATUS = 0
    BEGIN


    SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 1'

    EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
    PRINT @dbsize

    SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 2'


    EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
    PRINT @logsize

    SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
    PRINT @totaldbsize


    if @@error = 0
    begin
    IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
    @dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

    INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

    commit transaction
    end
    else
    begin
    rollback transaction
    end


    if @@error = 0
    begin

    INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (@dbid, getdate(), @totaldbsize)

    commit transaction
    end
    else
    begin
    rollback transaction
    end


    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid


    END

    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor






    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    Can you please review it and let me know where my problem is?

    Thanks.

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    There is no BEGIN TRANSACTION in your stored procedure. You can't commit or rollback a transaction that does not exist.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Jul 2004
    Posts
    268
    Do I need to change begin to begin transaction? Or I need to do something else?

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    No, you need that "begin" there if you want to execute the block of code between your current BEGIN and END like you are now.

    What you need to do is to put a BEGIN TRANSACTION in your code at the point at which you want your logical transaction to begin. It might be right AFTER the BEGIN in your code...it depends on where you want your logical transaction to start. I don't think ROAC was telling you to remove your BEGIN TRANSACTION code in your original post of your code...just how to handle the error section.

    I actually think your original code posted was closer to the way I would recommend than the current iteration. I'm just not sure what the RETURN(1) is supposed to signify in your original code...you do realize it returns you to the caller with an exit code of 1 if each update is successful, right? I don't THINK that is what you want to do.

    Try this:
    Code:
    .
    .
    .
    .
    INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())
    
    set @myerror = @@error
    print @myerror
    IF @myerror <> 0 goto handle_errors
    
    --INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (@dbid, getdate(), @totaldbsize) 
    INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 45.0) 
    
    set @myerror = @@error
    print @myerror
    IF @myerror <> 0 goto handle_errors
    
    commit transaction
    
    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    END
    
    return(0)
    
    handle_errors:
    rollback transaction
    print 'error occured'
    return(1)
    
    CLOSE dbnames_cursor
    DEALLOCATE dbnames_cursor
    .
    .
    .
    Your original code just didn't flow correctly, IMHO...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  14. #14
    Join Date
    Jul 2004
    Posts
    268
    TallCowboy0614,

    I've tried that and here is the message I get once I execute my sp.

    7328.00
    648.00
    62.31
    0

    (1 row(s) affected)

    0
    1408.00
    640.00
    16.00
    0

    (1 row(s) affected)

    0
    Server: Msg 3902, Level 16, State 1, Procedure usp_DBGrowth, Line 68
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    640.00
    648.00
    10.06
    0

    (1 row(s) affected)


    Now, it looks like although I got this error message all the records were committed. Can you tell me what to do with it?

  15. #15
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    You are trying to commit/Rollback 2 times and beginning the transaction only one time... thats the problem.

    My sugession is to merge the both @@Error = 0 blocks into one OR write BEGIN TRANSACTION after first @@Error = 0 block (which is just avoid the error but it is not recommended to do this way...)

    I think it is helpful.

    Cheers
    --Riaz

    Quote Originally Posted by inka
    TallCowboy0614,

    I've tried that and here is the message I get once I execute my sp.

    7328.00
    648.00
    62.31
    0

    (1 row(s) affected)

    0
    1408.00
    640.00
    16.00
    0

    (1 row(s) affected)

    0
    Server: Msg 3902, Level 16, State 1, Procedure usp_DBGrowth, Line 68
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    640.00
    648.00
    10.06
    0

    (1 row(s) affected)


    Now, it looks like although I got this error message all the records were committed. Can you tell me what to do with it?

Posting Permissions

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