February 8, 2007 at 9:34 am
OK, in a previous thread I was asking about Transactions and errors in a cursor. Now I have eliminated the cursor so now I'm just looking for a solution to handle errors within my transaction, but continue the batch.
Simple explanation and background. I must pull new employee (recruit) information from our recruiting DB and put it into our HR DB. The data coming out of the recruiting DB comes from 7-8 tables and must go into 4 tables in the HR DB. THe HR DB has design issues, such as no identity fields, although it DOES have PKs. It uses a separate table, "Thing", to hold the max PK value for each of the other tables, and when an app goes to insert a record it must go to the Thing table to find out the max value for the PK of a given table, increment it, use the value for the insert, and update the Thing table with the new max PK value for the given table. I have code that does this in my process.
How I'd like the process to go:
1. Begin While loop, pulling the SSNs of the recruits fitting the criteria, hired, etc.
2. Get max PK value for table1.
3. Update Max PK value for table1 to new PK value (+1).
4. Do the insert into table1 with new PK value and other data pulled from recruiting DB.
5. Get max PK value for table2.
6. Update max PK value for table2 with new PK value (+1).
7. Do the insert into table2 with new PK value and other data pulled from recruiting DB.
8. do same process as steps 2-4 for 3rd and 4th tables.
9. If ANYTHING fails in the preceding steps, except for 1, roll back the transaction and proceed to next SSN in While loop.
10. If nothing fails, commit this transcation and proceed to next SSN in While loop.
I have put error checking after every statement in the procedure, and I finally have it to a point where NO records are inserted if any one fails*, but it will not advance to the next SSN in the While loop.
* Other format os setting up the error checking provided unpredicatable results, most of which included a few records being inserted into some of the 4 tables, and all PK values being incremented by 1, before the process aborted, giving me no data integrity. The way I have the error checking set up now, altough it is ugly and not the preferred method, does give me data integrity in that no records are inserted and no PK values are incremented iof anything fails.
Source for 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 ON
Select @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
If @ErrorNum <> 0 GOTO ErrorHandler --Had to do this. If done with rollback here instead of in ErrorHandler records still got inserted into other tables.
Begin Tran
--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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
--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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
--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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
--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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
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 = @@Error
If @ErrorNum <> 0 GOTO ErrorHandler
Commit Tran --If made it this far, commit the tran.
CONTINUE --If made it this far, go start the next one.
GOTO EndOfProcess --Must not be any more to process, jump over Error Handler.
ErrorHandler:
ROLLBACK Tran
End
EndOfProcess:
Error message from running this:
Server: Msg 2627, Level 14, State 1, Line 132
Violation of PRIMARY KEY constraint 'PK_ReqBase_RbFlxID'. Cannot insert duplicate key in object 'ReqBase'.
Don't worry about the exact error here, as I have a solution for it. It is merely here to test the error handling routine. It is, however, a typcial error I see in the bad data I get sometimes.
Thanks,
Chris
February 8, 2007 at 11:06 am
This is NOT the best approach to take for this type of problem. It is best to use a set based approach with the data either being cleansed first or your queries taking this into account.
If you really want to continue with this approach:
1. SET XACT_ABORT ON will ROLLBACK all transactions and abort the SP as soon as an error occurs. It also makes your error handling code redundant!!!
2. SET XACT_ABORT OFF
3. Change WHILE @SS IS NOT NULL to WHILE 1 = 1
4. After SELECT @SS = ..., remove the error handling and enter:
IF @SS IS NULL
BREAK
5. Within the transaction replace the current error handling with:
IF @@ERROR <> 0
BEGIN
ROLLBACK
CONTINUE
END
6. Make sure this error handling occurs after EVERY statement. (At least some SELECTs from the Thing table are missing error handling at the moment.)
7. Get rid of:
CONTINUE --If made it this far, go start the next one.
GOTO ENDOFPROCESS --Must not be any more to process, jump over Error Handler.
ERRORHANDLER:
ROLLBACK TRAN
8. If other processes are going to be able to access the tables, put an UPDLOCK on the Thing table when selecting. eg:
SELECT @AppBaseID = THNUMBER + 1
FROM THING TH WITH (UPDLOCK, ROWLOCK)
INNER JOIN FLXTABLE FT
ON TH.THFLXID = FT.FTCOUNTER
WHERE FTTBLNAME = 'AppBase'
P.S. As this sort of code is potentially covering up problems it would NOT pass QA here.
(ie It is a bit like the apocryphal story of the programmer who ignored divide by zero errors in annuity calculations and lost an insurance company millions!) If there are problems, you want to know about them and intelligently decide how to deal with them.
February 8, 2007 at 12:27 pm
I agree about the code, and nont covering up problems. I also want the next guy to come along and support this to say, "at least he tried".
I have little control over the crappy data I get from the vendor, the story of my life, so I have to find intelligent and creative ways to deal with the data so I can keep my company going, and minimize my interaction with data. Haven't you ever had to deal with a vendor that did not know about business rules, data integrity, or proper database design? I won't get too deep into my opinions of this, but it seems like this describes every vendor I deal with, and none of them have much interest in fixing their problems.
I will design my queries as best I can to deal with the problematic data, but I need to also write the queries smart enough to not fail for minor problems and report the problems they do encounter.
The end goal this this procedure is to have to process what it can and report on what it couldn't.
I have implemented the changes you suggested. Without the XA ON it still does not work quite right. Table 1 gets 1 record, table 2 and 3 get 0 records, and table 4 gets 2 records.
Thanks,
Chris
February 9, 2007 at 5:54 am
Sorry if I seemed a little hard, but I have seen code like this cause too many problems.
On the question of the results, it is difficult for anyone to comment without DDL and sample data.
February 9, 2007 at 7:17 am
I'm right there with you, and I let me vendor contacts know. Many have been a recipient of a poster I created that says, "Garbage In Garbage Out", as a reminder to them. Then there's, "There's never time to do it right the first time but there's always time to do it over."
Welcome to my world.
I'm bringing MS in on this to see if they can help.
Thanks,
Chris
February 16, 2007 at 8:53 am
I had a discussion with a developer I know and he helped me work things out. Now, out of 30 possible inserts, 22 successfuly go in and the problem data does not affect everythign else.
Now I am trying to put error reporting into the proc so that when it does fail it keep going, but lets me know what failed after it completes.
So far, my error reporting, based on the idea that I have a variable just to store errors which I concatenate every error onto, then spit that variable out at the end of the procedure, doesn't work. Even though I still have failures in insert to at least one table, the error return is always nothing.
Current source for 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
DECLARE @ErrorTotal VARCHAR(2000)
Set NoCount OFF
Set Xact_Abort OFF
Select @SS = 0
While 1=1 --@SS Is Not Null
Begin
SELECT @ErrorNum = 0 --Reset the error variable.
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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = 'Fatal error occured getting recruit list. ' + CONVERT(VARCHAR(10), @ErrorNum)
BREAK
END
IF @SS IS NULL
Begin
BREAK
End
Begin Tran
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured while obtaining FlxID for AppBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--Print 'Table AppBase' --For troubleshooting
--Print 'AppBaseID= ' + Convert(Char(15),@AppBaseID) --For troubleshooting
--Check to see if any records are available, or more than one record will be returned for this insert before doing the actual insert.
IF (SELECT COUNT(*) 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) <> 1
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - No records returned for AppBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured on Insert to AppBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
PRINT @ErrorNum --For troublehsooting
PRINT @ErrorTotal --For troubleshooting.
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
--@ErrorTotal = @ErrorTotal + ' - Error occured while obtaining FlxID for AppEEO, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--Print 'Table AppEEO' --For Troubleshooting
--Print 'AppEEOID= ' + Convert(Char(15),@AppEEOID)
--Check to see if any records are available, or more than one record will be returned for this insert before doing the actual insert.
IF (SELECT COUNT(*) 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) <> 1
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - No records returned for AppEEO, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured on Insert to AppEEO, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured while obtaining FlxID for ReqBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--Print 'Table ReqBase' --For Troubleshooting
--Check to see if any records are available, or more than one record will be returned for this insert before doing the actual insert.
IF (SELECT COUNT(*) 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.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) <> 1
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - No records returned for ReqBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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.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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured on Insert to ReqBase, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured while obtaining FlxID for AppToReq, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
--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 = @@Error
If @ErrorNum <> 0
BEGIN
Select @ErrorTotal = @ErrorTotal + ' - Error occured on Insert to AppToReq, for SSN: ' + @SS + '. Error: ' + CONVERT(VARCHAR(10), @ErrorNum)
ROLLBACK
CONTINUE
END
Commit Tran
End
PRINT @ErrorTotal
IF @ErrorTotal <> ''
BEGIN
--RETURN @ErrorTotal --For use when stored procedure
PRINT @ErrorTotal --For use when intractive
END
ELSE
BEGIN
--Return 0 --For use when stored procedure
Print 0 --For use when intractive
END
Any ideas?
Thanks,
Chris
February 19, 2007 at 2:46 am
After declarations section in the beginning of your procedure.
SET @ErrorTotal = ' '
and try....
Thanks
Prasad Bhogadi
www.inforaise.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply