June 16, 2009 at 2:10 pm
I'm trying to use some .NET functions within a SQL Reporting Services 2005 "Code" window. I get them to work fine on my local workstation but they always return a "# ERROR" message when run after deployment.
For example, I want to get a count of all text files in a particular folder on a network share. Here's the code I'm using in the CODE tab in "Report Properties":
Public Function GetFileCount(ByVal strPath As String, ByVal strPattern As String) As Integer
Try
Return System.IO.Directory.GetFiles(strPath, strPattern).Length()
Catch ex As Exception
Throw New Exception("Error From GetFileCount Function" & ex.Message, ex)
End Try
End Function
I call this function from a text box using:
=Code.GetFileCount("P:\Project\Test")
It works perfectly well on my local workstation but always returns "# ERROR" in the text box when the report is run. I assume that it's another "security" issue. Furthermore, the "try...catch" loop does NOT work for this kind of error since it always returns "# ERROR" with no additional information available. Any help in getting this to work after deployment would greatly be appreciated. Thank you.
Denis Repke
Denis W. Repke
June 16, 2009 at 3:54 pm
Is this embedded custom code or a custom assembly?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2009 at 7:14 am
It's just a block of code using a basic .NET System.IO function that is entered into the "Code" tab of "Report Properties". I am not referencing any assembly. I don't think I need a reference. When you "browse" the assemblies, the only "System.IO" assembly available to reference is "System.IO.Log". As I mentioned, it runs perfectly on my workstation but fails when it is deployed. It seems like a "security" issue to me but I have no idea how to fix it. The file path I'm trying to use is on a network share but I've also trying using a path on our SQL Server itself (which is where Reporting Services are running) with the same result.
Denis W. Repke
June 17, 2009 at 3:10 pm
I am not an SSRS expert, and I could not find anything on Google, BOL or msdn about debugging embedded custom code functions (doesn't mean it isn't there, I just couldn't find it).
Failing all of that, I would recommend reverting to old school/low-tech debugging.
First write the following function based on your original function:
Public Function GetFileCountWErr(ByVal strPath As String, ByVal strPattern As String) As Integer
Try
Return System.IO.Directory.GetFiles(strPath, strPattern).Length()
Catch ex As Exception
Return "Error " & Err.Number & ", from " & ex.Source & ": " & ex.Message
End Try
End Function
(note: corrected code is below)
Now either change the calls to your original function with calls to this one, or add another column in your report that calls this function (be sure to give it a lot of space). Now try to deploy and run you report and this should give you a muc better error message.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2009 at 3:27 pm
Thanks for the response but it didn't help any. I've already determined that the error returned is NOT caught by the Try...Catch loop. To confirm again, I did try your modified routine and, as expected, it still returned "# ERROR" in the RSS Text Box. I still think it is a "security-related" issue.
Denis W. Repke
June 17, 2009 at 4:42 pm
It still tells you a lot about what the problem is. For instance, even if it is security related, you now know that the problem is not that you are not authorized to access some set of files, rather, it probably means that you are not allowed to call custom code at all (again, if it is security related).
The next step would be to confirm this: change your function to be the simplest possible: just return a 1 or an "A", do nothing else:
Public Function GetFileCountWErr(ByVal strPath As String, ByVal strPattern As String) As Integer
Return 1
End Function
Now, if you still get the error you can be certain that it is not even letting you call the function.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 17, 2009 at 4:44 pm
Hmm, I just noticed that my previous example defines an integer function, but returns a string. That will certianly fail, so here is a corrected version for any who care:
Public Function GetFileCountWErr(ByVal strPath As String, ByVal strPattern As String) As String
Try
Return System.IO.Directory.GetFiles(strPath, strPattern).Length()
Catch ex As Exception
Return "Error " & Err.Number & ", from " & ex.Source & ": " & ex.Message
End Try
End Function
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2009 at 8:35 am
Thank you. Changing the function from "integer" to "string" did the trick. At least now I get a "real" error message back. As I suspected, it is a "security" issue. Here's the error message:
Error 5, from mscorlib: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Now I have to figure out how to "fix" this issue. Any thoughts to point me in the right direction?
Denis W. Repke
June 18, 2009 at 10:50 am
Hmm, now that does NOT look like a normal security error (which would name the file). Rather it looks like a SQLCLR CAS security error, which I am not sure how it would apply to custom embedded code?
If you were adding an assembly, I would say that you needed to CREATE it with EXTERNAL_ACCESS permission.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2009 at 11:14 am
I found some pertinent info by "googling" the web. I found some .NET code that "should" do the trick except for one thing. It's difficult figuring out how the code has be structured and/or modified so that it can be used directly from SQL Reports. I haven't got it work yet but I haven't had much time to play around with it yet either. I also found another possible way of handling this, by adding a block of code to at least one of the Reporting Service "rdl" files or "config" files. I haven't had the time yet to pursue that one either. I "ought" to able to get one of these methods to work. I'll let you know if I get it figured out.
Thanks again for you help.
Denis W. Repke
June 18, 2009 at 11:25 am
Let us know what you figure out. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply