I was asked to take a look at an issue the other day regarding displaying Microsoft Project Task’s Notes in SSRS 2008. If you’ve worked with building reports for Microsoft Project Server, you have no doubt dealt with the RTF data that is stored in the Task’s Notes fields.
With SQL Server 2005 Reporting Services (SSRS) it was easy to display this information by following the steps outlined in Christophe Fiessinger post “How to display Microsoft Project Task’s Notes field in a report”.
But What About SSRS 2008
With SSRS 2008, the Reporting Services engine was re-architected. Some of this new architecture included an improvement in the underlying security. The question is did these changes have an effect the method available in the blog previously linked.
Unfortunately, it does. Instead of displaying notes from Project Server as desired, you end up with row after row of N/A – as seen below.
As a result, the code used for SSRS 2005 to display the RTF will not work with SSRS 2008. Digging into the error message reveals that the reason that all of the tasks display as N/A is the following error message:
System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Windows.Forms.NativeWindow.CreateHandle(CreateParams cp)
at System.Windows.Forms.Control.CreateHandle()
at System.Windows.Forms.Control.get_Handle()
at System.Windows.Forms.Application.ParkHandle(CreateParams cp)
at System.Windows.Forms.Control.CreateHandle()
at System.Windows.Forms.TextBoxBase.CreateHandle()
at System.Windows.Forms.RichTextBox.set_Rtf(String value)
at ReportExprHostImpl.CustomCodeProxy.byteArrayToString(Byte[] b)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Security.Permissions.UIPermission
The Zone of the assembly that failed was:
MyComputer
No good. The solution fails to function anymore because of the attempt to implement System.Windows.Forms.RichTextBox from within SSRS. The new security restriction prevent this.
Can It Be Implemented Differently?
Investigating this more, I found an post on the Microsoft forums that dealt with issue. In that post, there was a reference to a StackOverflow question that also was looking for a solution to this issue.
Both posts point towards adding the following function to the report:
Public Function ConvertRtfToTextRegex(ByVal input As String) As String
Dim returnValue As String = String.Empty
returnValue = System.Text.RegularExpressions.Regex.Replace(input, "\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?", "")
Return returnValue
End Function
Instead of the code from the original Project Server post the code for the SSRS report should look like this:
'Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string
Try
s.length = 0
For i = 0 To b.Length - 1
If i <> b.Length - 1 Then
s.Append(chr(b(i)))
End If
Next
mystr = left(s.ToString, len(s.ToString)-1)
return ConvertRtfToTextRegex(mystr)
Catch ex as exception
return ex.tostring()
s.length = 0
End Try
End Function
Public Function ConvertRtfToTextRegex(ByVal input As String) As String
Dim returnValue As String = String.Empty
returnValue = System.Text.RegularExpressions.Regex.Replace(input, "\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?", "")
Return returnValue
End Function
After adding the new code, the values that come back start looking as they would have with SSRS 2005. Problem solved!
Follow-Up
While I’ve provided a solution to this issue, there is a little more to be garnered from this solution. That is the power of the SQL Server community. In solving this I didn’t have to have any inherent knowledge of UIPermissions and how they got hosed up because SSRS 2008 is more secure. This is a good thing by the way. I also didn’t have to know how to write a goofy regular expression. Thank God!
All I had to do was to take what I knew and start to search for items that were similar and follow where the trail led me. In this way the common knowledge of the community was shared previously and I am sharing it again myself.
It’s a good thing to share what you know. Whether through answering questions on forums, writing blog posts, or participating in your local SQL Server user groups. This helps raise the bar for everyone else and expands the knowledge of our community.
July 21, 2010
Categories: DBA, PASS, Performance Tuning, Presentation, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQLServerSyndication
| 
Tags: DBA, PASS, Performance Tuning, Presentation, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2
|
1 Comment
