Link to home
Start Free TrialLog in
Avatar of jdallen75
jdallen75Flag for Canada

asked on

SSRS query execution failure that resolves by running the query in SSMS once

This problem used to happen once or twice a year, but it's happening with higher frequency now, and I'm at a loss how to troubleshoot it - because the normal troubleshooting method I'd use is the temporary and unintended "fix" itself.

I have an SSRS 2012 report deployed in SharePoint (2013) mode on a Windows Server 2012. The report is configured to use a dynamic data source, such that it can be run against 1 of 5 similar sites (databases). When the problem arises, whether I run the report from IE or within SSDT itself, I get this red-herring message:

User generated image
Here are some important outcomes from my troubleshooting so far:
  • My normal troubleshooting strategy is to gather the list of inputs the SP behind the report would use, and run the SP manually from SSMS. As soon as I run this query, the problem disappears
  • The date range on the report makes no difference. Even if the report ran last week on last week's data, setting the date range to last week when the problem arises this week still results in this message
  • The problem typically happens on 1 or 2 sites at any given time, but not all sites. The "fix" is to connect to each site's DB and run the query once in SSMS
  • I use the term "red-herring" because if it were a data issue, the problem wouldn't disappear simply by running the query in SSMS. One would expect to find a bad piece of data that would require correction
  • When I attempted to "fix" the problem last week by running the query, I did have to clear the IE cache for the problem to disappear

Any thoughts on how to capture what this issue is?

Thanks,
Jeff
Avatar of Darren
Darren
Flag of Ireland image

Hi,

Are you getting any more or better information from the SSRS logs?

C:\.......\Reporting Services\Log Files

Thanks,

Darren
Avatar of jdallen75

ASKER

@Darren, I'm having trouble finding the Reporting Services folder - recall that SSRS is deployed in SharePoint-integrated mode... would perhaps change the location of such log files?
Perhaps sharing the stored-procedure (SP) may help.

Does the SP contain any debugging/print statements?  This would introduce another result-set, which would cause the above casting error.
>> The report is configured to use a dynamic data source, such that it can be run against 1 of 5 similar sites (databases)

It appears like that across one of those databases, there is some data type mismatch in the table columns involved ..
Kindly try checking whether the procedure and table columns across all these sites are having the same datatype and if not, then fix it out to get rid of this issue. And moreover that varchar column contains some non numeric characters causing the issue..

And this explains why you are getting issues across 1 or 2 sites and not across all.
The problem typically happens on 1 or 2 sites at any given time, but not all sites. The "fix" is to connect to each site's DB and run the query once in SSMS

So, to resolve this issue
1. Identify the stored procedures or queries involved.
2. Check the tables and columns referred across those SP or queries.
3. Check whether the datatype of all table columns whether they are matching across all databases or not and fix it out.
4. Check for any incorrect non numeric values and fix it accordingly.
John, no debug selects or print statements.

Raja, the tables and SP's across all sites/DB's are identical.

The important - and very strange - thing to remember is that to fix the problem in SSRS, no code change is required, and no data needs to be fixed. The SP won't work inside the report (either in IE or SSDT, same result) - then the SP is run manually in SSMS - then the exact same query is run seconds later in SSRS, and it works fine.

The only thing I could think of originally was maybe some temp tables that didn't get cleaned up properly. I added the appropriate "DROP TABLE" statements, but this didn't resolve the issue. I think I may be hitting up MS on a support case, as nobody I've talked to can explain this.

Thanks for the input, folks.
>> then the SP is run manually in SSMS

May I know which database (out of your 5 databases) you run the SP manually, may be something related to that..
Raja, whichever one (or two) of the sites is experiencing the issue at that time.

As I said, it's very weird behaviour. It isn't an IE thing or a SharePoint thing because it also happens in SSDT directly. It's not a data issue. It's not an SP issue. And it doesn't affect all sites, just one or two, and independently (running the query in SSMS on site A fixes the problem only on site A, in the case that is happening on sites A & B. Have to run it on B in SSMS to fix it there too). Real head-scratcher!
>> running the query in SSMS on site A fixes the problem only on site A, in the case that is happening on sites A & B. Have to run it on B in SSMS to fix it there too

Okay, if that is the case, then it might be any one of the below scenarios..
1. Open cursors not closed properly..
2. Staging tables not #temp tables not dropped via the procedure which requires manual execution from query window to fix it.

Sharing your procedure can help identify whether there are any potential reasons for this behavior..
If you can't share your procedure for confidentiality reasons, then just scramble the database, procedure, column and table names and share it so that we can verify it once..
Raja,

I've attached the sanitized SP. I did notice one print statement at the end, in an error trap, but it seems doubtful that would be the cause. I will remove it anyways.

Thanks for your persistence on this issue,
Jeff
Problem-SP.sql
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Raja, thank you for the feedback.... your suggestion jogged my memory on an article I had bookmarked awhile ago on ISNUMERIC:

https://stackoverflow.com/questions/338075/cast-and-isnumeric

Running the "INSERT INTO #Variables SELECT" query for all of 2018 on one line of one site, although the Events table itself can have an Event_Num that is non-numeric (eg: a prefix on a PO#), it is exclusively a numeric value (ID) in this context. I'm wondering if I should use the:

IsNumeric(YourColumn + '.0e0')

safeguard from the article above that I've used before? (Mind you, I'm not using CAST in this case)

It could take weeks or months before this issue appears again, so in the meantime I'll accept your solution for all your attention you've given this. If any other ideas come to mind, please let me know.

Thanks!
Jeff
Thanks for the article and trying to debug why adding .0e0 works out..
Anyhow, this one should help resolve the issue you are having out..
kindly revert if you need more assistance..