exec sp_executesql error 'Incorrect syntax near 1' when using datetime parameter
- by anne78
I have a Ssrs report which sends the following text to the database :
EXEC ( 'DECLARE @TeamIds as TeamIdTableType ' + @Teams +
' EXEC rpt.DWTypeOfSicknessByCategoryReport @TeamIds , ' +
@DateFrom + ', ' + @DateTo + ', ' + @InputRankGroups + ', ' + @SubCategories )
When I view this in profiler it interprets this as :
exec sp_executesql N'EXEC ( ''DECLARE @TeamIds as TeamIdTableType '' + @Teams +
'' EXEC rpt.DWTypeOfSicknessByCategoryAndEmployeeDetailsReport @TeamIds, '' +
@DateFrom + '', '' + @DateTo + '', '' + @InputRankGroups + '', '' + @SubCategories )',N'@Teams nvarchar(34),@DateFrom datetime,@DateTo datetime,@InputRankGroups varchar(1),@SubCategories bit',@Teams=N'INSERT INTO @TeamIds VALUES (5);
',@DateFrom='2010-02-01 00:00:00',@DateTo='2010-04-30 00:00:00',@InputRankGroups=N'1',@SubCategories=1
When this sql runs it errors, on the dates.
I have tried changing the format of the date but it does not help. If I remove the dates it works fine.
Any help would be appreciated.
