• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4683
  • Last Modified:

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]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

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.
0
Jim Horn
Asked:
Jim Horn
1 Solution
 
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.
0
 
VirastaRCommented:
Hi,

Look into your date format and adjust it.

DECLARE @dt DATETIME
 
SET @dt = ‘2011-06-01′

Courtesy - Cannot read the next data row for dataset Dataset1

Hope that helps :)
0
 
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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..
0
 
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.
0
 
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.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

as

/*
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)
VALUES (
	@report_name, @run_by, @run_dt, @run_length_time, @rows_returned,
	@parameters, @version_no)
	

Open in new window

0
 
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.

VV.
0
 
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
	TO PUBLIC;

GRANT INSERT ON OBJECT::Report_tracking.rpt_log
	TO PUBLIC;

Open in new window

0
 
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.

Thanks.
Jim
0
 
Russell FoxDatabase DeveloperCommented:
To be fair, we DBAs are just...the worst. Good luck!
0
 
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...
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now