error when layout code

• 
  Hi,
  
  Does it happen consistently for a particular script?
  If so, would it be possible for you to give us the script that is causing the exception as it would help us fix the issue if we can reproduce it.
  
  Thanks,
  Tanya

  tanya May 19, 2008 12:38

  - 0 +
• 
  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
  

  Giggles220 May 19, 2008 12:42

  - 0 +

Add comment

Please sign in to leave a comment.