Hi there, I am trying to find out how to debug a CLR routine, whilest it is invoked in the SSRS Report Manager. I have been asked to make reports for my company in the Report Manager. The datasets for the rdl(c) files are prone to be T-SQL. However the complexity of some the queries require the use of Array's in memory, which T-SQL cannot support. Therefore my next step was to write a CLR-function. This is where my troubles begin. These simplified diagram of the steps I have to take: VB -> DLL -> Rdl -> XML VB = the Visual Studio VB coding in a SQL database project, that I can conveniantly debug using a test windows form or test script. DLL = executable generated from the VB code + PDB debuginfo file that I can debug in SQL Server Manager Studio. RDL = SSRS report that uses the DLL via the assemblies in the SQL database. Now here it gets hard. Up to now I have used Visual Studio 2008 to design, preview and deploy RDL files. However since Visual Studio 2010 does not support the Report Designer any more, I will be forced to use the Report Builder. In both cases I have not yet found a way yet to enter the VB-code whilest running the RDL(C) file either in Visual Studio 2008 or the Report Builder 3.0..... XML = Report Manager that runs the RDL that uses the DLL created from the VB. It happens frequently, that the DLL works fine in SQL Server Manager Studio or even in preview mode of the VS 2008 designer, but gets in an infinite loop trying to run the report in the Report Manager. So I only want to know what could possibly causes the infinite loop. My thought was to debug the CLR, but I cannot find any documentation how that could be done?????? Regards Jos I'll be back

Hello Josje, Based on my experience, we can debug the embed code in the report in Visual Studio. There is a detail document on MSDN, which provides a clear guide to debug the report in Visual studio. How to: Debug Custom Assemblies - http://msdn.microsoft.com/en-us/library/ms153693.aspx In my opinion, I suggest debugging the report on local first. After we solve the issue, we can upload the report to the report manager again. If you have any questions about this issue, please tell me. Regards, EdwardEdward Zhu TechNet Community Support

Thanks Edward, I will try out the debugging in the preview mode in VS. However my main question was how to debug the CLR in the Report Manager, that is not a part of Visual Studio. Can you enlighten me on that? Regards JosI'll be back

What did you have in mind? Meaning, debugging in VS means something very specific. There is no "debugging" in that sense in Report Manager.Mike G.

Hello Jos, I'm afraid that you cannot debug the CLR in the Report manager. Report manager is not a debug environment, but it is a release environment. It does not allow you to test the report. All development work should be finished in Visual Studio, and we can trust the debug result in these tools. If the report can work properly in these tools, but it fails on the report manager, we will check the configuration and review the log files to find out the possible causes. The final adjustment is not on the report, but on the report manager itself. Therefore, we recommend you debug the issue in Visual Studio. Regards, EdwardEdward Zhu TechNet Community Support

Hi Edward, I am sorry to hear it is not possible to debug a report and speficilly not a CLR routine used in the report, it hightens my hatred towards this product, that I consider to quite inadaquate for what I am supposed to do: make reports in as simple a fashion as possible, that can be easily be maintained in case I should not be available... My impression of the problem is that the RM is waiting for some resources that are kept locked by Visual Studio or a last session of VS that was aborted, because a VS session in preview mode that is in a loop cannot be stopped in a regular fashion. I have to cancel the entire VS to stop the looping. Nothing helps, not stop rendering or any other way to terminate the message "Loading".... As I am just a simple programmer I have no way of figuring out what resources are used and which are currently locked ("used by another program"). I have rebooted the testserver many times and sometimes the problem disappears after a reboot, but as many times it does not. It seems not all locked files are released after a reboot???? In any case this is just my assumption. When I called the network administrator the program of course ran fine (like taking your car to the garage when you think something is wrong). But at soon as he has left, I am stuck with the same problem. So perhaps you could point me to a tool to see what resources the RM is waiting for if it waiting at all, because the CPU usage is very high as is networking, but is runs endlessly for hours. Regards, JosI'll be back

Hello Jos, Thank you to share us the detail scenario you encountered. From your description, the reporting service is suffering in a performance issue. Since the performance issue is quite complex and have various causes, I cannot provide you an exact solution, which must solve your issue. If you want to an exact solution, we are very welcome you open a paid support case from http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone However, here, I can provide you a general check list about this issue. Please check whether you did the following operation in your DLL code. Were all unmanaged resources disposed before you quite from the custom DLL in report? If you access the network API, File API or ADO.net, you will use the classes which implement the interface, IDisposable. Did you call the Dispose() method before you return from a method?Did you set an Shared/static instance in the DLL, which need more and more resource? For example, set a global list to store some information. If so, it is possible that the resource on the server will be occupied.On the database side, did you use the cursor or temp database when you generate the report? If yes, did you release these resources at the end of your query? If not, it will cause database lock and resource exhausted. Besides the above check list, I think the following logs are useful to you. The Reporting service verbose log. It records all the processing information of the reporting service, and you can find any information about the process from this log.The SQL error log and trace log. It records the error and SQL statement for the database. If the issue is due to the SQL server, then it will be very helpful for you to troubleshoot this issue. I hope my information is helpful to you. If you have any questions about this issue, please tell us. Regards, EdwardEdward Zhu TechNet Community Support

Hi Edward, I considered your questions: Were all unmanaged resources disposed before you quite from the custom DLL in report? If you access the network API, File API or ADO.net, you will use the classes which implement the interface, IDisposable. Did you call the Dispose() method before you return from a method?Did you set an Shared/static instance in the DLL, which need more and more resource? For example, set a global list to store some information. If so, it is possible that the resource on the server will be occupied.On the database side, did you use the cursor or temp database when you generate the report? If yes, did you release these resources at the end of your query? If not, it will cause database lock and resource exhausted. I use VB Open/Close and Deallocate with cursors. I never use IDisposable because I thought this is implicit in the Close/Deallocate. But obviously these are not called if I have to reboot the testserver as the only way to stop VS from looping. My question rather is how to release these locked resouces or at least locate these locked resources. To this question I like an answer if you please. Regards, Jos I'll be back

Hello Jos, Thanks for your response. In .net framework, Dispose() method will invoke the function Close() normally. For example, Dispose() method in SQLConnection class will invoke the Close() function to shut down the connection first. Therefore, I hope you can invoke the Dispose() function at last. In addition, you can use the Using keyword to define the scope of the object, which implements the interface IDisposable. When we initialize the object in the Using statement, the object will keep live until it comes to the End Using. When the execution exists from the Using block, .net will invoke the Dispose() method automatically. Therefore, please try to use the Using statement for the unmanaged resource as more as possible. For more information, please refer to the following articles. http://msdn.microsoft.com/en-us/library/htd05whh.aspx If you have any questions about this issue, please tell us freely. Regards, EdwardEdward Zhu TechNet Community Support