Report Wizard

I am trying to use a stored proc in the report wizard with parameters
Exec usp_AgentSessionSummary @.StartDate, @.EndDate, @.ProjectID, @.AgentID
I get the error below. I want to use the wizard for ease, how do I get
around the error?
There is an error in the query. Implicit conversion from data type
sql_variant to datetime is not allowed. Use the CONVERT function to run this
query.You could try first hard coding the parameters, use the wizard and then
switch it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JW" <JW@.discussions.microsoft.com> wrote in message
news:E1ADCFEF-E23F-41F7-8327-BB5B10C11008@.microsoft.com...
>I am trying to use a stored proc in the report wizard with parameters
> Exec usp_AgentSessionSummary @.StartDate, @.EndDate, @.ProjectID, @.AgentID
> I get the error below. I want to use the wizard for ease, how do I get
> around the error?
> There is an error in the query. Implicit conversion from data type
> sql_variant to datetime is not allowed. Use the CONVERT function to run
> this
> query.

Report Running Slowly

I have a report in SQL Reporting Services 2005 which calls a stored
proc and the report takes a very long time to run and sometimes
returns zero records. But when i run the stored proc in query analyzer
it takes about 4 seconds.
I have checked the execution log on the RS using the below sql:
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval
(601309ms, about 10mins) and does not return any records.
TimeDataRetrieval TimeProcessing TimeRendering
Source Status ByteCount RowCount
601309 2227
3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about
400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this
to retrieve data.
The report is very simple - it basically returns the records straight
out into a table.
Can anyone suggest where to look or how to troubleshoot this problem?On Jun 27, 9:19 pm, Mark.J.Br...@.gmail.com wrote:
> I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
Of course, SSRS has some overhead in being a service as opposed to
directly being a part of the database (as w/a query). Also, it
accesses IIS, etc (which adds overhead). The first thing that I can
suggest doing is to run the Database Engine Tuning Advisor against the
query used in the report and implement any indexes that it suggests.
Also if you are using report parameters, you should verify that they
are linked correctly in the datasets (via Data view -> Edit Dataset
[...] -> Parameters tab). Also, verify that there are not other
processes running on the machine that are consuming excessive
resources (though this could be a long shot). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||How long does it take from the development environment versus the server?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1182997161.429465.190340@.a26g2000pre.googlegroups.com...
>I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
>|||On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How long does it take from the development environment versus the server?
The report is scheduled to run out of hours, when no other tasks are
scheduled to run (in SQL or Reporting Services or Windows).
Most scheduled reports work correctly and contain data. Occassionally
this report will return 0 rows.
The only common denominator between the reports that return 0 rows is
they always exceed 600,000 in the TimeDataRetrieval column.|||Are you able to run it from the development environment as a test?
It could be that you are getting into some locking issues when this is
running.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183003249.202007.213580@.x35g2000prf.googlegroups.com...
> On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How long does it take from the development environment versus the server?
> The report is scheduled to run out of hours, when no other tasks are
> scheduled to run (in SQL or Reporting Services or Windows).
> Most scheduled reports work correctly and contain data. Occassionally
> this report will return 0 rows.
> The only common denominator between the reports that return 0 rows is
> they always exceed 600,000 in the TimeDataRetrieval column.
>|||On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Are you able to run it from the development environment as a test?
> It could be that you are getting into some locking issues when this is
> running.
Same problem in development environment.
As to the live data, there should be no other processes running at
that time, or users accessing the data.
Mark|||I have heard about but never seen an issue where SQL from RS ends up using a
different query plan.
Try one of the two methods in this link to force a recompile:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726af78f7c18.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183337771.100767.292930@.z28g2000prd.googlegroups.com...
> On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Are you able to run it from the development environment as a test?
>> It could be that you are getting into some locking issues when this is
>> running.
> Same problem in development environment.
> As to the live data, there should be no other processes running at
> that time, or users accessing the data.
> Mark
>|||On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up usin=g a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-7=26=ADaf78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?|||This isn't the MS link but it describes someone who had a performance
problem with the stored procedure. What you want to do is use the With
Recompile option with the stored procedure.
http://articles.techrepublic.com.com/5100-9592_11-5662581.html
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183506487.200772.282060@.j4g2000prf.googlegroups.com...
On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up using
> a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726­af78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?

Report Render in VS2005 slower than stored proc

I have searched many forums and found some cases of people also reporting slower rendering in RS than in Management Studio / Query Analyzer. However, none of the other solution suggestions seem to make a difference for me.

