jdallen75
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:
Here are some important outcomes from my troubleshooting so far:
Any thoughts on how to capture what this issue is?
Thanks,
Jeff
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:
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
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.
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.
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.
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.
ASKER
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, 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..
May I know which database (out of your 5 databases) you run the SP manually, may be something related to that..
ASKER
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!
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..
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..
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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..
Anyhow, this one should help resolve the issue you are having out..
kindly revert if you need more assistance..
Are you getting any more or better information from the SSRS logs?
C:\.......\Reporting Services\Log Files
Thanks,
Darren