search

BrentOzarULTD / SQL-Server-First-Responder-Kit

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
http://FirstResponderKit.org
Other
3.35k stars 993 forks source link

sp_BlitzRS Inline TSQL - False Positive #426

Closed mshahidusman closed 7 years ago

mshahidusman commented 8 years ago

I would like to report a tiny bug related to inline TSQL found in dataset. As its scanning command text and looking if keyword “Select” is there or not. If a stored procedure used in report contains ‘Select’ in name it will be in result set as false positive.

BrentOzar commented 8 years ago

Can you be more specific about the script you're talking about, what the output is, how to reproduce it, etc? I'm not even sure which stored procedure in the First Responder Kit you're talking about. Thanks!

On Aug 23, 2016, at 11:27 PM, mshahidusman notifications@github.com wrote:

I would like to report a tiny bug related to inline TSQL found in dataset. As its scanning command text and looking if keyword “Select” is there or not. If a stored procedure used in report contains ‘Select’ in name it will be in result set as false positive.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

BlitzErik commented 8 years ago

This is about BlitzRS. He left a comment on the blog about it, and I directed him here. I'll take a look, but since I don't have SSRS set up anywhere, or populated with reports, it would be nice if @mshahidusman made a code suggestion to fix it.

mshahidusman commented 8 years ago

Regeneration of Bug: Use a stored procedure in report name containing string “Select”, and it will be included as false positive for inline TSQL usage in report.

Possible Solution: Currently In report’s XML we are looking for string “SELECT” at following path “Report/DataSets/DataSet/Query/CommandText”.

To fix the issue we need to check if node “Report/DataSets/DataSet/Query/CommandText/CommandType” exists and node value is “StoredProcedure” then do not sacn “Report/DataSets/DataSet/Query/CommandText”. We also can go other way around.

Thanks

BlitzErik commented 8 years ago

Do you have either example XML documents, or XQuery written to parse this? It's helpful information, but since I don't have SSRS to test against, I wouldn't trust any XQuery I'd write to find these values.

mshahidusman commented 8 years ago

I will update thread soon.

mshahidusman commented 8 years ago

Hi Erik,

I have performed extract of Reports from my SSRS, kindly find them in attachment. I hope it will help. Let me know in case you require any additional information.

Thanks

Correct Finding.xml.txt False-Positive.xml.txt

BlitzErik commented 8 years ago

I think I see what's going on. I'll work on this when I have a chance.

BlitzErik commented 7 years ago

Closing. We're not doing any more development work on sp_BlitzRS.