Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2012 Report Issue On Server


SSRS 2012 Report Issue On Server

Author
Message
sql Sarah
sql Sarah
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 589
robert.gerald.taylor (9/6/2013)
nick947 (8/21/2013)

Do you know any other tricks to get past parameter sniffing?

Nick, here's a link explaining parameter sniffing and how to work around it:
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm

It is probably worth trying to change your query so that you assign the parameters to internally defined variables and then use those internal variables in the rest of your query (as opposed to using the parameters directly).

HTH,
Rob


Hi Nick,

I have had to do what Rob suggested with the internal parameters. That worked for us.

So you have parameters passed to the stored procedure, then you declare variables at the top of the stored procedure and assign the parameter values to them, and use the local variable in the code...

example:

Alter Procedure dbo.My_Stored_Proc (@Var1 int, @Var2 varchar(50))

AS

Declare @Local_Var1 INT, @Local_Var2 varchar(50)

Set @Local_Var1 = @Var1
Set @Local_Var2 = @Var2

/****
code block
*****/
WHERE database_field1 = @Local_Var1
and database_field2 = @Local_Var2

Hope that helps,
Sarah
nick947
nick947
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 491
mysorian (9/5/2013)
This is just an experiment I want you to try.

Save your report to the file system. Pick up the report in Report Builder. Now deploy it to the server. Do you see the same thing? I am not suggesting this will solve your problem, but just take a look.

I have run into similar problem recently. I am suspecting the database drivers. Have a look here,

http://jayaramkrishnaswamy.sys-con.com/node/2777798

Make sure you look up the last link on the above article.


I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.

Nick
sql Sarah
sql Sarah
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 589
What does it mean to "pick up the report in Report Builder"?

Does that mean open the .rdl file in Report Builder?

Thanks for the clarification,
Sarah
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 1399
nick947 (9/6/2013)

I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.

I'm glad that did the trick for you; I've had a similar experience recently.

Rob
nick947
nick947
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 491
sarah.sickinger (9/6/2013)
What does it mean to "pick up the report in Report Builder"?

Does that mean open the .rdl file in Report Builder?

Thanks for the clarification,
Sarah


I pulled the .rdl out of my solution and save it to my desktop. Then I opened it in RB 3.0 (which was a very slow experience). I tried to publish through RB but that was not what I wanted, so I made sure I could run it in RB (I had to change the shared dataset). Finally, I used the upload feature in the folder I wanted and ran the report.


Nick
mysorian
mysorian
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 101
After running in RB 3.0 did you save it to the Report Server and then run the report on the Report Server or Report Manager?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search