March 9, 2016 at 8:33 pm
Hello,
My name is Avadesh, and I'm new to this forum. Nice to be here!
I have this SQL agent job that runs on a daily basis that calls & execute 2 SP. The issue is that the job fails, but not on a daily basis. The last job execution failure was on 8th March 2016. Then the job succeeded on 9th March 2016. Today (10th March 2016), the job failed again.
Here is the error message ===> Executed as user: DB2-AYOPAY\db_service. Violation of UNIQUE KEY constraint 'IX_SnapShot'. Cannot insert duplicate key in object 'dbo.SnapShot'. The duplicate key value is (2, 1, 1, 10, 3, 2016). [SQLSTATE 23000] (Error 2627) Mail (Id: 1079) queued. [SQLSTATE 01000] (Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Ayopay Indonesia Reload 09 Mar 2016
TOTAL RELOADIDR 39,982,651.00RM 11,407.84TOTAL NEW USER16
SUCCESSINCOMPLETECHANNELQTYIDRRMQTYIDRRMOFFLINE RELOADBCA3720,373,800.005,813.05112,591,000.00739.26BRI156,730,000.001,920.20216,300,000.001,797.52Mandiri186,878,851.001,962.67124,818,850.001,374.91TOTAL7033,982,651.009,695.924413,709,850.003,911.69 ONLINE RELOADFinnet - ATM75,000,000.001,426.60
I would appreciate if someone could guide/lead me into the right direction. I have no prior experience in handling error messages such as this. I know that there were similar error messages posted b4, but i still could not wrap my head around it, due to my inexperience.
The server is running on Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
My 1st attempt is to look for the object name 'dbo.SnapShot', but to no avail. Any help is much appreciated. TQ
March 10, 2016 at 6:47 am
avadeshuwaran (3/9/2016)
Hello,My name is Avadesh, and I'm new to this forum. Nice to be here!
I have this SQL agent job that runs on a daily basis that calls & execute 2 SP. The issue is that the job fails, but not on a daily basis. The last job execution failure was on 8th March 2016. Then the job succeeded on 9th March 2016. Today (10th March 2016), the job failed again.
Here is the error message ===> Executed as user: DB2-AYOPAY\db_service. Violation of UNIQUE KEY constraint 'IX_SnapShot'. Cannot insert duplicate key in object 'dbo.SnapShot'. The duplicate key value is (2, 1, 1, 10, 3, 2016). [SQLSTATE 23000] (Error 2627) Mail (Id: 1079) queued. [SQLSTATE 01000] (Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Ayopay Indonesia Reload 09 Mar 2016
TOTAL RELOADIDR 39,982,651.00RM 11,407.84TOTAL NEW USER16
SUCCESSINCOMPLETECHANNELQTYIDRRMQTYIDRRMOFFLINE RELOADBCA3720,373,800.005,813.05112,591,000.00739.26BRI156,730,000.001,920.20216,300,000.001,797.52Mandiri186,878,851.001,962.67124,818,850.001,374.91TOTAL7033,982,651.009,695.924413,709,850.003,911.69 ONLINE RELOADFinnet - ATM75,000,000.001,426.60
I would appreciate if someone could guide/lead me into the right direction. I have no prior experience in handling error messages such as this. I know that there were similar error messages posted b4, but i still could not wrap my head around it, due to my inexperience.
The server is running on Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
My 1st attempt is to look for the object name 'dbo.SnapShot', but to no avail. Any help is much appreciated. TQ
Finding dbo.SnapShot would also be my starting point. It sounds like a table on which a unique index is defined.
If you find the table and script the index, you'll find that it has six columns (not counting any INCLUDE columns). These six columns will correspond with the 6-tuple (2, 1, 1, 10, 3, 2016) from the error message.
March 10, 2016 at 1:04 pm
In addition to what Phil writes...
1. Double-click the job name in the Job Activity Monitor window, switch to the Steps tab, and click Edit (if there are more steps, do this for the step that failed). Depending on the type of job, you hopefully will see a field "Database" that showss in which database the job starts, and beneath that a large window where the command(s) are entered. In many cases those commands will be stored procedures, but that is not a strict requirement. Looking at the code in this window, or at the definition of the stored procedure that is executed, can help you find the object is named in the error, and perhaps find the root cause.
2. I agree with Phil that dbo.SnapShot is probably indeed a table, but it could also be an indexed view. Not likely, but if you fail to find dbo.SnapShot in the tables node of SSMS Object Explorer, check the views node.
March 10, 2016 at 1:13 pm
2. I agree with Phil that dbo.SnapShot is probably indeed a table, but it could also be an indexed view. Not likely, but if you fail to find dbo.SnapShot in the tables node of SSMS Object Explorer, check the views node.
Perhaps it could also be a SYNONYM, so check that node as well (I'm not sure whether the error message would refer to the synonym itself or its target).
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply