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?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.|||It is defined as a primary key.|||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.|||Sorry, my fault. This table has a composite primary key - updatedate and dbsize columns.|||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 ;) )|||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.|||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()|||for how to handle errors in sql:
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html|||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.|||There is no BEGIN TRANSACTION in your stored procedure. You can't commit or rollback a transaction that does not exist.|||Do I need to change begin to begin transaction? Or I need to do something else?|||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:.
.
.
.
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...|||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?|||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
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?|||Sorry, I should've posted my latest version of sp since I made the changes that TallCowboy0614 recommended.
Here what it looks like now:
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 @.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
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())
set @.myerror = @.@.error
print @.myerror
if @.myerror <>0 goto handle_errors
INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (@.dbid, getdate(), @.totaldbsize)
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 occurred'
return(1)
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
And this is the error on execution:
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)|||OK, the code looks good to me, so I think there is something else involved. Perhaps implicit transactions getting in the way, or something similar?
Anyway, try printing the contents of @.@.TRANCOUNT before and after your BEGIN TRAN and COMMIT TRAN statements, as well as after (and maybe even before) the INSERTS within your transaction block.
Somewhere the transaction is being lost for some reason.
