Urgent!! SQL Job is showing as Failed due to Error

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

SQL Server Administration (2000)

Urgent!! SQL Job is showing as Failed due to Error

Author

Topic

skaluva
Starting Member

17 Posts

Posted - 2002-07-09 : 17:46:13

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) < 2
Select @sTagSSN = '0' + @sTagSSN
Select @sUID = Rtrim(@sBranchID) + @sUID + @sTagSSN
Select @iSpecID = Case @iSpecID
When 7 then 13
When 11 then 7
Else
@iSpecID
End

Insert 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')
End
Fetch next from cusrNewEmployees into @sBranchID, @sFirstName, @sLastName, @sMidInit, @mRate, @mOTRate, @sSSN4, @iSpecID

End
Close cusrNewEmployees
Deallocate cusrNewEmployees

End -- End of Case 1

Thanks in Hand,

-- Sree

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-09 : 17:58:57

Cross Posting is not needed on these boards. The regulars here use the "Ative Topics" page, so we can see all the new questions as they come in.

I've posted a possible solution at your other question here:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=17536

<Yoda>Use the Search page you must. Find the answer you will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources