What are the challenges you face when working across database platforms? Take the survey
Options

error when layout code

Giggles220Giggles220 Posts: 154
edited May 19, 2008 12:42PM in SQL Prompt Previous Versions
when trying to layout one of my stored procedures I get the following error. It doesn't happen for all code I try and layout. In this particular case I did not get an error when requesting that refactor layout the code.
System.ApplicationException: Exception within progress task ---> System.ArgumentException: Value does not fall within the expected range.
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode)
   at Microsoft.VisualStudio.ErrorHandler.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure)
   at Microsoft.VisualStudio.ErrorHandler.ThrowOnFailure(Int32 hr)
   at RedGate.SQLPrompt.CommonVS.Editor.VSScriptProvider.IndexFromPosition(CaretPosition position)
   at RedGate.SQLPrompt.CommonUI.Refactor.ScriptHelper.GetPositionOfLineIndex(IScriptProvider script, CaretPosition caret)
   at ae.c(CaretPosition )
   at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , b , c& )
   at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a(EditorWindowBase , c& , b[] )
   at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.a.Run()
   at RedGate.SQLPrompt.CommonUI.Progress.TaskAggregator.Run()
   at RedGate.SQLPrompt.CommonUI.Forms.ProgressDialogue.a()
   --- End of inner exception stack trace ---
   at RedGate.SQLPrompt.CommonUI.Forms.ProgressDialogue.ShowProgress(ITaskRunner task)
   at RedGate.SQLPrompt.CommonUI.Refactor.Refactor.LayOutSql(EditorWindowBase editor, SqlPromptOptions options)
   at b.Execute()
   at RedGate.SQLPrompt.CommonVS.Commands.VSCommandControler.Exec(String CmdName, vsCommandExecOption ExecuteOption, Object& VariantIn, Object& VariantOut, Boolean& Handled)

Comments

  • Options
    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
  • Options
    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
    
Sign In or Register to comment.