Issues with CR 2016 report parameter that executes a stored procedure in SQL Server 2008 R2

Question

Quick Summary: Crystal Reports gives me error "ADO Error Code: 0x80040e14...Incorrect syntax near the keyword 'ALL'." when I execute my SQL SP with 'ALL' special keyword for employee number.

The Long Story: I have a report written in Crystal Reports 2016 that collects 3 parameters when it executes:

From Date (@fDate - SQL Stored Procedure / {?FromDate} - CR Command)
To Date (@tDate - SQL Stored Procedure / {?ToDate} - CR Command)
Employee Number (@eCode - SQL Stored Procedure / {?EmplNo} - CR Command)

One feature we want in the report is to be able to either run it for one employee or all employees. However, since these parameters are in the Crystal Reports command, I cannot make the employee number parameter optional. From some other forums I looked at the top suggestion was to make a CASE...WHEN evaluation of the parameter on the stored procedure side to determine if I'm actually passing an employee number or if I want all records. I used the keyword "ALL" to signify that I want to return records for all employees. My stored procedure works when I explicitly declare and set the parameters in MS SSMS. However, CR errors out when I try to execute the exact same stored procedure. What is causing this error? How can I get CR to report the information that I need?

SAP Crystal Reports Designer 2016 - Failed to retrieve data from the database. Details: ADO Error Code: 0x80040e14

SAP Crystal Reports SQL Command:

EXEC AIM.dbo.GetLaborUtil {?FromDate}, {?ToDate}, {?EmplNo}

MS SQL Server 2008 R2 (SP2) Express - Stored Procedure Code:

USE [AIM]
GO
/****** Object:  StoredProcedure [dbo].[GetLaborUtil]    Script Date: 12/12/2017 1:59:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetLaborUtil] @fDate date, @tDate date, @eCode varchar(4)
AS
    SELECT
        *
    FROM
        AIM.dbo.LaborUtil
    WHERE
        LaborUtil.AttendDate BETWEEN @fDate AND @tDate
        AND
        LaborUtil.EmplCode =    CASE    WHEN @eCode = '' THEN LaborUtil.EmplCode
                                        WHEN @eCode LIKE '%ALL%' THEN LaborUtil.EmplCode
                                        WHEN @eCode IS NULL THEN LaborUtil.EmplCode
                                        ELSE CAST(@eCode AS smallint)
                                END
    ORDER BY
        LaborUtil.EmplName,
        LaborUtil.AttendDate

Manual query to execute the stored procedure that works:

DECLARE @fDate date
DECLARE @tDate date
DECLARE @eCode varchar(4)

SET @fDate = '1900-01-01'
SET @tDate = '2100-12-31'
SET @eCode = 'ALL'

EXEC AIM.dbo.GetLaborUtil @fDate, @tDate, @eCode

sepupic sepupic · Accepted Answer · 2017-12-14T11:14:49

I think your problem is in HOW your parameters are sent to SQL Server.

Your procedure is correct so it cannot produce the error, but the CALL of your sp can.

Here is an example:

create proc dbo.sp_test_all
@p1 varchar(100), @p2 varchar(100)
as
   select @p1 as p1, @p2 as p2;
go


exec dbo.sp_test_all '10', all;

This exec dbo.sp_test_all '10', all; is producing

Incorrect syntax near the keyword 'all'.

You can see it using SQL Server Profiler. It will show you WHAT is sent to server.

I think your crystal report can interpret ALL as smth special within crystal report itself, so it's not sent as a string.

You can change your sp with the word 'everything' and try it instead of ALL.

Issues with CR 2016 report parameter that executes a stored procedure in SQL Server 2008 R2

1 Answers