openquery passing date parameter conversion failed

Question

Created the following stored procedure to create a dataset within SSRS. But when I run the query in SSRS I get the conversion failed error when converting date from character string. I'm sure it's a syntax or variable issue. Any ideas?

   ALTER PROCEDURE [dbo].[usp_r_pcdh_counts]
     @START datetime,
     @END datetime
       AS
       BEGIN
       SET NOCOUNT ON;
   DECLARE @START1 datetime= @START 
   DECLARE @END1 datetime = @END
   DECLARE @sql nvarchar (4000)
   SET @sql= 'SELECT *
   FROM OPENQUERY(MR_UNI,''select 
                count(case when event_audit_type_key in ("1346","1038") then 1 else null end) as outsuccessful,
                count(case when event_audit_type_key = "1531" then 1 else null end) as outunsuccessful,
                count( case when event_audit_type_key in ("1040") then 1 else null end) as outdocsfound,
                count( case when event_audit_type_key in ("1532") then 1 else null end) as outdocsnotfound,
                count( case when event_audit_type_key in ("1042") then 1 else null end) as outdocsretrieved,
                count( case when event_audit_type_key in ("1580") then 1 else null end) as outdocsnotretrieved,
                count( case when event_audit_type_key in ("1048") then 1 else null end) as insuccess,
                count( case when event_audit_type_key in ("1048") and success_code = "12"  then 1 else null end) as infailure,
                count( case when event_audit_type_key in ("1050") then 1 else null end) as indocsretrieved
            from public.event_audit ea
            where ea.event_date >= convert(date,''''' + @START1+ ''''', 110)
                    and ea.event_date < convert(date, '''''+ @END1+ ''''', 110)
                    AND event_audit_key >= (SELECT MAX(event_audit_key) - 100 
                         FROM reports.event_audit_index 
                         WHERE event_date <= convert(date, '''''+ @START1+ ''''', 110))
                    AND event_audit_key <= (SELECT MIN(event_audit_key) + 100 
                         FROM reports.event_audit_index 
                          WHERE event_date >= convert(date, '''''+ @END1+''''', 110))'')'
    EXEC @sql
    END

John Eisbrener · Answer 1 · 2017-04-12T15:59:27.387

You're double-nesting Dynamic SQL (DSQL) which is just a pain in the butt. The trick is to pop a layer of Dynamic logic out with a simple print statement to check the syntax of that level, which going forward will help you better troubleshoot these sort of issues.

Regardless, this should give you are looking for:

ALTER PROCEDURE [dbo].[usp_r_pcdh_counts]
    @START datetime,
    @END datetime
    AS
    BEGIN
    SET NOCOUNT ON;
DECLARE @START1 datetime= @START 
DECLARE @END1 datetime = @END
DECLARE @sql nvarchar (4000)
SET @sql= 'SELECT *
FROM OPENQUERY(MR_UNI,''select 
            count(case when event_audit_type_key in (1346,1038) then 1 else null end) as outsuccessful,
            count(case when event_audit_type_key = 1531 then 1 else null end) as outunsuccessful,
            count( case when event_audit_type_key in (1040) then 1 else null end) as outdocsfound,
            count( case when event_audit_type_key in (1532) then 1 else null end) as outdocsnotfound,
            count( case when event_audit_type_key in (1042) then 1 else null end) as outdocsretrieved,
            count( case when event_audit_type_key in (1580) then 1 else null end) as outdocsnotretrieved,
            count( case when event_audit_type_key in (1048) then 1 else null end) as insuccess,
            count( case when event_audit_type_key in (1048) and success_code = 12  then 1 else null end) as infailure,
            count( case when event_audit_type_key in (1050) then 1 else null end) as indocsretrieved
        from public.event_audit ea
        where ea.event_date >= convert(date, ''''' + convert(varchar, @START1, 110)+ ''''', 110)
                and ea.event_date < convert(date, '''''+ convert(varchar, @END1, 110) + ''''', 110)
                AND event_audit_key >= (SELECT MAX(event_audit_key) - 100 
                        FROM reports.event_audit_index 
                        WHERE event_date <= convert(date, '''''+ convert(varchar, @START1, 110) + ''''', 110))
                AND event_audit_key <= (SELECT MIN(event_audit_key) + 100 
                        FROM reports.event_audit_index 
                        WHERE event_date >= convert(date, '''''+ convert(varchar, @END1, 110) +''''', 110))'')'
--PRINT @sql
EXEC @sql
END

Thanks, I added another quote to the outside of convert(varchar) to work but I am now getting an error "The name 'Select * from openquery....is not a valid identifier" and goes on to show the openquery select statement. I added EXEC (@sql) and that seemed to help but now I have "incorrect syntax near +" — Wendi, Apr 12 '17 at 15:52
@Wendi - I've made an edit and removed the double-quotes because I would hope those key fields are defined as INTS and not VARCHARs. That may be your problem. Also, I don't think you need to add any more quotes, as the nesting should be appropriate in my answer. — John Eisbrener, Apr 12 '17 at 16:00

openquery passing date parameter conversion failed

1 Answers1