August 24, 2012 at 2:40 pm
Dear Experts,
What happen with my transaction below. I am getting this error. (Somehow I can't post the @ sign, in sql code block)
Msg 50000, Level 16, State 1, Line 91
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
look like the last insert codes block is the one that cause the issue.
INSERT INTO tableB(fieldB )
SELECT fieldB FROM tableB1
DECLARE (C)ErrorMum INT
DECLARE (C)ErrorMessage VARCHAR(200)
DECLARE (C)Severity INT
DECLARE (C)State INT
BEGIN TRY
BEGIN TRAN BACKUP101
BEGIN TRY
SET IDENTITY_INSERT TableA ON
END TRY
BEGIN CATCH
END CATCH
INSERT INTO TableA(fieldA)
SELECT fieldA FROM TableA1
BEGIN TRY
SET IDENTITY_INSERT TableB ON
END TRY
BEGIN CATCH
END CATCH
INSERT INTO tableB(fieldB )
SELECT fieldB FROM tableB1
COMMIT TRAN BACKUP101
END TRY
BEGIN CATCH
IF (C)(C)TRANCOUNT > 0 BEGIN
ROLLBACK TRAN BACKUP101
END
SET (C)ErrorMum = ERROR_NUMBER()
SET (C)ErrorMessage = ERROR_MESSAGE()
SET (C)Severity = ERROR_SEVERITY()
SET (C)State = ERROR_STATE()
RAISERROR((C)ErrorMessage, (C)Severity, (C)State)
END CATCH
August 24, 2012 at 3:16 pm
It looks like the issue is because the exception occurs at the line below . Basically what I try to do is
try to set insert identity ON for every table before i do insertion and regardless of that setting is successful or not, I want to move on to do insert. what is the proper way to do this in clean maner?
-Thanks
BEGIN TRY
SET IDENTITY_INSERT TableB ON
END TRY
BEGIN CATCH
END CATCH
August 24, 2012 at 3:21 pm
My guess is one your early try-catch blocks has encountered an error but you can't tell because you have empty catch blocks. I call that error squelching, not handling. The way you coded this is like the old VB6 "on error resume next". Put something in your catch blocks or better yet, remove the nested ones and figure out what the actual error is. Then start putting you try catch blocks back in. And make sure your catch blocks DO SOMETHING!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2012 at 3:26 pm
Here is proof. In my database I do not have TableA.
DECLARE @ErrorMum INT
DECLARE @ErrorMessage VARCHAR(200)
DECLARE @Severity INT
DECLARE @State INT
BEGIN TRY
BEGIN TRAN BACKUP101
BEGIN TRY
SET IDENTITY_INSERT TableA ON
END TRY
BEGIN CATCH
--uncomment the following line and you will see the error
--Select ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()
END CATCH
COMMIT TRAN BACKUP101
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN BACKUP101
END
SET @ErrorMum = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @Severity = ERROR_SEVERITY()
SET @State = ERROR_STATE()
RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2012 at 3:26 pm
Thank you for quick response. I actually replied to my post previously. you are right all i tried to do on that is basically squelching, definitely not handling. I am working on a better way to handle that
Sean Lange (8/24/2012)
My guess is one your early try-catch blocks has encountered an error but you can't tell because you have empty catch blocks. I call that error squelching, not handling. The way you coded this is like the old VB6 "on error resume next". Put something in your catch blocks or better yet, remove the nested ones and figure out what the actual error is. Then start putting you try catch blocks back in. And make sure your catch blocks DO SOMETHING!!!
August 24, 2012 at 3:29 pm
haiao2000 (8/24/2012)
Thank you for quick response. I actually replied to my post previously. you are right all i tried to do on that is basically squelching, definitely not handling. I am working on a better way to handle thatSean Lange (8/24/2012)
My guess is one your early try-catch blocks has encountered an error but you can't tell because you have empty catch blocks. I call that error squelching, not handling. The way you coded this is like the old VB6 "on error resume next". Put something in your catch blocks or better yet, remove the nested ones and figure out what the actual error is. Then start putting you try catch blocks back in. And make sure your catch blocks DO SOMETHING!!!
Try this.
DECLARE @ErrorMum INT
DECLARE @ErrorMessage VARCHAR(200)
DECLARE @Severity INT
DECLARE @State INT
BEGIN TRY
BEGIN TRAN BACKUP101
SET IDENTITY_INSERT TableA ON
INSERT INTO TableA(fieldA)
SELECT fieldA FROM TableA1
SET IDENTITY_INSERT TableB ON
INSERT INTO tableB(fieldB )
SELECT fieldB FROM tableB1
COMMIT TRAN BACKUP101
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN BACKUP101
END
SET @ErrorMum = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @Severity = ERROR_SEVERITY()
SET @State = ERROR_STATE()
RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH
This way either your entire process completes OR it is rolled back.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2012 at 3:36 pm
Sean
That will not work because TableB may not have a column with identify specification properties set to YES. The reason i do this because these tables being created dynamically by other process, and some tables may have identity set, other dont.
-Thanks
August 24, 2012 at 3:44 pm
Then you may want something more like this:
DECLARE @ErrorMum INT
DECLARE @ErrorMessage VARCHAR(200)
DECLARE @Severity INT
DECLARE @State INT
BEGIN TRY
BEGIN TRAN BACKUP101
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableA'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableA ON
INSERT INTO TableA(fieldA)
SELECT fieldA FROM TableA1
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableB'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableB ON
INSERT INTO TableB(fieldB )
SELECT fieldB FROM tableB1
COMMIT TRAN BACKUP101
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN BACKUP101
END
SET @ErrorMum = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @Severity = ERROR_SEVERITY()
SET @State = ERROR_STATE()
RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH
August 24, 2012 at 3:45 pm
Try this instead then:
if EXISTS
(
select * from sys.columns
where object_id = object_id('TableA')
and is_identity = 1
)
SET IDENTITY_INSERT TableA ON
LOL or use Lynn's. His is probably a little better way to check for the same thing.
It seems I was typing as he posted again...that is about the third or fourth time this week I have posted the same thing as you but you beat me. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2012 at 3:49 pm
Lynn
This is what I am looking for. Great! Let me try this. Thank You!
Lynn Pettis (8/24/2012)
August 24, 2012 at 3:49 pm
Sean Lange (8/24/2012)
Try this instead then:
if EXISTS
(
select * from sys.columns
where object_id = object_id('TableA')
and is_identity = 1
)
SET IDENTITY_INSERT TableA ON
LOL or use Lynn's. His is probably a little better way to check for the same thing.
It seems I was typing as he posted again...that is about the third or fourth time this week I have posted the same thing as you but you beat me. 😀
Hey, All I can say is Great Minds.....
August 24, 2012 at 3:50 pm
Sean,
Great which mean you guys are fast and furious!
Thanks for all the helps, I am sure this will work.
Sean Lange (8/24/2012)
Try this instead then:
if EXISTS
(
select * from sys.columns
where object_id = object_id('TableA')
and is_identity = 1
)
SET IDENTITY_INSERT TableA ON
LOL or use Lynn's. His is probably a little better way to check for the same thing.
It seems I was typing as he posted again...that is about the third or fourth time this week I have posted the same thing as you but you beat me. 😀
August 24, 2012 at 3:54 pm
Actually, you need a little more:
DECLARE @ErrorMum INT
DECLARE @ErrorMessage VARCHAR(200)
DECLARE @Severity INT
DECLARE @State INT
BEGIN TRY
BEGIN TRAN BACKUP101
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableA'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableA ON
INSERT INTO TableA(fieldA)
SELECT fieldA FROM TableA1
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableA'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableA OFF
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableB'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableB ON
INSERT INTO TableB(fieldB )
SELECT fieldB FROM tableB1
IF OBJECTPROPERTY(OBJECT_ID('dbo.TableB'),'TableHasIdentity') = 1
SET IDENTITY_INSERT TableB OFF
COMMIT TRAN BACKUP101
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN BACKUP101
END
SET @ErrorMum = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @Severity = ERROR_SEVERITY()
SET @State = ERROR_STATE()
RAISERROR(@ErrorMessage, @Severity, @State)
END CATCH
If you have turned IDENTITY_INSERT ON on one table and attempt to turn it on on another it will fail.
From BOL:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
http://msdn.microsoft.com/en-us/library/ms188059(v=sql.100).aspx
August 24, 2012 at 4:21 pm
One more issue though.. how do i check table on another database from one database . if i run this query from the "MainDatabase" and set table on the "BackupDatabase", assuming linked server has been setup correctly. the if statement does not seem recognize "BackupDatabase".
IF OBJECTPROPERTY(OBJECT_ID('BackupDatabase.dbo.TableA'),'TableHasIdentity') = 1
SET IDENTITY_INSERT BackupDatabase.dbo.TableA ON
Thanks!
August 24, 2012 at 4:26 pm
haiao2000 (8/24/2012)
One more issue though.. how do i check table on another database from one database . if i run this query from the "MainDatabase" and set table on the "BackupDatabase", assuming linked server has been setup correctly. the if statement does not seem recognize "BackupDatabase".IF OBJECTPROPERTY(OBJECT_ID('BackupDatabase.dbo.TableA'),'TableHasIdentity') = 1
SET IDENTITY_INSERT BackupDatabase.dbo.TableA ON
Thanks!
This is the type of problems you run into when you fail to provide all the information needed to provide you with a correct answer. Also, are you talking about a linked server or a another database on the same server? The code posted seems to indicate the latter.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply