SSRS 2012: Errmsg 'Cannot read the next data row for the dataset 'ds_rpt_daily_load_factor'

Hi All

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].DesignerThe 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.
USE [the_database]


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 @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

Open in new window

LogBut when I deploy it to the SSRS server and execute, I get the below error message:ssrsweb01-server-errmsg.jpgWhen I go back into SP rpt_daily_load_factor and remove the ‘exec rpt_log_insert’ statement, the report works correctly.
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
You probably have a permissions issue. The account used to run the report (check the Data Source in SSRS) will need Modify access on the log table.
VirastaRUC Tech Consultant Commented:

Look into your date format and adjust it.

SET @dt = ‘2011-06-01′

Courtesy - Cannot read the next data row for dataset Dataset1

Hope that helps :)
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
VirastaR - Please tell me if you read the message I sent, and if you have a response.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Russell - You're probably correct.  There's a DBA here that insisted on a separate SP (rpt_log_insert) instead of a straight INSERT INTO which was how I originally had it coded.  

It's working now, likely because of something the DBA did, and I'm attempting to extract what that was out of him.

Stay tuned..
Russell FoxDatabase DeveloperCommented:
He's absolutely right to do it that way. It looks like a logging function that you can now use in every report, and it's much handier to have it coded in one place so changes don't need to be replicated. Though he would need to set the right permissions for the function.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
@Russell - Right now I'm writting SSRS reports that hit multiple databases on the same server.  Each database has a SP rpt_log_insert, which inserts into a report_tracking..rpt_log table. I need to understand from him how he's securing the SP.   Like many IT people, this guy is severely English-challenged which makes this difficult.

ALTER procedure [dbo].[rpt_log_Insert] (
	@report_name varchar(100),
	@run_by  varchar(25),
	@run_dt  datetime,
	@run_length_time time(7),
	@rows_returned  int,
	@parameters varchar(1000),
	@version_no numeric(3,1)


Insert a row for every report run into report_tracking..rpt_log.  Used to monitor report activity. 

08-22-14  jim.horn  Original

INSERT INTO Report_tracking.dbo.rpt_log (
	report_name, run_by, run_dt, run_length_time, rows_returned,
	parameters, version_no)
	@report_name, @run_by, @run_dt, @run_length_time, @rows_returned,
	@parameters, @version_no)

Open in new window

ValentinoVBI ConsultantCommented:
Hey Jim,

How is the data source for your dataset set up?  Are you using a particular user to access the DB or Windows Integrated Security?  Once you've figured that out you know which user needs to be given read/write access to the report_tracking DB.

Russell FoxDatabase DeveloperCommented:
If your PUBLIC server role is active, the below will work, otherwise you'll need to replace PUBLIC with the report server execution account. The hard part might be getting him to run the code...
GRANT EXECUTE ON OBJECT::Report_tracking.rpt_log_Insert

GRANT INSERT ON OBJECT::Report_tracking.rpt_log

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Guys - I have some larger issues with this DBA, so it's not likely I'll get a definitive answer anytime soon.  I'm sure Russell's comment nails it though, minus maybe any ActiveDirectory things I don't have insight on yet, so I'll accept that answer.

Russell FoxDatabase DeveloperCommented:
To be fair, we DBAs are just...the worst. Good luck!
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Like any job there's good ones and bad ones, but there's a higher impact on the bad ones effing up your world...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.