SSRS, error message of query execution failed

Hi Experts,

The report runs well in Visual Studio. After I deployed it to the reporting manager, the report cannot run with an error message of

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'GGMExport'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


How do I fix it?

Many thanks,
tanj1035Asked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Double-click on the data set named GGMExport, and in the Properties dialog that pops up, query tab and if the Query type says...
Text then copy-paste the Query into this question.
Stored Procedure then paste the name here, then go into SSMS and find that stored procedure, copy the entire T-SQL and paste it into this question.

Also paste whatever parameters were passed if there were any.

Also whatever data source is behind used in the data set, test it to make sure it connects correctly.

Since the data set execution  failed, and there's not any code in this question, there's currently not enough information here to provide an answer.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also make sure that the user that ran the report also has privs to execute the dataset tables/view/stored procedure, although I believe that would be a different error message.
0

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
tanj1035Author Commented:
Thanks for your reply Jim. Please see the sp code below. I have added "grant execute on sp_rept_TitleLoan_VP_GrossProfit to public
go" in the SP which I think it will give privs to public. If I am wrong, where can I set up privs to public?
Thanks.

USE [SMRLoanAppLive]
GO

/****** Object:  StoredProcedure [dbo].[sp_rept_GGMExport]    Script Date: 11/04/2015 08:23:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

If exists (select 1 from sys.procedures where name ='sp_rept_GGMExport')
drop procedure dbo.sp_rept_GGMExport
go 
/******************************************************************
Created by:		Byron Chan
Created on:		8/24/2015
Description:	
Testing:		exec sp_rept_GGMExport 
	
	 
	if exists (select 1 from sys.objects where name = 'tblGGMExport' and type = 'U')
		drop table tblGGMExport
	else
		Create table tblGGMExport (LoanID int,ContractString varchar(20),LoanAmountFinanced money, LoanDate datetime, LoanStoreID int, LoanStoreState varchar(20)) 
	go
	insert into tblGGMExport(LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, LoanStoreState)
	select loanid, contractstring, LoanAmountFinanced, LoanDate, LoanStoreID, st.StoreState
	from tblloans ln join tblStores st on ln.LoanStoreID = st.StoreID
	where LoanStatusID not in (2,8)
	and exists (select * from tblStores_tblStoreTypes sst where st.StoreID = sst.StoreID and sst.StoreTypeID = 1)
--*******************************************************************/


create procedure [dbo].[sp_rept_GGMExport]
	
as
Begin


