Transaction inside a cursor?
-
I can't imagine this is a new concept, but a search here didn't seem to turn up anything relevant.
I have a screwy database and crappy data coming in, just so you know up front. Nothing I can do about either at this point, which is why I am trying to build in error handling and recovery in my procedure.
I have to do inserts into 4 tables with data being selected from another database, for new recruits. There will be multiple records coming from the other DB, and I must maintain relationships as I do the insert.
I am using a cursor loop though the list of recruits, pulling each recruit's info from the source DB and insert it into the destination tables. Sometimes I get a duplicate record on the Select (remember the crappy data part?). I'd like to use a transaction to fail one recruit's inserts, but go on to the next recruit even though the previous failed.
My error handling looks like this:
Open PCursor
Fetch Next From PCursor Into @SS
While @@Fetch_Status <> -1
Begin
Select @ErrorNum = 0
Begin Transaction InsertNewEmp
<all the inserts for the recruit>
The following line goes after each SQL statement during the inserts.
Select @ErrorNum = @ErrorNum + @@Error
At the end of the inserts I do this:
If @ErrorNum > 0
Begin
ROLLBACK TRANSACTION InsertNewEmp
Print 'Error: ' + Convert(VarChar(15), @ErrorNum)
--Return @ErrorNum
End
Else
Begin
Commit Transaction InsertNewEmp
End
Fetch Next From PCursor Into @SS
End
I was hoping this would allow me to roll back all inserts for the one recruit if any insert failed, but when I run it, and it encounters the first error, first it does not handle the error correctly, because it completes the insert into the other tables, and second, it loops through to the next iteration of the cursor, the next recruit, and does the same thing, except it no longer inserts records in any table.
I'm doing Inserts into 4 tables, and another routine for each table to figure out the ID I need to insert into the primary key (remember the screwy DB?).
Here are some results from troubleshooting Prints I put into the code.:
AppBaseID= 282381
SS: 5494
AppEEO
AppEEOID= 153525548
ReqBase
ReqID= 165391969
Server: Msg 2627, Level 14, State 1, Line 132
Violation of PRIMARY KEY constraint 'PK_ReqBase_RbFlxID'. Cannot insert duplicate key in object 'ReqBase'.
The statement has been terminated.
AppToReqID= 165391970
Error: 2627
I have been reading about "Save Transaction <tran name>", but I have not been able to figure out if that is relevant to this situation since I want to either commit or rollback every statement inside a single iteration of a cursor.
I know this is confusing. Let me know what more info you need.
Thanks,
Chris
-
I doubt if you need to use a cursor for this. Why not just do a batch insert of everything in the correct order with the INSERT queries getting rid of the duplicates? If you have a large amount of data the inserts can be done in batches.
If you really want to use a cursor, I suspect the problem could be with the naming of the transactions. Try just using BEGIN TRAN, COMMIT and ROLLBACK.
-
I'd rather not use a cursor, whenever possible, but I have been unable to think of a way to do this without it. You see, in order to maintain data integrity, I must grab the ID created on the first insert and use it for the later inserts, and on top of that, these tables do not have Identity fields for the primary key. They are Int, but not Identity, and the way this vednor chose to do it is to keep the current latest ID value for X table in another table, so when I need to get an ID, I go to the other table, find out what the ID value for X table is, increment it, update the other table, then use that same value to do the insert into table X. This must happen for each of the 4 tables I am inserting into. This is another reason I want my transaction to work, so that the ID value does not get incremented if the insert fails.
The only logical reason I have been able to come up with for this design is because this DB must be supportable across different platforms, but I would think that all platforms would be able to have an Identity type of field in them, so maybe we are talking legacy too.
I will try removing the naming of the transaction now
Thanks,
Chris
-
The removal of the name did not help.
Here is the entire source of the procedure:
Declare @AppBaseID Int
Declare @AppID Int
Declare @AppEEOID Int
Declare @RBID Int
Declare @AppToReqID Int
Declare @SS VarChar(15) --Social Security Number.
Declare @ErrorNum Int
Set NoCount ON
Set Xact_Abort OFF
Declare PCursor Cursor Fast_Forward For
Select Replace(P.SS, '-', '')
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Where S.Status = 'Hired'
And C.CostCenterNum <> '99999'
And S.Current_Emp <> 1
And S.Former_Emp <> 1
And (P.SS Is Not Null And LTrim(P.SS) <> '') --Must not have blank SSN.
And Replace(P.SS, '-', '') Not In(Select apSocNum From VHR_DataSQL.dbo.AppBase)
And S.StatusDate > DateAdd(d, -5, GetDate())
Select @AppID = Max(apAppID) + 1 From AppBase
Open PCursor
Fetch Next From PCursor Into @SS
While @@Fetch_Status <> -1
Begin
Select @ErrorNum = 0
Begin Transaction
--Get new FlxID for AppBase table.
Select @AppBaseID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppBase'
Update TH
Set TH.thNumber = @AppBaseID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppBase'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table AppBase' --For troubleshooting
Print 'AppBaseID= ' + Convert(Char(15),@AppBaseID) --For troubleshooting
--Insert to AppBase.
Insert Into VHR_DataSQL.dbo.AppBase
(apFlxID, apFirstName, apLastName, apSocNum, apStreet1,
apStreet2, apCity, apState, apZip, apHomePhone, ApEMail,
ApStatus, apCountry, apUserName, apAppID)
Select
@AppBaseID, Left(S.FirstName, 20), Left(S.LastName, 20),
Left(Replace(P.SS, '-', ''), 9), Left(S.Address, 35), Left(S.Address2, 35), Left(S.City, 35),
Left(S.State, 35), Left(S.Zip, 10),
Left(Replace(S.Phone, '-', ''), 14), Left(S.Email, 64),
Left(J.Type, 30),
'USA', 'RMSImportProcess',
S.Submit_ID
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Where Replace(P.SS, '-', '') = @SS
Print 'SSN: ' + @SS
Select @ErrorNum = @ErrorNum + @@Error
--Get FlxID for AppEEO table.
Select @AppEEOID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppEEO'
Update TH
Set TH.thNumber = @AppEEOID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppEEO'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table AppEEO' --For Troubleshooting
Print 'AppEEOID= ' + Convert(Char(15),@AppEEOID)
--Insert to the AppEEO table.
Insert Into AppEEO (aoFlxID, aoFlxIDap, aoDateBorn, aoSex, aoEthnic, aoVeteran, aoUserName,
aoDateBeg)
Select
@AppEEOID,
@AppBaseID,
Convert(DateTime, P.DOB),
ED.Sex,
Left(EP.Profile_Name, 30),
IsNull(ED.MilStatus, 'None'),
'RMSImportProcess',
GetDate()
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.EEODETAILEDTABLE ED On S.Submit_ID = ED.Submit_ID
Inner Join RMS.dbo.eeoProfiles EP On ED.Profile_ID = EP.Profile_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Inner Join VHR_DataSQL.dbo.AppBase AP On Replace(P.SS, '-', '') = AP.apSocNum
Where Replace(P.SS, '-', '') = @SS
Select @ErrorNum = @ErrorNum + @@Error
--Print 'ReqBase ID' --For Troubleshooting
--Get latest FlxID for ReqBase.
Select @RBID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'ReqBase'
Update TH
Set TH.thNumber = @RBID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'ReqBase'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table ReqBase' --For Troubleshooting
--Insert to the ReqBase table.
Print 'ReqID= ' + Convert(VarChar(15),@RBID)
Insert Into ReqBase (rbFlxID, rbTitle, rbDateStart, rbDateEnd, rbSalary,
rbNewHireName, rbSupervisor, rbReqID, rbDivision, rbFlxIDjb,
rbSupervisorFlxIDeb, rbContactFlxIDeb, rbContact, rbUserName)
Select
@RBID,
Left(JL.JobTitle, 60),
Convert(DateTime,P.StartDate) AS StartDate,
Convert(DateTime,P.EndDate) AS EndDate,
Convert(Decimal(18,0), P.Salary),
Left(P.Sign_On, 40),
Left(M.FirstName + ' '+ M.LastName, 30) As ManagerName,
Left(J.REQ, 30),
Left(C.CostCenterNum, 30) As UnitNbr,
JB.jbFlxID,
M.[User_ID],
Case IsNumeric(U2.[User_Name])
When 1 Then U2.[User_Name]
Else Null
End as RecruiterID,
Left(U1.UserFirstName + ' ' + U1.UserLastName, 30) As Recruiter,
'RMSImportProcess'
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.JobLibrary JL On J.JobLibraryID = JL.JobLibraryID
Inner Join RMS.dbo.JobCodes JC On JL.JobCodeID = JC.JobCodeID
Inner Join RMS.dbo.Managers M On P.Man_ID = M.Man_ID
Inner Join RMS.dbo.Users U2 On M.Man_ID = U2.Man_ID
Inner Join RMS.dbo.Users U1 On M.[User_ID] = U1.[User_ID]
Inner Join RMS.dbo.tblBranchInfo BR On M.BranchID = BR.BranchID
Inner Join RMS.dbo.Clients CL On BR.BranchID = CL.BranchID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Inner Join VHR_DataSQL.dbo.JobCode JB On JB.jbJobCode = JC.JobCode
Inner Join VHR_DataSQL.dbo.AppBase AP On AP.apSocNum = Replace(P.SS, '-', '')
Where Replace(P.SS, '-', '') = @SS
Select @ErrorNum = @ErrorNum + @@Error
--Print 'AppToReq ID' --For Troubleshooting
--Get latest FlxID for the AppToReq table.
Select @AppToReqID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxID = FT.ftCounter
Where ftTblName = 'AppToReq'
Update TH
Set TH.thNumber = @AppToReqID
From Thing TH
Inner Join FlxTable FT On TH.thFlxID = FT.ftCounter
Where ftTblName = 'AppToReq'
Select @ErrorNum = @ErrorNum + @@Error
Print 'AppToReqID= ' + Convert(Char(15),@AppToReqID)
Print 'Table AppToReq' --For Troubleshooting
--Insert the necessary IDs from the AppBase and ReqBase tables to this link table.
Insert Into AppToReq (arFlxId, arFlxIDap, arFlxIDrb, arUserName)
Values(@AppToReqID, @AppBaseID, @RBID, 'RMSImportProcess')
Select @ErrorNum = @ErrorNum + @@Error
--SAVE TRANSACTION InsertNewEmp
If @ErrorNum <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error: ' + Convert(VarChar(15), @ErrorNum)
--Return @ErrorNum
End
Else
Begin
Commit Transaction
End
Fetch Next From PCursor Into @SS
End
Close PCursor
DEALLOCATE PCursor
The errors returned look like this:
Table AppBase
AppBaseID= 282382
SSN: 5750
Table AppEEO
AppEEOID= 153525549
Table ReqBase
ReqID= 165391971
Server: Msg 2627, Level 14, State 1, Line 129
Violation of PRIMARY KEY constraint 'PK_ReqBase_RbFlxID'. Cannot insert duplicate key in object 'ReqBase'.
The statement has been terminated.
AppToReqID= 165391972
Table AppToReq
Error: 2627
Table AppBase
AppBaseID= 282382
SSN: 0486
Table AppEEO
AppEEOID= 153525549
Table ReqBase
ReqID= 165391971
Server: Msg 2627, Level 14, State 1, Line 129
Violation of PRIMARY KEY constraint 'PK_ReqBase_RbFlxID'. Cannot insert duplicate key in object 'ReqBase'.
The statement has been terminated.
AppToReqID= 165391972
The current problem is that one record is inserted into every table except the ReqBase, which has the primary key violation. The 2 problems here are 1. that one record is inserted into every other table but ReqBase, even though all of the insterts are contained withint 1 transaction, and 2. only one record gets inserted into each table, of those that any record gets insterted. I find this strange because the 3rd insert is where the failure occurs, but the 4th insert, on the first iteration, works, yet no other iterations happen, such as all of the records get inserted into all tables except the ReqBase table, where the primary key violation is.
My goal is to insert all records that do not violate a PK on any table, since I cannot count on my incoming data to be clean (I am trying to remedy the bad data with the vendor, but they just aren't getting it)
Thanks,
Chris
-
OK, after thinking about it, I was able to eliminate the cursor. The transaction still does not work as expected though.
Here is the new source.
The results are still the same for the error messages, but the inserts act differently. Inserts for tables 1 and 2 work for the first record, and no others, no matter how many times I run the procedure. Inserts for table 3 never work due to the PK violation, and an insert happens for table 4 every time I run the procedure, but only for one insert.
Declare @AppBaseID Int
Declare @AppID Int
Declare @AppEEOID Int
Declare @RBID Int
Declare @AppToReqID Int
Declare @SS VarChar(15) --Social Security Number.
Declare @ErrorNum Int
Set NoCount ON
Set Xact_Abort OFF
Set @SS = 0
While @SS Is Not Null
Begin
Select @SS = Min(Replace(P.SS, '-', ''))
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Where S.Status = 'Hired'
And C.CostCenterNum <> '99999'
And S.Current_Emp <> 1
And S.Former_Emp <> 1
And (P.SS Is Not Null And LTrim(P.SS) <> '') --Must not have blank SSN.
And Replace(P.SS, '-', '') Not In(Select apSocNum From VHR_DataSQL.dbo.AppBase)
And S.StatusDate > DateAdd(d, -5, GetDate())
And Replace(P.SS, '-', '') > @SS
Select @ErrorNum = 0
Begin Transaction
--Get new FlxID for AppBase table.
Select @AppBaseID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppBase'
Update TH
Set TH.thNumber = @AppBaseID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppBase'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table AppBase' --For troubleshooting
Print 'AppBaseID= ' + Convert(Char(15),@AppBaseID) --For troubleshooting
--Insert to AppBase.
Insert Into VHR_DataSQL.dbo.AppBase
(apFlxID, apFirstName, apLastName, apSocNum, apStreet1,
apStreet2, apCity, apState, apZip, apHomePhone, ApEMail,
ApStatus, apCountry, apUserName, apAppID)
Select
@AppBaseID, Left(S.FirstName, 20), Left(S.LastName, 20),
Left(Replace(P.SS, '-', ''), 9), Left(S.Address, 35), Left(S.Address2, 35), Left(S.City, 35),
Left(S.State, 35), Left(S.Zip, 10),
Left(Replace(S.Phone, '-', ''), 14), Left(S.Email, 64),
Left(J.Type, 30),
'USA', 'RMSImportProcess',
S.Submit_ID
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Where Replace(P.SS, '-', '') = @SS
Print 'SSN: ' + @SS
Select @ErrorNum = @ErrorNum + @@Error
--Get FlxID for AppEEO table.
Select @AppEEOID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppEEO'
Update TH
Set TH.thNumber = @AppEEOID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'AppEEO'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table AppEEO' --For Troubleshooting
Print 'AppEEOID= ' + Convert(Char(15),@AppEEOID)
--Insert to the AppEEO table.
Insert Into AppEEO (aoFlxID, aoFlxIDap, aoDateBorn, aoSex, aoEthnic, aoVeteran, aoUserName,
aoDateBeg)
Select
@AppEEOID,
@AppBaseID,
Convert(DateTime, P.DOB),
ED.Sex,
Left(EP.Profile_Name, 30),
IsNull(ED.MilStatus, 'None'),
'RMSImportProcess',
GetDate()
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.EEODETAILEDTABLE ED On S.Submit_ID = ED.Submit_ID
Inner Join RMS.dbo.eeoProfiles EP On ED.Profile_ID = EP.Profile_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Inner Join VHR_DataSQL.dbo.AppBase AP On Replace(P.SS, '-', '') = AP.apSocNum
Where Replace(P.SS, '-', '') = @SS
Select @ErrorNum = @ErrorNum + @@Error
--Print 'ReqBase ID' --For Troubleshooting
--Get latest FlxID for ReqBase.
Select @RBID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'ReqBase'
Update TH
Set TH.thNumber = @RBID
From Thing TH
Inner Join FlxTable FT On TH.thFlxId = FT.ftCounter
Where ftTblName = 'ReqBase'
Select @ErrorNum = @ErrorNum + @@Error
Print 'Table ReqBase' --For Troubleshooting
--Insert to the ReqBase table.
Print 'ReqID= ' + Convert(VarChar(15),@RBID)
Insert Into ReqBase (rbFlxID, rbTitle, rbDateStart, rbDateEnd, rbSalary,
rbNewHireName, rbSupervisor, rbReqID, rbDivision, rbFlxIDjb,
rbSupervisorFlxIDeb, rbContactFlxIDeb, rbContact, rbUserName)
Select
@RBID,
Left(JL.JobTitle, 60),
Convert(DateTime,P.StartDate) AS StartDate,
Convert(DateTime,P.EndDate) AS EndDate,
Convert(Decimal(18,0), P.Salary),
Left(P.Sign_On, 40),
Left(M.FirstName + ' '+ M.LastName, 30) As ManagerName,
Left(J.REQ, 30),
Left(C.CostCenterNum, 30) As UnitNbr,
JB.jbFlxID,
M.[User_ID],
Case IsNumeric(U2.[User_Name])
When 1 Then U2.[User_Name]
Else Null
End as RecruiterID,
Left(U1.UserFirstName + ' ' + U1.UserLastName, 30) As Recruiter,
'RMSImportProcess'
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.JobLibrary JL On J.JobLibraryID = JL.JobLibraryID
Inner Join RMS.dbo.JobCodes JC On JL.JobCodeID = JC.JobCodeID
Inner Join RMS.dbo.Managers M On P.Man_ID = M.Man_ID
Inner Join RMS.dbo.Users U2 On M.Man_ID = U2.Man_ID
Inner Join RMS.dbo.Users U1 On M.[User_ID] = U1.[User_ID]
Inner Join RMS.dbo.tblBranchInfo BR On M.BranchID = BR.BranchID
Inner Join RMS.dbo.Clients CL On BR.BranchID = CL.BranchID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
Inner Join VHR_DataSQL.dbo.JobCode JB On JB.jbJobCode = JC.JobCode
Inner Join VHR_DataSQL.dbo.AppBase AP On AP.apSocNum = Replace(P.SS, '-', '')
Where Replace(P.SS, '-', '') = @SS
Select @ErrorNum = @ErrorNum + @@Error
--Print 'AppToReq ID' --For Troubleshooting
--Get latest FlxID for the AppToReq table.
Select @AppToReqID = thNumber + 1
From Thing TH
Inner Join FlxTable FT On TH.thFlxID = FT.ftCounter
Where ftTblName = 'AppToReq'
Update TH
Set TH.thNumber = @AppToReqID
From Thing TH
Inner Join FlxTable FT On TH.thFlxID = FT.ftCounter
Where ftTblName = 'AppToReq'
Select @ErrorNum = @ErrorNum + @@Error
Print 'AppToReqID= ' + Convert(Char(15),@AppToReqID)
Print 'Table AppToReq' --For Troubleshooting
--Insert the necessary IDs from the AppBase and ReqBase tables to this link table.
Insert Into AppToReq (arFlxId, arFlxIDap, arFlxIDrb, arUserName)
Values(@AppToReqID, @AppBaseID, @RBID, 'RMSImportProcess')
Select @ErrorNum = @ErrorNum + @@Error
--SAVE TRANSACTION InsertNewEmp
If @ErrorNum <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error: ' + Convert(VarChar(15), @ErrorNum)
--Return @ErrorNum
End
Else
Begin
Commit Transaction
End
End
Thanks,
Chris
-
Your second SP still uses a pseudo cursor.
You should really look at writing set based code. To do this one needs to know the DDL (CREATE TABLE with PKs and FKs etc) of the source and destination tables. Temporary tables may needed to generate IDs etc.
-
Hi Chris,
You check for the Error count after every Insert or update statement and do a rollback on each check if the count is greater than zero also check the max(rbFlxID) from ReqBase table and see if your thNumber in Thing table is less than that for the filter you are applying to retrieve the thNumber.
Obviously your Insert is failing as you are trying to insert a duplicate key into ReqBase table. I don't won't to comment on the design of your tables as you have already mentioned that they were not properly designed.
Prasad Bhogadi
www.inforaise.com -
Prasad,
Changing that, either using
If @@Error > 1
Or
Set @ErrorNum = @@Error
If @ErrorNum > 1
actually did not help, but did change the behavior slightly.
Out of what should be 13 records, 13 recruits, I got one record in tables 1 and 2. Table 3, got none, due to the PK violation, and table 4, which is a link table between table 1 and table 3, got 2 records.
I'm going to have to analyze this some more to see if I can figure out what's going on, besides the obvious fact that the error checking is not working.
Thanks,
Chris
PS - I believe that if you design a DB for a product, you should have to go through a public review of the design before you are allowed to sell it.
-
Ok, Check this SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
BEGIN
ROLLBACK TRANSACTION
END
Prasad Bhogadi
www.inforaise.com -
I see the problem use this syntax...
IF @ErrorNum <>0
GO TO ErrHandler
at the bottom, just write a COMMIT TRANSACTION
On the ErrHandler :
ROLLBACK TRANSACTION
Prasad Bhogadi
www.inforaise.com -
This what you had in mind?
Select @ErrorNum = @@Error
If @ErrorNum <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error: ' + Convert(VarChar(15), @ErrorNum)
--Return @ErrorNum
End
It's only getting worse. Now I get the same error about the PK as before, although the error checking should handle it, and on top of that I get this error too:
Server: Msg 3902, Level 16, State 1, Line 246
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Despite getting that error, it is still inserting one record in tables 1 and 2, and 11 records into table 4. The entire thing is enclosed in this one transaction, within the While loop.
It should drop out of the inserts for the one recruit and go to the next recruit of there is a failure on any insert for that recruit.
Oh, the PK problem is solved now, as I received word from the analyst that we could eliminate the table join that was causing multiple records from the source DB. This is good, but I am keeping it in there for now, so I can use it to help perfect the error handling aspect of the procedure.
Thanks,
Chris
-
No luck there. I get the same behavior from that also.
Thanks,
Chris
-
Give this a try
Declare @AppBaseID Int
Declare @AppID Int
Declare @AppEEOID Int
Declare @RBID Int
Declare @AppToReqID Int
Declare @SS VarChar(15) --Social Security Number.
Declare @ErrorNum Int
SET NoCount ON
SET Xact_Abort OFF
SET @SS = 0
WHILE @SS Is NOT NULL
BEGIN
SELECT @SS = Min(REPLACE (P.SS, '-', ''))
FROM RMS.dbo.Submits S
INNER Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
INNER Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
INNER Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
WHERE S.Status = 'Hired'
AND C.CostCenterNum <> '99999'
AND S.Current_Emp <> 1
AND S.Former_Emp <> 1
AND (P.SS Is NOT NULL AND LTrim(P.SS) <> '') --Must not have blank SSN.
AND REPLACE (P.SS, '-', '') Not In(SELECT apSocNum FROM VHR_DataSQL.dbo.AppBase)
AND S.StatusDate > DATEADD (d, -5, GETDATE())
AND REPLACE (P.SS, '-', '') > @SS
SELECT @ErrorNum = 0
BEGIN TRANSACTION
--Get new FlxID for AppBase table.
SELECT @AppBaseID = thNumber + 1
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'AppBase'
UPDATE TH
SET TH.thNumber = @AppBaseID
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'AppBase'
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
Print 'Table AppBase' --For troubleshooting
Print 'AppBaseID= ' + Convert(Char(15),@AppBaseID) --For troubleshooting
--Insert to AppBase.
Insert Into VHR_DataSQL.dbo.AppBase
(apFlxID, apFirstName, apLastName, apSocNum, apStreet1,
apStreet2, apCity, apState, apZip, apHomePhone, ApEMail,
ApStatus, apCountry, apUserName, apAppID)
SELECT
@AppBaseID, LEFT (S.FirstName, 20), LEFT (S.LastName, 20),
LEFT (REPLACE (P.SS, '-', ''), 9), LEFT (S.Address, 35), LEFT (S.Address2, 35), LEFT (S.City, 35),
LEFT (S.State, 35), LEFT (S.Zip, 10),
LEFT (REPLACE (S.Phone, '-', ''), 14), LEFT (S.Email, 64),
LEFT (J.Type, 30),
'USA', 'RMSImportProcess',
S.Submit_ID
FROM RMS.dbo.Submits S
INNER Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
INNER Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
INNER Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
WHERE REPLACE (P.SS, '-', '') = @SS
Print 'SSN: ' + @SS
SET @ErrorNum =@@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
--Get FlxID for AppEEO table.
SELECT @AppEEOID = thNumber + 1
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'AppEEO'
UPDATE TH
SET TH.thNumber = @AppEEOID
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'AppEEO'
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
Print 'Table AppEEO' --For Troubleshooting
Print 'AppEEOID= ' + Convert(Char(15),@AppEEOID)
--Insert to the AppEEO table.
Insert Into AppEEO (aoFlxID, aoFlxIDap, aoDateBorn, aoSex, aoEthnic, aoVeteran, aoUserName,
aoDateBeg)
SELECT
@AppEEOID,
@AppBaseID,
Convert(DateTime, P.DOB),
ED.Sex,
LEFT (EP.Profile_Name, 30),
IsNull(ED.MilStatus, 'None'),
'RMSImportProcess',
GETDATE()
FROM RMS.dbo.Submits S
INNER Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
INNER Join RMS.dbo.EEODETAILEDTABLE ED On S.Submit_ID = ED.Submit_ID
INNER Join RMS.dbo.eeoProfiles EP On ED.Profile_ID = EP.Profile_ID
INNER Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
INNER Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
INNER Join VHR_DataSQL.dbo.AppBase AP On REPLACE (P.SS, '-', '') = AP.apSocNum
WHERE REPLACE (P.SS, '-', '') = @SS
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
--Print 'ReqBase ID' --For Troubleshooting
--Get latest FlxID for ReqBase.
SELECT @RBID = thNumber + 1
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'ReqBase'
UPDATE TH
SET TH.thNumber = @RBID
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxId = FT.ftCounter
WHERE ftTblName = 'ReqBase'
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
Print 'Table ReqBase' --For Troubleshooting
--Insert to the ReqBase table.
Print 'ReqID= ' + Convert(VarChar(15),@RBID)
Insert Into ReqBase (rbFlxID, rbTitle, rbDateStart, rbDateEnd, rbSalary,
rbNewHireName, rbSupervisor, rbReqID, rbDivision, rbFlxIDjb,
rbSupervisorFlxIDeb, rbContactFlxIDeb, rbContact, rbUserName)
SELECT
@RBID,
LEFT (JL.JobTitle, 60),
Convert(DateTime,P.StartDate) AS StartDate,
Convert(DateTime,P.EndDate) AS EndDate,
Convert(Decimal(18,0), P.Salary),
LEFT (P.Sign_On, 40),
LEFT (M.FirstName + ' '+ M.LastName, 30) As ManagerName,
LEFT (J.REQ, 30),
LEFT (C.CostCenterNum, 30) As UnitNbr,
JB.jbFlxID,
M.[User_ID],
Case IsNumeric(U2.[User_Name])
When 1 Then U2.[User_Name]
Else Null
End as RecruiterID,
LEFT (U1.UserFirstName + ' ' + U1.UserLastName, 30) As Recruiter,
'RMSImportProcess'
FROM RMS.dbo.Submits S
INNER Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
INNER Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
INNER Join RMS.dbo.JobLibrary JL On J.JobLibraryID = JL.JobLibraryID
INNER Join RMS.dbo.JobCodes JC On JL.JobCodeID = JC.JobCodeID
INNER Join RMS.dbo.Managers M On P.Man_ID = M.Man_ID
INNER Join RMS.dbo.Users U2 On M.Man_ID = U2.Man_ID
INNER Join RMS.dbo.Users U1 On M.[User_ID] = U1.[User_ID]
INNER Join RMS.dbo.tblBranchInfo BR On M.BranchID = BR.BranchID
INNER Join RMS.dbo.Clients CL On BR.BranchID = CL.BranchID
INNER Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
INNER Join VHR_DataSQL.dbo.JobCode JB On JB.jbJobCode = JC.JobCode
INNER Join VHR_DataSQL.dbo.AppBase AP On AP.apSocNum = REPLACE (P.SS, '-', '')
WHERE REPLACE (P.SS, '-', '') = @SS
SET @ErrorNum =@@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
--Print 'AppToReq ID' --For Troubleshooting
--Get latest FlxID for the AppToReq table.
SELECT @AppToReqID = thNumber + 1
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxID = FT.ftCounter
WHERE ftTblName = 'AppToReq'
UPDATE TH
SET TH.thNumber = @AppToReqID
FROM Thing TH
INNER Join FlxTable FT On TH.thFlxID = FT.ftCounter
WHERE ftTblName = 'AppToReq'
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
Print 'AppToReqID= ' + Convert(Char(15),@AppToReqID)
Print 'Table AppToReq' --For Troubleshooting
--Insert the necessary IDs FROM the AppBase AND ReqBase tables to this link table.
Insert Into AppToReq (arFlxId, arFlxIDap, arFlxIDrb, arUserName)
Values(@AppToReqID, @AppBaseID, @RBID, 'RMSImportProcess')
SET @ErrorNum = @@ERROR
IF @ErrorNum <>0
GOTO ErrHandler
COMMIT TRANSACTION
RETURN
ErrHandler:
ROLLBACK
RETURN
END
Prasad Bhogadi
www.inforaise.com -
It works better. I still get the error message from the PK violation though, and it doesn't go on to the next recruit.
Server: Msg 2627, Level 14, State 1, Line 142
Violation of PRIMARY KEY constraint 'PK_ReqBase_RbFlxID'. Cannot insert duplicate key in object 'ReqBase'.
The statement has been terminated.
The good thing here, though, is that no records are inserted into any tables with this procedure.
Thanks,
Chris
-
Chris,
To avoid the primary key violation error, you'll have to either eliminate the duplicates before trying to INSERT the data, or by checking for duplicates as part of the INSERT.
In the first method, you could first delete any existing rows in your source data, then insert everything.
Or, add a NOT EXISTS (SELECT * FROM WHERE ...)
to the SELECT statement portion of the INSERT.
For example, for the ReqBase table insert:
...
WHERE REPLACE (P.SS, '-', '') = @SS
AND NOT EXISTS (SELECT * FROM ReqBase WHERE ReqBase.rbFlxID = @RBID)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply