Archive for the ‘SSRS’ Category


Executing a SSRS report in the VS works fine, but when deployed in SP environment throws error “User cannot be found“. Below is the detailed error.

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Report Server has encountered a SharePoint error. —> Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. —> Microsoft.SharePoint.SPException: User cannot be found.
   — End of inner exception stack trace —
   at Microsoft.ReportingServices.WebServer.ReportingServiceSPImpl.ListParents(String Item)
   at Microsoft.ReportingServices.WebServer.ReportingService2006.ListParents(String Item)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.SqlServer.ReportingServices2006.ReportingService2006.ListParents(String Item)
   at Microsoft.ReportingServices.SharePoint.Soap.ReportService2006.ListParents(String Item)

But other reports in different sites of the same farm seemed to be working fine. Then digging further, we came to find that the site has been restored from a different domain. The report seemed to reference a user that was in the old domain. The user is marked deleted(tp_deleted flag set to 1)  in the Content DB. We just need to set this as o. But we have to find who is that user and then do the change actually. Follow the below simple steps..

  1. Open SQL Profiler and set up a trace on the target SharePoint Content DB.
  2. Start SQL Profiler trace.
  3. Open the report you are getting the error in a browser.
  4. Go back to the SQL Profiler Trace. You should see, somewhere, a string which looks like the following
    proc_SecGetPrincipalById <GUID>,<INT_1>,<INT_2>,<INT_3>
  5. Here,
    <GUID> is the GUID of your SharePoint site.
    <INT_1> is the user id in UserInfo table that cannot be found and which we want to update.
  6. Run  the following SQL :
    SELECT * FROM UserInfo WHERE tp_SiteID = <GUID> AND tp_ID = <INT_1> (<GUID> & <INT_1> comes from step 5)
  7. Check if the value of  tp_Deleted = 1. If yes set it to 0 using the below sql query.
    UPDATE UserInfo SET tp_Deleted = 0 where TP_Id = <INT_1> AND tp_SiteID = <GUID>
  8. If the error persists, repeat steps 3 to 7 as there may be more users.

You are done. Reports work like charm 🙂

Advertisements

SSRS Reports Errors and Resolutions

Posted: December 23, 2010 in Errors, SSRS

Find the link below for SSRS reports errors and their respective solutions.

http://msdn.microsoft.com/en-us/library/ee384252(SQL.100).aspxom/en-us/library/ee384252(SQL.100).aspx


Even though there is no direct control to place a link in our SQL Server reports, we can use a textbox as a link by providing the navigation property of the text box.

When designing the report:

  • Open up a text box properties window
  • Go to Navigation
  • Enable Jump to URL
  • For the URL enter the URL you want to open from your report.


What if you want to open the link in a new window?? (And in some reports the previous case didn’t worked for me 😦 . So I did it in a round way 🙂 )

Enter the below script instead of entering the URL directly.

javascript:void(window.open(https://chanakyajayabalan.wordpress.com‘, ‘_blank’)


When your business user wants a report based on the data stored in your list, you may want to leverage SQL Server Reporting Services for this. In this case to to retrieve the data in your reports, the available options are:

  • Query the SharePoint database. Although it’s an option, this is NOT AT ALL recommended. Don’t touch your SharePoint content databases directly! Not only that it’s complicated to query it, it also adds extra load to the server that is outside of SharePoint’s control. MS also places no guarantee that the structure of the db will not change when patches or service packs are applied. Your changes may get overwritten, and you will have to re-do them, or re-architect your solution Throwing more light on this
  • Duplicate your data. You can either create an event that inserts into a parallel database when a record is inserted/updated/deleted from your SharePoint list, or dump the data to a database using a batch job that runs on a regular basis. If you choose the latter, you can go with either a console application, or PowerShell . The advantage here is you gain awesome speed in your reports. It also makes your report creation a lot easier! The big disadvantage is the latency, which may or may not be tolerable depending on your business.
  • Use Web Services But it is not great in terms of performance, and you cannot query and correlate multiple lists (for example if you need to “join” lists together in one report)
  • Use a reliable, third party tool

Here are the workflow status codes which are required, for example, you want to filter a view on the workflow status. The status enumeration is defined by the SPWorkFlowStatus enum, within the Microsoft.SharePoint.Workflow namespace in the Microsoft.SharePoint assembly.

In case if you are retrieving the list values in a SQL Reports using the SharePoint provided web service you will get these status codes only and not the text values.


NotStarted = 0

FailedOnStart = 1

InProgress = 2

ErrorOccurred = 3

StoppedByUser = 4

Completed = 5

FailedOnStartRetrying = 6

ErrorOccurredRetrying = 7

ViewQueryOverflow = 8

Max = 15


In addition, if you are using the OOTB Approval workflow then the return codes for this, as defined in the ReviewRoutingState enumeration within the Microsoft.Office.Workflow namespace (Microsoft.Office.Workflow.Routing assembly), are:


Canceled = 15

Approved = 0x10 (16)

Rejected = 0x11 (17)