error when layout code

Giggles220

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)

tanya

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

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