/*
	--delete yesterday's data for testing purpose.

	Declare @BeginDate datetime, @EndDate datetime
	Set @BeginDate=dbo.date(dateadd(d,-1,getdate()))
	Set @EndDate=DateAdd(ss,-1,DateAdd(d,1,@BeginDate))	
	delete tblGGMExport where LoanDate >= @BeginDate
*/
	set FMTONLY off

	declare @EndDate datetime = cast(convert(varchar(10),GETDATE(),121) as date)

	if object_ID('tempdb..#temp') > 0
		drop table #temp
	
	create table #temp(loanid int, OldLoanNumber int, contractstring varchar(100), 
						LoanAmountFinanced money , LoanDate datetime, customerid int, assetid int, 
						LoanStoreID int, StoreState varchar(20), EntryDate datetime)

	insert into #temp
	select loanid, OldLoanNumber, contractstring, LoanAmountFinanced, LoanDate, customerid, assetid, LoanStoreID, st.StoreState,
		cast(convert(varchar(10),GETDATE(),121) as date)
	from tblloans ln join tblStores st on ln.LoanStoreID = st.StoreID
	where LoanStatusID not in (2,8)
		and not exists (select * from tblGGMExport g where g.LoanID = ln.LoanID)
		and exists (select * from tblStores_tblStoreTypes sst where st.StoreID = sst.StoreID and sst.StoreTypeID = 1)
		and LoanDate < @EndDate

	Select StoreState,
		LoanID,
		ContractString,
		CustomerFirstName,
		CustomerLastName,
		LoanAmountFinanced,
		cast((LoanAmountFinanced/Case When BBValue=0 THEN 1 ELSE BBValue END) as decimal(10,4)) as LoanToValue,
		cast((LoanAmountFinanced/Case When Income=0 Then 1 Else Income END) as decimal(10,4)) as IncomeToLoan,
		NumberReferences,
		lengthOfResidency,
		lengthOfemployment,
		LoanRefinanceID,
		VIN,
		(
			Select COUNT(*) 
			from tblLoans
				inner Join tblAutomobiles on tblAutomobiles.AssetID=tblLoans.AssetID
			Where LoanID<>a.LoanID 
				and AutomobileVIN=VIN 
				and LoanStatusID not in (2,8)
		) as NumDuplicateVin,
		convert(varchar(10),LoanDate,101) as LoanDate
	from
	(
		Select 
			LoanID,
			LoanDate,
			ln.CustomerID,
			ContractString,
			LoanAmountFinanced,
			cust.CustomerFirstName,
			cust.CustomerLastName,
			ISNULL(ISNULL(AutomobileBlackBookRetailClean,AutomobileBlackBookLoanValue),AutomobileBlackBookValue) as BBValue,
			ISNull(CustomerMonthlyIncome,0) as Income,
			Case When CustomerReferenceName1 is not null then 1 Else 0 END + 
			Case When CustomerReferenceName2 is not null then 1 Else 0 END +
			Case When CustomerReferenceName3 is not null then 1 Else 0 END +
			Case When CustomerReferenceName4 is not null then 1 Else 0 END as NumberReferences,
			DateDiff(m,IsNull(CustomerEmploymentStartDate,LoanDate),GETDATE()) as LengthOfEmployment,
			DateDiff(m,IsNull(CustomerAddressFromDate,LoanDate),GETDATE())as LengthOfResidency,
			isNull(OldLoanNumber,0) as LoanRefinanceID,
			AutomobileVIN as VIN,
			StoreState
		from #temp ln
			inner Join tblCustomers	cust on cust.CustomerID=ln.CustomerID
			inner Join tblAutomobiles auto on auto.AssetID=ln.AssetID
		where ln.StoreState not in ('TX','OH')

		union
			
		Select 
			LoanID,
			LoanDate,
			ln.CustomerID,
			ContractString,
			LoanAmountFinanced,
			cust.applicantFirstName as CustomerFirstName,
			cust.applicantLastName as CustomerLastName,
			coalesce(AutomobileBlackBookRetailClean,AutomobileBlackBookLoanValue,AutomobileBlackBookValue) as BBValue,
			(ISNull(cust.applicantEmployerSalary,0) * PayfrequencyMultiplier) as Income,
			Case When cust.applicantReferenceName1 is not null then 1 Else 0 END + 
			Case When cust.applicantReferenceName2 is not null then 1 Else 0 END +
			Case When cust.applicantReferenceName3 is not null then 1 Else 0 END +
			Case When cust.applicantReferenceName4 is not null then 1 Else 0 END as NumberReferences,
			(IsNull(cust.applicantEmployerLengthYears,0) * 12) + (IsNull(cust.applicantEmployerLengthMonths,0)) as LengthOfEmployment,
			IsNull(cust.applicantMonthsAtResidence,0) as LengthOfResidency,
			isNull(OldLoanNumber,0) as LoanRefinanceID,
			AutomobileVIN as VIN,
			StoreState
		from #temp ln
			inner Join tblApplicants cust on cust.applicantLoanID=ln.LoanID
			inner Join tblAutomobiles on tblAutomobiles.AssetID=ln.AssetID
			left join tblPayfrequency pf on cust.applicantEmployerPayFrequency = pf.PayfrequencyType
		where ln.StoreState in ('TX','OH') and cust.applicantIsCoApplicant = 0
	) as a

	insert into tblGGMExport(LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, LoanStoreState, EntryDate)
	select LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, StoreState, EntryDate
	from #temp

	if object_ID('tempdb..#temp') > 0
		drop table #temp

end

GO

grant execute on sp_rept_TitleLoan_VP_GrossProfit to public
go

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What happens when you execute the code without it being in a Stored Procedure, i.e. delete lines 1-37 and 151-156?
0
tanj1035Author Commented:
Thx, I tried it. The error msg showed "cannot read the next data row for the dataset dataset 1 (rserrorreadingnextdatarow).
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
So if the SP executes with an error message, then the SSRS can't use it, so you'll need to fix the SP first.

Double-click on the error message, watch the cursor jump to the offending line, and tell us which line it is.
0
tanj1035Author Commented:
Actually, it is a prvis issue. I fixed it. Thanks, Jim.
The report can run in the reporting manager. But, the data did not show. My manager showed me something like it below, to get the data yesterday. But I do not remember it exactly. I tried the code below, and it did not show the data. Is the code correct?

If exists (select 1 from sys.procedures where name ='sp_rept_GGMExport')
drop procedure dbo.sp_rept_GGMExport

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes, as long as the default schema is dbo as it's not referenced in line 1.
Also, you'll want to watch that DROPping the proc doesn't also drop the security assignment, so your script should be drop - create - assign privs OR alter - assign privs.
0
tanj1035Author Commented:
Thank you, Jim
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade, good luck with your report.  -Jim

btw for SSRS questions you'll want to also add the MS SQL Server zone, as more experts monitor that one where some of them might not also be monitoring the SSRS zone.
0
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
SSRS

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.