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

jdallen75
jdallen75 used Ask the Experts™
on
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:

The error message
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DarrenSenior Software Engineer

Commented:
Hi,

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

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

Thanks,

Darren

Author

Commented:
@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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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.

Author

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..

Author

Commented:
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!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..

Author

Commented:
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
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Thanks, I've verified the attached procedure and it seems normal..
No Cursors or transactions or loops, just creation of temporary tables, populating them, updating temp tables and fetching result from temp tables.

While I was checking the procedure, I suspect the below code might be causing the issue..
Table #Variables was declared with Event_Num column as VARCHAR(40) and was populated with Event_Num column from .GBDB..Events tables. Kindly confirm the datatype of Event_Num column in that table..

While selecting records from #Variables table, we are having the below condition..
FROM #Variables
WHERE ISNUMERIC(Event_Num) = 1

Open in new window


This seems to be the place why you are getting Error converting varchar to Numeric error..
Kindly check the data in Events table once and see whether those non numeric records are valid or not, this can help resolve the issue..

Author

Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial