User profile for Giggles220

Here you go. The only thing I changed was the email address in the code. USE [TUMS] GO /****** Object: StoredProcedure [dbo].[uspx_EOD2375SendUserDivBurChangeNotification] Script Date: 05/19/2008 08:29:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Alka G -- Create date: 05/15/08 -- Description: This proc sends a warning Email message to EOD mail box -- in case it is detected that -- EOD2375 Application Users have moved -- within the agency from one Division or Bureau to another -- -- It is possible that email messages may even get sent -- in case there is name change of division or bureau. -- -- Called from: usp_UserRolesBatchProcessing - a nightly job executes this proc -- ============================================= ALTER PROCEDURE [dbo].[uspx_EOD2375SendUserDivBurChangeNotification] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here Declare @MovedUsers Table ( RowNumber int , TumsUserID int , UserID varchar(6) , employeeName varchar(100) ) ; With EmpCurHeader As ( SELECT eh.emp_LastName , eh.emp_FirstName , eh.emp_MI , a.APPOINTMENT_DIVISION_ID As DivisionID , a.APPOINTMENT_BUREAU_ID As BureauID , d.DIVISION_CODE As Division , b.BUREAU_CODE As Bureau , eh.emp_userid FROM ATC.dbo.Employee_Header AS eh INNER JOIN HRIS_Prod.dbo.EMPLOYEES AS e ON eh.emp_ssn = e.SOC_SEC_NO INNER JOIN HRIS_Prod.dbo.APPOINTMENTS AS a ON e.INTERNAL_ID = a.INTERNAL_ID INNER JOIN HRIS_Prod.dbo.DIVISIONS AS d ON a.APPOINTMENT_DIVISION_ID = d.DIVISION_ID INNER JOIN HRIS_Prod.dbo.BUREAUS AS b ON a.APPOINTMENT_BUREAU_ID = b.BUREAU_ID INNER JOIN HRIS_Prod.dbo.SECTIONS AS s ON a.APPOINTMENT_SECTION_ID = s.SECTION_ID WHERE (eh.Active = 'A') AND (eh.LoginRecID NOT IN (SELECT RecID FROM ATC.dbo.Login WHERE (SecurityLevel LIKE '%LCK%'))) ), EmpOldHeader As ( SELECT eh.emp_LastName , eh.emp_FirstName , eh.emp_MI , isNull((select Division_ID from HRIS_Prod.dbo.Divisions d where eh.Division = d.DIVISION_CODE), 0) As DivisionID , isNull((select Bureau_ID from HRIS_Prod.dbo.Bureaus b where eh.Bureau = b.Bureau_CODE), 0) as BureauID , Division , Bureau , eh.emp_userid FROM ATC.dbo.Employee_Header_TC AS eh WHERE (eh.Active = 'A') AND (eh.LoginRecID NOT IN (SELECT RecID FROM Atc.dbo.Login WHERE (SecurityLevel LIKE '%LCK%'))) AND eh.TCdate in (select Max(tcdate) from ATC.dbo.employee_header_tc where emp_userID = eh.emp_userID) ), EmpMovedEmployees As ( select ec.emp_FirstName, ec.emp_LastName, ec.emp_MI, ec.emp_userID from EmpCurHeader ec inner join empOldHeader eo on ec.emp_userID = eo.emp_userID where (ec.DivisionID <> eo.DivisionID and ec.BureauID <> eo.BureauID) ) Insert @MovedUsers ( RowNumber , TumsUserID , UserID , employeeName ) select ROW_NUMBER() OVER (ORDER BY u.UserID ASC) , u.UserID , u.UserName , e.emp_FirstName + ' ' + e.emp_LastName + ' (' + e.emp_userID + ')' from TumsExecute.Users u inner join ATC.dbo.Employee_header e on u.UserName = e.emp_userID where u.UserName in (select emp_userID from EmpMovedEmployees ) And u.UserID in (select UserID from TumsExecute.UserToApplication where ApplicationID = (select ApplicationID from TumsExecute.Application where AppCode = 'EOD2375')) DECLARE @names VARCHAR(2500) Declare @Subject varchar(500) DECLARE @body varchar(4000) Declare @addMsg varchar(1000) Declare @employeeName as varchar(100) set @names = '' set @subject = 'User May No Longer Have EOD2375 E-Form Approval Responsibility ' set @body = 'The division or bureau has changed for the following user(s) that are able to approve EOD2375 e forms:<br><br>' set @AddMsg = 'A change in the userâ€™s home division or bureau may indicate that they no longer may have responsibility for approving EOD2375 e forms. EOD needs to investigate if the roles for the listed user(s) should be removed from the Recommended Appointment System. Failure to do so may result in an EOD2375 e-form approval request being e-mailed to a user who no longer has responsibility to approve it.' DECLARE @Cntr INT SELECT @Cntr = 1 WHILE EXISTS (SELECT 1 FROM @MovedUsers WHERE [@MovedUsers].RowNumber = @Cntr) BEGIN SELECT @EmployeeName = Upper(employeeName) FROM @MovedUsers WHERE RowNumber = @Cntr set @names = @names + @EmployeeName + '<br>' SELECT @Cntr = @Cntr + 1 END if @names <> '' begin set @body = @body + @names + '<br>' + @AddMsg execute msdb.dbo.sp_send_dbMail @recipients = 'xxx@lll' ,@subject = @subject ,@body = @body ,@body_format = 'HTML' ,@importance = 'HIGH'; end END / comments

Here you go. The only thing I changed was the email address in the code.USE [TUMS] GO /****** Object: StoredProcedure [dbo].[uspx_EOD2375SendUserDivBurChangeNotification] ...

0 votes

You say at this time? Are there plans to add this to the GUI? Clearly this is something that many of us want, myself included. / comments

You say at this time? Are there plans to add this to the GUI? Clearly this is something that many of us want, myself included.

0 votes

When I run the query I get my default path, but when I try to do a restore from a file I get a different path. I have screenshots if it helps. [/img] / comments

When I run the query I get my default path, but when I try to do a restore from a file I get a different path. I have screenshots if it helps. [/img]

0 votes

Thanks, we use the GUI for everything so I'll have to wait for it to make it to the GUI / comments

Thanks, we use the GUI for everything so I'll have to wait for it to make it to the GUI

0 votes

I created the job and I am the one trying to edit the job so I should have permissions. I didn't add any steps but I think maybe the schedule was changed in SSMS. Would this cause the problem? / comments

I created the job and I am the one trying to edit the job so I should have permissions. I didn't add any steps but I think maybe the schedule was changed in SSMS. Would this cause the problem?

0 votes

One column would be better than no sorting. If I could sort by server as well then that could be useful. In my case most of the time I'll be looking at one server and just want to know all of the items with one type. / comments

One column would be better than no sorting. If I could sort by server as well then that could be useful. In my case most of the time I'll be looking at one server and just want to know all of the i...

0 votes

minimally I'd like to see timestamps of when something is closed or reopened and a trail of when it is reopened/closed, as well as a comments area. I can include in the comments area. I can put the person who fixed it in the comments area if need be. while assigning and things found in a bug tracking system may be nice maybe a way to email the contents of incidents to people. So that this way I can mail the contents easily to my network guy who takes care of disk space if he needs to take care of it. / comments

minimally I'd like to see timestamps of when something is closed or reopened and a trail of when it is reopened/closed, as well as a comments area. I can include in the comments area. I can put the...

0 votes

In every case so far that pane is empty. / comments

In every case so far that pane is empty.

0 votes

I agree with John. I have different requirements on different boxes, I could very easily categorize my boxes in the 4 levels you have and set a different theshold for those categories though. Unless I missed it I don't see anyway to even set the threshold globally for all servers. / comments

I agree with John. I have different requirements on different boxes, I could very easily categorize my boxes in the 4 levels you have and set a different theshold for those categories though. Unles...

0 votes

This would be extemely useful for us as well. Currently I wrote a script to do the second delete for us. / comments

This would be extemely useful for us as well. Currently I wrote a script to do the second delete for us.

0 votes

How can we help you today?

Giggles220

Comments