September 7, 2008 at 7:29 pm
I have a few question regarding this issue. manager has received the automated email about another person 'status change email for ' aaa' who is not in same department as manager 'bbb' which is incorrect. there is autoupdate on the account using ssis package. i have found which proc is used to send out the email. i tryed to figuer out the value that has been passed as manager id and employee id which was received as mistake. and it did not give any conclusion. proc is like in below, does any one have any expecience of such issue or any kind of help.
Thanks
sagar
ALTER PROCEDURE [dbo].[spDeactivateUsers]
AS
BEGIN
SET NOCOUNT ON
DECLARE -- Local Variables
@useridINT
,@MaxUserIdint
,@HomeDivisionIDINT
,@UserDivisionIdint
,@MaxDivisionIdint
,@ProjectMemberIdINT
,@ProjectIdINT
,@MaxProjectIdint
,@JobTypeIdINT
,@ManagerOfJobTypeIdINT
,@ManagerUserIdINT
,@CostCenterIdINT
,@SiteIdINT
,@SendErrorEmailINT
,@ProjectDivisionIdINT
,@ErrorDescriptionVARCHAR(1000)
,@ErrorMsgRecipientVARCHAR(800)
,@ErrorMessageVARCHAR(1000)
,@DoneUserbit
,@DoneProjectbit
,@DoneDivisionbit
,@AdminIDint
SELECT @AdminID = dbo.fnCommon_GetAdminUserID()
INSERT tblsd_Progress (Message) values ('spDeactivateUsers Started')
SET @ErrorMsgRecipient = 'Admin@abc.com'
DECLARE @TermedUsers table (UserId int, DivisionId int)
DECLARE @ProjectList table (ProjectID int)
DECLARE @DivisionList table (DivisionId int, ManagerUserID int)
BEGIN TRY -- DeActivate employees
BEGIN TRANSACTION
--add users who are active in resource and are inactive in SDpackage
INSERT @TermedUsers (UserId, DivisionId)
SELECT u.UserId, u.DivisionId
FROM tblsd_Dumps cd
INNER JOIN tblUsers u ON cd.Wwid = u.Wwid
WHERE
dbo.fnsd_IsActive(cd.StatCode) = 0
AND u.IsActive = 'True'
--add users who are active in resource but their employee status code has changed to a status code
--that is not included in the divisions sd Options
--don't add any users who've already been added to the temp file
INSERT @TermedUsers (UserId, DivisionId)
SELECT u.UserId, u.DivisionId
FROM tblsd_Dumps cd
INNER JOIN tblUsers u ON cd.Wwid = u.Wwid
INNER JOIN tblDivisions div on div.DivisionID=u.DivisionID
LEFT JOIN tblCdisOptions opt on cd.DivisionID=opt.DivisionId and cd.EmpTypeCode=opt.EmployeeTypeCode
LEFT JOIN @TermedUsers t on u.UserID=t.UserId AND u.DivisionID = t.DivisionId
WHERE div.Active=1
AND u.IsActive = 'True'
AND opt.EmployeeTypeCode IS NULL
AND t.UserID IS NULL
--add user are active and their status code is not included in the divisions options
--don't add any users who've already been added to the temp file
INSERT @TermedUsers (UserId, DivisionId)
SELECT u.UserId, u.DivisionId
FROM tblUsers u
INNER JOIN tblDivisions div on div.DivisionID=u.DivisionID
LEFT JOIN tblCdisOptions opt on u.DivisionID=opt.DivisionId and u.EmpTypeCode=opt.EmployeeTypeCode
LEFT JOIN @TermedUsers t on u.UserID=t.UserId AND u.DivisionID = t.DivisionId
WHERE div.Active=1
AND u.IsActive = 'True'
AND opt.EmployeeTypeCode IS NULL
AND t.UserID IS NULL
AND u.EmpTypeCode IS NOT NULL
SET @DoneUser=0
SET @userid=-1
SELECT @MaxUserId = max(UserID) From @TermedUsers
--loop thru users
WHILE @DoneUser=0 AND @MaxUserID IS NOT NULL
BEGIN
SELECT @userid = min(UserID) FROM @TermedUsers WHERE UserId>@UserID
SELECT @HomeDivisionID = DivisionID FROM @TermedUsers WHERE UserId=@UserID
IF @userid=@MaxUserId
SET @DoneUser=1
delete from @ProjectList
INSERT @ProjectList
SELECT distinct ProjectId FROM tblProjectMembers WHERE ProjectMemberUserID = @userid
SET @DoneProject=0
SET @ProjectID=-1
SELECT @MaxProjectID = max(ProjectID) FROM @ProjectList
--loop thru projects for current user
WHILE @DoneProject=0 AND @MaxProjectID IS NOT NULL
BEGIN
SELECT @ProjectID = min(ProjectID) FROM @ProjectList WHERE ProjectID>@ProjectID
IF @ProjectID=@MaxProjectID
SET @DoneProject=1
--get the division that the project belongs to
SELECT @ProjectDivisionId = pl.DivisionId
FROM tblProjects prj
INNER JOIN tblPrograms pgm ON pgm.ProgramId = prj.ProgramId
INNER JOIN tblProductLines pl ON pl.ProductLineId = pgm.ProductLineId
WHERE prj.ProjectId = @ProjectId
-- Get the Manager UserId and CostCenter of the soon to be disabled user
-- for the forcasted project division
SELECT @ManagerOfJobTypeId = UserId
,@CostCenterId = CostCenterID
,@SiteId = SiteId
FROM tblUsers
WHERE UserId = (SELECT ManagerUserId FROM tblUserDetails WHERE UserId = @userid
AND DivisionID = @ProjectDivisionId)
-- Update the User record for the division
UPDATE tblProjectMembers
SET ManagerOfJobTypeId = @ManagerOfJobTypeId
,SiteId = @SiteId
,CostCenterId = @CostCenterId
,ProjectMemberUserId = NULL
WHERE ProjectMemberUserId = @userid
AND ProjectId = @ProjectId
END -- loop thru @ProjectList
--loop thru each division the user belonged to and update capital and bti forecasts
delete from @DivisionList
INSERT @DivisionList
SELECT DivisionID, ManagerUserID
FROM tblUserDetails WHERE UserID=@UserID
SET @DoneDivision=0
SET @UserDivisionId=-1
SELECT @MaxDivisionID = max(DivisionId) FROM @DivisionList
--loop thru projects for current user
WHILE @DoneDivision=0 AND @MaxDivisionID IS NOT NULL
BEGIN
SELECT @UserDivisionId = min(DivisionID) FROM @DivisionList WHERE DivisionID>@UserDivisionId
SELECT @ManagerUserId = ManagerUserID FROM @DivisionList WHERE DivisionID=@UserDivisionId
IF @UserDivisionID=@MaxDivisionID
SET @DoneDivision=1
--this will reassign the bti and capital forecasts for this user
EXEC spUserLeftDivision @userid,@UserDivisionId,@ManagerUserID
--reset manageruserid of any people reporting to the termed user
UPDATE tblUserDetails SET
ManagerUserID=@ManagerUserID
,ChangedUserId=@AdminID
,ChangedDate=GetDate()
WHERE ManagerUserID=@UserID and DivisionID=@UserDivisionId
END --loop thru divisions the user belongs to
-- Delete user detail records (shared resource) except for division record
DELETE FROM tblUserDetails
WHERE UserId = @userid AND DivisionId <> @HomeDivisionID
-- Deactivate user in tblUsers
UPDATE tblUsers
SET IsActive = '0'
,ChangedUserId=@AdminID
,ChangedDate=GetDate()
WHERE UserId = @userid
-- Send manager an email telling them employee deactivated
SET @ManagerUserId = (SELECT @ManagerUserId FROM tblUserDetails WHERE UserId = @userid AND DivisionId = @HomeDivisionID)
EXEC spEmailManagerLeavingEmployee @userid, @ManagerUserId
END -- Loop thru @TermedUsers
commit TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
--Make sure the cursors exist before closing/deallocating them
IF CURSOR_STATUS('local', 'ProjectMemberCursor') <> -3
BEGIN
CLOSE ProjectMemberCursor
DEALLOCATE ProjectMemberCursor
END
IF CURSOR_STATUS('local', 'DeactivateUserCursor') <> -3
BEGIN
CLOSE DeactivateUserCursor
DEALLOCATE DeactivateUserCursor
END
SET @ErrorMessage = 'The deactivae employees stored procedure had a critical error: ' + (SELECT ERROR_MESSAGE())
EXEC spCommon_SendEMail @ErrorMsgRecipient, 'Deactivate Users Failed', @ErrorMessage
RETURN
END CATCH;
END -- Procedure spCdis_DeactivateUsers
September 8, 2008 at 12:20 am
Hello,
If you have a tool such as Visual Studio then you can use the Debugger to step through the execution of the code and see the logic flow.
Without a debugger, you could add in Print Statements at relevant decision points in the SP and that will help you track down the issue.
I assume you have a none-production environment that you can test his SP in.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 9, 2008 at 7:10 pm
First thing I would do is to put a lot of debugging select statements and print statements in the SP (I wrap them in a comment block to know where to clean up later), remark out all your transaction handling, then run it in a dev environtment wrapped in a transaction that rolls back (lets me run it over and over with the same data)
IE:
BEGIN TRAN
EXEC spDeactivateUsers
ROLLBACK
If I don't find the problem that way, I'll go to Visual Studio and step into the SP after I have changed all the variable/temp tables to fixed tables. I do this so that I can stop at any point in the SP and run a query against the working tables using the WITH(NOLOCK) hint on the tables.
BTW: I would suggest changing the SP name to NOT start with SP! 🙂
Gary Johnson
Sr Database Engineer
September 9, 2008 at 7:56 pm
Thanks Gary. That would definitely help me lot... i never debug stored proc in visual studio.. so do i just execute as i do in management studio or any other process.
September 9, 2008 at 11:47 pm
Hello,
In Visual Studio (2005) - if you don’t already have a (test/dev) Database Project now would be a good time to create one. Add a DB Reference to your test/dev DB.
Alternatively you can just add the DB Reference directly in the Server Explorer window.
Either way, in the Server Explorer window, you can then browse to the SP you want to debug, right click and select Step into Stored Procedure.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 10, 2008 at 11:52 am
To debug in Visual Studio you need to open the Server Explorer window. Add a data source for your database. Then drill down to the stored procedure. Right click on it and select "Step into Stored Procedure". You can then step through the SP and put break points on the SP.
I've done all my development with Visual Studio for the last 12 years. I use the 2nd tier database project (other projects/database project). This allows me to maintain all my scripts in a Source Control environment, and to use an extensive library of macros specific to the way I work.
I've recently moved to VS2008 For Database Developers, but I'm not too happy with the 1st tier DB project at this point. I lose far too much functionality that I have in the 2nd tier project.
Gary Johnson
Sr Database Engineer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply