So it looks like the reports are throwing an error when trying to load data from the DataSet which contains information about collections. Where in the SSRS database can I see what view the fn_rbac_Collection function is pulling data from? I’ve found several post( like this one<https://social.technet.microsoft.com/Forums/systemcenter/en-US/a8cafd1c-9de2-4e60-b34a-fef89e7a39ea/sccm-srs-issue>) describing a similar problem with reports pulling info from ARP where there is bad characters, however other reports which pull data from ARP still work, so I’m wondering if there is a collection name with a bad character which SSRS is interpreting as part of the report XML info
From: [email protected] [mailto:[email protected]] On Behalf Of Phil Wilcock Sent: Wednesday, May 20, 2015 10:41 AM To: [email protected] Subject: RE: [mssms] RE: Built in report not working Tip: if you want to test reports with the @UserSIDs parameter in a function (or any other) you can replace it with ‘disabled’ for testing purposes. e.g. fn_rbac_R_System(‘disabled’) cheers Phil From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: 20 May 2015 14:29 To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] RE: Built in report not working Nothing has changed recently in the security settings or scopes From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Tuesday, May 19, 2015 5:01 PM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] RE: Built in report not working The @UserSIDS business is SSRS figuring our the person who is running that report login id, and then using the rba function to only return back values for those objects for which that particular user has rights to view in the console. Thoughts: Did someone/something change regarding Security or Scopes in the console? On Tuesday, May 19, 2015 3:11 PM, "Krueger, Jeff" <[email protected]<mailto:[email protected]>> wrote: Well I can do a similar query in SSMS and it works, but the canned report is using some functions defined somewhere (buried in SSRS?) that SSMS doesn’t recognize it. Also cant’ think what acronym BIDS is referring to at the moment… Here’s the canned report query: Select DISTINCT sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0 FROM fn_rbac_R_System(@UserSIDs) sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID WHERE DisplayName0 like @filterwildcard and fcm.CollectionID=@CollID<mailto:fcm.CollectionID=@CollID> And running basically the same query (aside from whatever voodoo is in the function) does work: Select DISTINCT sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0 FROM v_r_system sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID WHERE DisplayName0 like '%midas%' and fcm.CollectionID='SMS0000D' From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Tuesday, May 19, 2015 3:54 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working Do the same queries work in SSMS, BIDS, or Report Builder when ran manually? Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Tuesday, May 19, 2015 3:47 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working After looking a bit more the thing that all the broken reports seem to have in common is they have a dataset referencing fn_rbac_collection and the report doesn’t give an error until after trying to load values to select the collection if it has a parameter for that. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Tuesday, May 19, 2015 3:32 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working I don’t think it’s that, I can make a new report and query the same info. And now we’ve found other reports that are broke… Also are you talking about the new SP1 for R2 that just came out? From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Monday, May 18, 2015 2:37 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working It is caused by high ASCII (or very low ASCII) characters within your ARP. They trick is to find out which client is send up the data and fix. I would start my looking for all clients that are NOT running SP1 version of the client. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Monday, May 18, 2015 2:12 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working Sorry forgot to include details, we’re running 2012R2 CU4. The report was working last week apparently, so it just recently broke. Other built in reports are working, and all our custom reports seem to be good still. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Monday, May 18, 2015 1:55 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Built in report not working Are you running CM12 Sp1? There was a fix it for this type of issue. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Krueger, Jeff Sent: Monday, May 18, 2015 1:25 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] Built in report not working Has anyone come across this scenario where a built in report has just stopped working? The report for “Computers with specific software registered in Add Remove Programs” has stopped working for us, and is generating the following error: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. There is an error in XML document (1, 168712). '', hexadecimal value 0x1F, is an invalid character. Line 1, position 168712. Also is there an easy way to restore one of the built in reports? Jeff Krueger [email protected]<mailto:[email protected]> Solutions Design Team IT - Henry Ford Health System 248.853.4466 ________________________________ CONFIDENTIALITY NOTICE: This email contains information from the sender that may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected from disclosure. This email is intended for use only by the person or entity to whom it is addressed. If you are not the intended recipient, any use, disclosure, copying, distribution, printing, or any action taken in reliance on the contents of this email, is strictly prohibited. If you received this email in error, please contact the sending party by reply email, delete the email from your computer system and shred any paper copies. Note to Patients: There are a number of risks you should consider before using e-mail to communicate with us. See our Privacy & Security page on www.henryford.com<http://www.henryford.com/> for more detailed information as well as information concerning MyChart, our new patient portal. If you do not believe that our policy gives you the privacy and security protection you need, do not send e-mail or Internet communications to us. The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
