In SSRS 2012 I have a report that returns the error message in the question title when ran on our SSRS server, but not when ran in the designer (both by me), and I could use some troubleshooting help.
There are two parameters for start date and end date, and a hidden parameter UserID with a default value of [&UserID].
The SP that serves as the dataset has at the bottom of it an exec rsp_log_insert, which inserts one row into a different db's table, intended to log the report run.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROC [dbo].[sp_name] (@start_dt date = NULL, @end_dt date = NULL, @run_by varchar(25)) AS
-- Logging variables
Declare @dtStartSP datetime = GETDATE(), @row_count as int, @version_no numeric(3,1) = 1
-- A big honkin' pile of T-SQL that returns the report's data set is here.
SET @row_count = @@ROWCOUNT
-- Write a single row to the logging table
Declare @dtEndSP as datetime, @diff time, @parameters varchar(1000)
SET @dtEndSP = GETDATE()
SET @diff = CAST(@dtEndSP - @dtStartSP as time)
SET @parameters ='@start_dt=' + CAST(@start_dt as varchar(10)) + ', @end_dt=' + CAST(@end_dt as varchar(10))
exec rpt_log_insert 'sp_name', @run_by, @dtStartSP, @diff, @row_count, @parameters, @version_no
But when I deploy it to the SSRS server and execute, I get the below error message:
When I go back into SP rpt_daily_load_factor and remove the ‘exec rpt_log_insert’ statement
, the report works correctly.