I have a 2008 report that calls a PROC. When I go to verify the database, I get my date prompt, click okay and get the above error. I have tried using different data source, but still get this error. Can you please take a look at my code and see what may be causing this?

CREATE PROCEDURE [dbo].[spLwRptCheckDirectDepositRegisterHeader](
     @iCompany nvarchar(3)
     ,@iStartDate Date
     ,@iEndDate Date
     , @iDepartmentStart nvarchar(12)
     , @iDepartmentEnd nvarchar(12)
     , @iELINStart nvarchar(12)
     , @iELINEnd nvarchar(12)
)
AS
BEGIN
     SET NOCOUNT ON;
     --Have to use dynamic sql since we have to touch different payroll side databases here...
     DECLARE @lSQL nvarchar(MAX)
               ,@lParams nvarchar(MAX)
               ,@lPRDbName nvarchar(100)
               ,@lDStart decimal(9,0)
               ,@lDEnd decimal(9,0)
               ,@lELINStart nvarchar(12)
               ,@lDepartmentStart nvarchar(12)
               ,@lELINEnd nvarchar(12)
               ,@lDepartmentEnd nvarchar(12)               
     
     SELECT @lDStart = CONVERT(nvarchar(4),DATEPART(YEAR,@iStartDate)) +
                              RIGHT('00' + CONVERT(nvarchar(2),DATEPART(MONTH,@iStartDate)),2) +
                              RIGHT('00' + CONVERT(nvarchar(2),DATEPART(DAY,@iStartDate)),2)
               ,@lDEnd     = CONVERT(nvarchar(4),DATEPART(YEAR,@iEndDate)) +
                              RIGHT('00' + CONVERT(nvarchar(2),DATEPART(MONTH,@iEndDate)),2) +
                              RIGHT('00' + CONVERT(nvarchar(2),DATEPART(DAY,@iEndDate)),2)
     
     SELECT @lPRDbName = '[SageHRMS_' + LTRIM(RTRIM(@iCompany)) + ']'               
     SELECT @lParams = N'@lForCompany nvarchar(3),@lStartRange decimal(9,0), @lEndRange decimal(9,0), @lDepartmentStart nvarchar(12), @lELINStart nvarchar(12), @lDepartmentEnd nvarchar(12), @lELINEnd nvarchar(12)'

     SELECT @lSQL = 'SELECT chkH.*' + CHAR(13)+CHAR(10) +
                         ',hr.*' + CHAR(13)+CHAR(10) +
                         ',empl.*' + CHAR(13)+CHAR(10) +               
                         'FROM ' + @lPRDbName + '.[dbo].[UPCHKH] chkH' + CHAR(13)+CHAR(10) +
                         '     INNER JOIN [SageHRMS_LIVE].[dbo].[hrpersnl] hr ON LTRIM(RTRIM(chkH.Employee)) = LTRIM(RTRIM(hr.p_empno)) COLLATE SQL_Latin1_General_CP1_CI_AS' + CHAR(13)+CHAR(10) +
                         '                                             AND LTRIM(RTRIM(hr.p_company)) = LTRIM(RTRIM(@lForCompany))' + CHAR(13)+CHAR(10) +
                         '     INNER JOIN [SageHRMS_LIVE].[dbo].[syemploy] empl ON LTRIM(RTRIM(empl.e_company)) = LTRIM(RTRIM(@lForCompany))' + CHAR(13)+CHAR(10) +
                         'WHERE chkH.TRANSDATE >= @lStartRange' + CHAR(13) + CHAR(10) +
                         ' AND chkH.TRANSDATE <= @lEndRange' + CHAR(13) + CHAR(10) +
                         ' AND ((@lDepartmentStart = '''' AND @lDepartmentEnd = '''') OR hr.p_level1 BETWEEN @lDepartmentStart AND @lDepartmentEnd)' + CHAR(13) + CHAR(10) +
                         ' AND ((@lELINStart = '''' AND @lELINEnd = '''') or hr.p_level2 BETWEEN @lELINStart AND @lELINEnd)' + CHAR(13) + CHAR(10) +
                         --FOR DEBUG, JUST ONE EMP'
                         --' AND LTRIM(RTRIM(chkH.Employee)) = ''10''' + CHAR(13) + CHAR(10) +
                         'ORDER BY chkH.Employee,ChkH.TransDate'
               
     EXEC sp_executesql @lSQL, @lParams, @lForCompany = @iCompany,@lStartRange = @lDStart,@lEndRange = @lDEnd,@lDepartmentStart = @iDepartmentStart,@lELINStart=@iELINStart,@lDepartmentEnd = @iDepartmentEnd,@lELINEnd=@iELINEnd
END


Edited by MikeColeman407 - 05 May 2015 at 10:24am