I'm a VS/VB developer and have got multiple reports built -- all using stored procedures on the backend -- that all take many times longer to run than if executed via Management Studio (SSMS). My simplest proc takes a couple of parameters (no defaults included) and does a simple select against one table with a few joins. Nothing complicated. It runs in 8 secs for 6867 rows via SSMS. Through RS (running locally through Visual Studio 2005 at this point) it takes around 25-28 secs. Yet, when I'm in the report on the DATA tab (not the PREVIEW tab) the run takes the expected 8 secs ?!?!

All reports are behaving this way.

I am not using cursors.

I have no default values on parameters.

I have added the "WITH RECOMPILE" to the proc statement.

I have "SET NOCOUNT ON" as the first line of the proc.

I hate to say this, but I even connected the proc to Crystal Reports to see how it behaved. It ran in the expected 8 secs.

I've seen some mention by someone that perhaps this is a known issue of RS that it reads the proc twice. Any truth to this?

Also a couple posts have traced and demonstrated that the report is generating significantly more data "reads" via RS than through SSMS.

We're a shop that is considering a switch from Crystal to RS, but we do everything through stored procedures. I need to clear up this issue before I can go forward recommending a switch. I'm including a copy of a typical proc below for review... What am I missing? What's the deal here with RS?

IF OBJECT_ID('dbo.rpt_InactiveAccounts') IS NOT NULL

DROP PROCEDURE dbo.rpt_InactiveAccounts

GO

CREATE PROCEDURE dbo.rpt_InactiveAccounts

(@.pRunDate datetime

,@.pSalesperson varchar(5000)

,@.pIncludeOpen char(1)

)

WITH RECOMPILE

AS

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- CREATE/SETUP TEMP TABLE FOR USE IN PARSING MULTI-VALUED STRING INPUTS

DECLARE @.NumberPivot TABLE (NumberID INT PRIMARY KEY)

DECLARE @.intLoopCounter INT

SELECT @.intLoopCounter =0

WHILE @.intLoopCounter <=4999 BEGIN

INSERT INTO @.NumberPivot

VALUES (@.intLoopCounter)

SELECT @.intLoopCounter = @.intLoopCounter +1

END

-- CREATE TEMP TABLES TO HOLD PARSED VALUES FROM MULTI-VALUE STRING INPUT PARAMETERS

DECLARE @.SalespersonTable TABLE

(tmpSalesperson varchar(30))

-- PARSE OUT @.pSALESPERSON PARAMETER AND STORE VALUES IN TEMP TABLE

INSERT INTO @.SalespersonTable

SELECT SUBSTRING(',' + @.pSalesperson + ',', NumberID + 1,

CHARINDEX(',', ',' + @.pSalesperson + ',', NumberID + 1) - NumberID -1)

FROM @.NumberPivot

WHERE NumberID <= LEN(',' + @.pSalesperson + ',') - 1

AND SUBSTRING(',' + @.pSalesperson + ',', NumberID, 1) = ','

SELECT DISTINCT

CASE

WHEN s.Name IS NULL THEN '<< OPEN >>'

ELSE s.Name

END As SalespersonName

,c.ClassId

,c.CustId

,c.Name

,c.Addr1

,c.Addr2

,c.Addr3

,c.City

,State

,CASE

WHEN Len(c.Zip) = 9 And CharIndex(' ', c.Zip, 0) = 0 THEN Left(c.Zip, 5) + '-' + Right(c.Zip, 4)

ELSE c.Zip

END As Zip

,ac1.descr As Terms

,ac2.descr As Status

FROM

ACTCustomer c (NOLOCK)

LEFT OUTER JOIN CustSales cs ON c.CustId = cs.CustId

LEFT OUTER JOIN ACTSalesperson s ON cs.SlsId = s.SalesId

INNER JOIN @.SalespersonTable st ON s.Name = st.tmpSalesperson OR (s.Name IS NULL AND @.pIncludeOpen = 'Y')

INNER JOIN ACTCode ac1 ON ac1.Code = c.Terms And ac1.FieldId = 'CustTerms'

INNER JOIN ACTCode ac2 ON ac2.Code = c.Status And ac2.FieldId = 'Status'

WHERE

c.LastInvcDate <= @.pRunDate

And c.ClassId <> 'TR'

ORDER BY

SalespersonName, Name, CustId

Hi Dave,

I don't think that this will solve you performance issue but I have a recommendation for you, since you are passing some comma separated list to your reports as parameter, maybe you should create a user-defined function that will convert you list into a table. This would allow you to reuse this same function in all of you reports.

Sample Here:

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

For the performance issue, did you try to profile and compare your SSMS execution and the Report execution?

HTH,