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.
| Author |
Topic |
|
skaluva
Starting Member
17 Posts |
 Posted - 2002-07-09 : 17:50:19
|
| Hi All;The below is the code I have in one of my stored proceedures. I am calling this stored proceedure as a scheduled job. You can see that in my code I am using @@Error to handle errors. Though I am doing error handle in this SQL job is showing me as job failed as SQL Server spitting out the critical error message as given below.Msg 2627, Sev 14: Violation of UNIQUE KEY constraint 'IX_tblEmployees'. Cannot insert duplicate key in object 'tblEmployees'. [SQLSTATE 23000]Msg 3621, Sev 14: The statement has been terminated. [SQLSTATE 01000]Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007]This message is comming due to the execution of Insert statement into tblEmployees. Please help me how to suppress this system error messages?Begin -- Case 1 Declare cusrNewEmployees Insensitive cursor for SELECT BranchID, Left(FirstName, 20) as FirstName, Left(LastName, 20) as LastName, MidInit, IsNull(RegRate, 0), IsNull(OTRate, 0), SSN5 , tblEmpSpec.SpecID as SpecID from tblInput_Employees Left Outer Join tblEmpSpec on tblEmpSpec.SpecCode = tblInput_Employees.GroupID Where RTrim(BranchID) + ' - ' + RTrim(FirstName) + IsNull(MidInit, ' ') + RTrim(LastName) + ' - ' + RTrim(SSN5) Not In (Select RTrim(BranchID) + ' - ' + RTrim(EmpFirstName) + IsNull(EmpMidInit, ' ') + RTrim(EmpLastName) + ' - ' + RTrim(SSN5) From tblEmployees ) Open cusrNewEmployees Fetch next from cusrNewEmployees into @sBranchID, @sFirstName, @sLastName, @sMidInit, @mRate, @mOTRate, @sSSN4, @iSpecID While @@FEtch_Status = 0 Begin Begin If Len(@sLastName) > 7 Select @sUID = Left(@sLastName, 7) Else Select @sUID = @sLastName Select @sUID = @sUID + Left(@sFirstName, 2) Select @sTagSSN = Convert(Char(2), Convert(TinyInt, SubString(@sSSN4, 1, 1)) + Convert(TinyInt, SubString(@sSSN4, 2, 1)) + Convert(TinyInt,SubString(@sSSN4, 3, 1)) + Convert(TinyInt,SubString(@sSSN4, 4, 1))) If Len(@sTagSSN) < 2Select @sTagSSN = '0' + @sTagSSNSelect @sUID = Rtrim(@sBranchID) + @sUID + @sTagSSN Select @iSpecID = Case @iSpecIDWhen 7 then 13When 11 then 7Else@iSpecIDEndInsert into tblEmployees (BranchID, SpecID, EmpFirstName, EmpMidInit, EmpLastName, EmpRegRate, EmpOTRate, SSN5, UserName, pwd) Values (@sBranchID, @iSpecID, @sFirstName, @sMidInit, @sLastName, @mRate, @mOTRate, @sSSN4, @sUID, @sUID ) If @@Error > 0 Begin Insert into tblData_Import_Log (JobID, JobName, Error_Description, JobType) values ( IsNull(@sLastName, ''), RTrim(IsNull(@sFirstName, '')) + '-SSN4=' + @sSSN4 + 'BranchID=' + RTrim(@sBranchID) , 'Unique Key violation error on User ID. Please make sure the data of the record is not repeated.', 'employee') EndFetch next from cusrNewEmployees into @sBranchID, @sFirstName, @sLastName, @sMidInit, @mRate, @mOTRate, @sSSN4, @iSpecID End Close cusrNewEmployeesDeallocate cusrNewEmployeesEnd -- End of Case 1Thanks in Hand,-- Sree |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-07-09 : 17:57:41
|
| That error tells me that you are trying to insert a duplicate employee into the employee's table. Since you have a Primary Key constraint on that table (thus the err msg), you probably WANT that error message.To avoid the error, check to see if the employee exists before the insert.<SELECT STATEMENT HERE THAT DETERMINES IF THE EMPLOYEE EXISTS>Insert into tblEmployees ...... etcI'd give you a better answer, but I dont know what your UNIQUE KEY constraint is.Michael<Yoda>Use the Search page you must. Find the answer you will. |
 |
|
|
dsdeming
479 Posts |
Posted - 2002-07-10 : 08:10:10
|
In your cursor declaration you appear to be trying to filter out rows that already exist in the target table: quote:
Where RTrim(BranchID) + ' - ' + RTrim(FirstName) + IsNull(MidInit, ' ') + RTrim(LastName) + ' - ' + RTrim(SSN5) Not In (Select RTrim(BranchID) + ' - ' + RTrim(EmpFirstName) + IsNull(EmpMidInit, ' ') + RTrim(EmpLastName) + ' - ' + RTrim(SSN5) From tblEmployees )
However, the concatenated strings you're filtering on must not be the PK. What is the PK on the table? |
|
|
|
skaluva
Starting Member
17 Posts |
Posted - 2002-07-10 : 08:34:33
|
quote:
That error tells me that you are trying to insert a duplicate employee into the employee's table. Since you have a Primary Key constraint on that table (thus the err msg), you probably WANT that error message.To avoid the error, check to see if the employee exists before the insert.<SELECT STATEMENT HERE THAT DETERMINES IF THE EMPLOYEE EXISTS>Insert into tblEmployees ...... etcI'd give you a better answer, but I dont know what your UNIQUE KEY constraint is.Michael<Yoda>Use the Search page you must. Find the answer you will.
Dear Michael,Thanks for your reply. Unique ID constraint on the table is 'UserName'. Yes!! you are right, this error message is causing because the process in the loop is trying to insert duplicate key into this field. It is successfully processing rest of the records in cursor even that are after error record. I can see that the error record is successfully being logged into 'tblData_Import_Log' table as I wrote in If @@error > 0 block. But, due to this error message Job, in which I called this procedure is resulting as Failed. I guess my problem could be solved, if I can bypass (or suppress) this error message.Thanks in hand,-- Sree |
|
|
|
BoHey
Starting Member
1 Post |
Posted - 2002-07-11 : 11:37:57
|
| ...hm, may be to simple but what about this:Set the 'On failure action' of the Job Step to 'Quit the Job reporting success'.Pragmatic,BoHey |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-11 : 11:48:57
|
quote:
...hm, may be to simple but what about this:Set the 'On failure action' of the Job Step to 'Quit the Job reporting success'.
Yes, but what happens when the job fails for another reason, a failure that he wants to be notified for?Don't throw good money after bad; the best way to "suppress" error messages is to avoid situations that cause the error in the first place.Jonathan Boott, MCDBA |
|
|
|
skaluva
Starting Member
17 Posts |
Posted - 2002-07-13 : 11:40:13
|
Yap!!That is true. I choosed to suppress these kind of errors as many as I can think of using big chunk of test data. Setting 'On failure action' of the Job Step to 'Quit the Job reporting success' is always risky to find out the cause of failure in production.Thnks for all your replies -- Sreequote:
quote:
...hm, may be to simple but what about this:Set the 'On failure action' of the Job Step to 'Quit the Job reporting success'.
Yes, but what happens when the job fails for another reason, a failure that he wants to be notified for?Don't throw good money after bad; the best way to "suppress" error messages is to avoid situations that cause the error in the first place.Jonathan Boott, MCDBA
|
|
|
|
|
|
|
|
|
Urgent!! SQL Job is reporting as Failed.