Error Checking Issue - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Error Checking Issue

Author

Topic

inka
Starting Member

8 Posts

Posted - 2007-03-13 : 13:50:12

Hi All,

I have a stored procedure that has some error checking. When I execute this stored procedure, I get the following error message:

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

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 @myerror int

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

BEGIN TRANSACTION

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 @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 @myerror <> 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 @myerror <> 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

Can you please tell me how to fix this error?

Also, if you have sample stored procedure that would have a cursor, dynamic sql and error checking all in one, I would really appreciate it.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-13 : 15:54:44

Notice where the COMMIT is:

<snip>
commit transaction

FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
<snip>

You need to at least move it after the fetch. It is looping, so it COMMITs then starts a new loop, then COMMITs, then starts a new loop....

Alternativly, you could move the BEGIN TRANSACTION inside the loop so you are creating and commiting transactions for each loop.

Cheers,

-Ryan

inka
Starting Member

8 Posts

Posted - 2007-03-14 : 09:26:33

Here what it looks like now and I am still gettint the same error message. Could you please modify my sp so I know exactly what you mean?

Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 10:03:54

My views on this:

1. Keep transactions as short as possible (means keep only the portion that is really required to be inside transaction, inside BEGIN TRANS...COMMIT TRANS block)

2. Keep commit or Rollback close to your error handling logic and keep it simple. Something like this:

IF @@error <> 0
  Commit Transaction
ELSE
  Rollback Transaction

No need for labels to jump through the code and it should be avoided.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

inka
Starting Member

8 Posts

Posted - 2007-03-14 : 10:08:08

Thanks. That is what I'll do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources