January 5, 2011 at 2:46 am
Hi Expert !!! in the following code i get Violation of Primary key Error.. when i debug this error it should to this line
"Set @FName= @EmpName
Set @MName = Null
Set @LName = Null"
Could anybody help me sort out this problem
Declare @EmpID int
Declare @FName nvarchar(50)
Declare @MName nvarchar(20)
Declare @LName nvarchar(20)
Declare @JTitle nvarchar(250)
Declare @EmpCode nvarchar(150)
Declare @OfficeTel nvarchar(150)
Declare @mobile nvarchar(100)
--Declare @pager nvarchar(100)
Declare @hometel nvarchar(100)
Declare @manager int
Declare @fax nvarchar(100)
Declare @email nvarchar(100)
Declare @Work_Street nvarchar(100)
Declare @Work_block nvarchar(100)
Declare @Work_city nvarchar(100)
Declare @Work_Zip nvarchar(100)
Declare @Work_Country nvarchar(100)
Declare @home_Street nvarchar(100)
Declare @home_block nvarchar(100)
Declare @home_city nvarchar(100)
Declare @home_Zip nvarchar(100)
Declare @home_Country nvarchar(100)
Declare @StartDate datetime
Declare @status nvarchar(50)
Declare @termdate datetime
Declare @Sex nvarchar(10)
Declare @birthdate datetime
Declare @martstatus nvarchar(10)
Declare @govID nvarchar(100)
Declare @Salary money
Declare @bankcode nvarchar(200)
Declare @bankAccount nvarchar(100)
Declare @bankBranch nvarchar(100)
Declare @EmpName nvarchar(500)
Declare @DeptCode nvarchar(100)
Declare @DesgCD nvarchar(100)
Declare @CAddress1 nvarchar(500)
Declare @CAddress2 nvarchar(500)
Declare @PAddress1 nvarchar(500)
Declare @PAddress2 nvarchar(500)
Declare @branch nvarchar(200)
Declare @IndexofSpace int
Declare @IndexofSpace2 int
Declare @U_Name nvarchar(200)
Declare @TempName nvarchar(200)
Declare @SecondThirdName nvarchar(200)
DECLARE C5 CURSOR
FOR
Select Emp_Cd,Emp_Nm,Birth_Dt,Gender_Cd,
(Select GMDesgMst.Desg_Nm from GMDesgMst
Where GMDesgMst.Desg_Cd=GMEmpMst.Desg_Cd) As Designation,Desg_Cd,
GMEmpMst.Dept_Cd,Join_Dt,Passpt_Cd,
Emp_Cat_Cd As Emp_Status,
GMEmpMst.Branch_Cd,Marital_Cd,Cr_Add1 ,Cr_Add2 ,Cr_Pincode,
Pr_Add1,Pr_Add2,Pr_Pincode,Phone1,Phone2,mobile,Email,SalBankNm,
SalBankBranch,Salary_Ac_No,Present_CTC,PanNo As ContractEndDate
From GMEMPMST
OPEN C5
Set @EmpID=1
FETCH NEXT FROM C5
INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@DesgCD,@StartDate,
@govID,@Status,@branch,
@martstatus,@CAddress1,@CAddress2,@Work_Zip,
@PAddress1,@PAddress2,@home_Zip,
@OfficeTel,@hometel,@mobile,
@email,@bankcode,@bankBranch,@bankAccount,
@Salary,@termdate
WHILE @@FETCH_STATUS = 0
BEGIN
If Not Exists(Select empid from SBO_Rehnuma.dbo.OHEM
where SBO_Rehnuma.dbo.OHEM.jobTitle=@EmpCode)
Begin
--Select @DeptCode
Set @DeptCode=(Select SBO_Rehnuma.dbo.OUDP.Code from SBO_Rehnuma.dbo.OUDP
Where SBO_Rehnuma.dbo.OUDP.U_HRMSCode=@DeptCode)
----Select @DesgCode
Set @DesgCD=(Select SBO_Rehnuma.dbo.OHPS.Name from SBO_Rehnuma.dbo.OHPS
Where SBO_Rehnuma.dbo.OHPS.Name=@DesgCD)
--Select @DesgCD
--Select @DeptCode
Set @bankcode=(Select top 1 T1.BankCode From SBO_Rehnuma.dbo.ODSC As T1
Where T1.BankName Like @bankcode)
Set @branch=(Select SBO_Rehnuma.dbo.OUBR.Code from SBO_Rehnuma.dbo.OUBR
Where SBO_Rehnuma.dbo.OUBR.Remarks=@branch)
--Select @status
Set @status= (Select statusID From SBO_Rehnuma.dbo.OHST where descriptio Like @status)
Set @U_Name= @empname
Set @IndexofSpace= CharIndex(' ',@EmpName ,0)
if (@IndexofSpace> 0)
Begin
Set @FName = SubString(@EmpName,0,@IndexofSpace)
Set @SecondThirdName = SubString(@EmpName,@IndexofSpace+1,len(@Empname))
Set @IndexofSpace2 = CharIndex(' ',@SecondThirdName,0)
if(@IndexofSpace2 > 0 )
Begin
Set @MName = SubString (@SecondThirdName,0,@IndexofSpace2)
Set @LName = SubString(@SecondThirdName,@IndexofSpace2,len(@Empname))
End
Else
Begin
Set @MName = @SecondThirdName
Set @LName = Null
End
End
Else
Begin
Set @FName= @EmpName
Set @MName = Null
Set @LName = Null
End
Insert Into SBO_Rehnuma.dbo.OHEM(empid,jobTitle,
firstName,middleName,lastName,
sex,dept,branch,
workStreet,workBlock,workZip,workCountr,
officeTel,mobile,homeTel,
email,startDate,status,
salary,termDate,
bankCode,bankBranch,bankAcount,
homeStreet,homeBlock,homeZip,
homeCountr,birthDate,
govID,U_JobTitle,U_EmpName,position)
Values(@EmpID,@EmpCode,
@FName,@MName,@LName,@Sex,@DeptCode,@branch,
@CAddress1,@CAddress2,@Work_Zip,'PK',
@OfficeTel,@hometel,@mobile,@email,@StartDate,
@status,@Salary,@termdate,@bankcode,@bankBranch,@bankAccount,
@PAddress1,@PAddress2,@home_Zip,'PK',
@birthdate,@govID,@JTitle,@U_Name,@DesgCD)
End
Set @EmpID=@EmpID+1
FETCH NEXT FROM C5
INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@DesgCD,@StartDate,
@govID,@Status,@branch,
@martstatus,@CAddress1,@CAddress2,@Work_Zip,
@PAddress1,@PAddress2,@home_Zip,
@OfficeTel,@hometel,@mobile,
@email,@bankcode,@bankBranch,@bankAccount,
@Salary,@termdate
END
CLOSE C5
DEALLOCATE C5
January 5, 2011 at 2:53 am
violation is coming from this statement (it's the only insert/update):
Insert Into SBO_Rehnuma.dbo.OHEM(empid,jobTitle,
firstName,middleName,lastName,
sex,dept,branch,
workStreet,workBlock,workZip,workCountr,
officeTel,mobile,homeTel,
email,startDate,status,
salary,termDate,
bankCode,bankBranch,bankAcount,
homeStreet,homeBlock,homeZip,
homeCountr,birthDate,
govID,U_JobTitle,U_EmpName,position)
Values( @EmpID,@EmpCode,
@FName,@MName,@LName,@Sex,@DeptCode,@branch,
@CAddress1,@CAddress2,@Work_Zip,'PK',
@OfficeTel,@hometel,@mobile,@email,@StartDate,
@Status,@Salary,@termdate,@bankcode,@bankBranch,@bankAccount,
@PAddress1,@PAddress2,@home_Zip,'PK',
@birthdate,@govID,@JTitle,@U_Name,@DesgCD)
We'll need to see the DDL on table SBO_Rehnuma.dbo.OHEM to answer your question. Check out the first link in my signature if you need help to get that from your system.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2011 at 3:38 am
Hi i have already seen the DDL on table SBO_Rehnuma.dbo.OHEM .
But i couldn't found any answer related to this table OHEM..
so please guide me what should i have to do now...
Thanx
January 5, 2011 at 3:42 am
rizkhan92 (1/5/2011)
Hi i have already seen the DDL on table SBO_Rehnuma.dbo.OHEM .But i couldn't found any answer related to this table OHEM..
so please guide me what should i have to do now...
Thanx
You have, we haven't, thus, we can't help you.
A Primary Key failure is a duplicate value being added into a primary key constraint. Thus, the DDL tells us what that key is, to help you locate what you need to find in your data to this.
I have guided you what to do now... which is post the table's DDL.
Your query is producing a duplicated key. Either in that table, or via an insert trigger on that table to another table. Help us help you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2011 at 3:49 am
What would also help if you simply could post the full error message. It usually tells the table and primary key that's violated.
And I don't think it's an issue with thinsert statement. You most probalby simply have duplicate values in your c.u.r.s.o.r. *cough* declaration.
Question aside: Why do you do it using a RBAR solution instead of set based?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply