Report is not visible

Question

Report is not visible

arup chakraborty

Hall of Fame

Points: 3640
More actions
January 22, 2009 at 3:42 am

#200322

Basically it is a very common interview question. Its like this:
My SSRS report works on some Sp's. Its working fine until last night. But today morning onwards, its not working, not showing any data. Previously it took 3 mins to execute, now it is taking hours and not showing any data.
The SP is also taking hours to execute and not showing any data.
What might happen and what is the work around?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

CrazyMan SSChampion Points: 12560 More actions · Answer 1

Check connections from studio and the reporting server, if there is a problem with SP, they try running on SQL and check the time, there must be any index missing, it must be a small thing to spot out 🙂

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi,

Thanks for your reply.

It is working yesterday night fine. Meanwhile there is not such bulk insertion etc. So index should not be a factor.

Also other reports are working fine. It happens with only this report. So conenction etc. is ok.

The problem is happening in the SP also. This SP was running good last night. Today its not able to execute, taking hours of time.

Any suggestion?

CrazyMan SSChampion Points: 12560 More actions · Answer 3

so this is a problem with SP which suddenly behaves badly:), can you post the SP, we can check on the code 🙂

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

Hi,

I have told in my first post, this is an ineterview question. I need to know what may happen and what may be the work around.

what I think, it may be realted to locking, but all of a sudden it happend.

This is the scenario.

thanks,

Arup

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

Run profiler and check for events like SP:starting ,SP:Recompile,SP:stmtcompleted and SQL:Batchstarting while running this report.

Not to mention the code for this SP can also be checked by viewing the execution plan for any missing statistics..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

Hi The _SQL_DBA,

its not a problem i have faced. I need 2 know what may happen for this problem and the possible work around 4 that. Is locking may cause this kind of problems?

Plz remember, the report/SP was working fine until last night. From today morning, this is happening.

rjgoggin SSC Rookie Points: 25 More actions · Answer 7

I would check the actual data added between the time the report/stored procedure was working and when it quit working. I have seen several cases where reports or stored procedures have failed because the new data was entered in a different format, or contained characters that the writer of the report or stored procedure did not expect.

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 8

are you outputing your data into table region in your report? well, it has a property 'no rows' and defaults to 'No Rows matching your criteria'. if your stored proc runs well directly from sql, but in report this message is printed, you may want to look at other things.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

Well, it being an interview question you have to answer this in a stipulated amount of time, the answer could be as:

1. If report is not working then run profiler and check the events I mentioned before

2. If the stored procedure is taking too long then check the events 'textdata' and 'blocked process'. Not to mention, take a look at the execution plan.

Ideally this looks like a severe blocking issue to me as nothing has changed from last night.

Look to see if either SP or the CULPRIT SPID have any NOLOCK hints or no...having said that this is just a few steps in the right direction to find out the solution.

We can do better than this in real scenario, for an interview this answer should be more than sufficient..;)

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 10

nitin.saha (1/23/2009)
I had a similar problem in SQL 2005. The issue turned up to be that my id lost execute authority on the SP. I had to re-grant the execute authority & it then worked fine.

If the ID lost execute permission then this is not at all an issue as you would see an error message at the report execution.

But then it would be a pain to find out how did the ID lost the execute permission all of a sudden..time to work harder on your security....:D

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 11

Hi All,

Really this is a great help for me. I have faced this question several times.

Thanks again.

esa.kauppinen Old Hand Points: 387 More actions · Answer 12

When you exec your SP use [WITH RECOMPILE] option - it forces a new plan to compiled. Old plans remains in cache and if some indexes etc changes or drops that old plan might turn really slow.