Sql Server Reporting Service : Rendering Report From C# Code & Save The Report

One of the requirement for our last project was after generating the report the report link would be emailed to client. The client will follow the link and download the report in PDF format. So we decided that after generating the report we will save the report in the file system in PDF format.To achieve the goal we have gone through the following steps.

1.Add web reference to the url http://MyServer/ReportServer/ReportExecution2005.asmx
2.Generate a report snapshot which I described here.
3.Call the following function to get stream in your preferred format.

public byte[] GetRenderStream(string historyID, string format)
{
ReportExecutionService reportExecutionService = new ReportExecutionService();
CredentialCache credentials = new CredentialCache();
NetworkCredential serviceCredential = new
NetworkCredential(Settings.Default.ReportUserName, Settings.Default.ReportPassword);
credentials.Add(new Uri(reportExecutionService.Url), "Basic", serviceCredential);

reportExecutionService.Credentials = serviceCredential;

byte[] resultStream = null;
string reportPath = Settings.Default.ReportPath;
string format = format; // PDF,Excel etc;

string encoding;
string mimeType;
string extension;
ReportExecutionService.Warning[] warnings = null;
string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();

try
{
reportExecutionService.ExecutionHeaderValue = execHeader;
execInfo = reportExecutionService.LoadReport(reportPath, historyID);
String SessionId = reportExecutionService.ExecutionHeaderValue.ExecutionID;
resultStream = reportExecutionService.Render(format, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

return resultStream;
}
catch (Exception ex)
{
throw ex;
}
}


4. Save the returned stream as a file.

string snapshotID = CreateAndGetNewSnapShotID(int year); // this method is described
//here
string format = "PDF";
string fullFileName = @"C:\Reports\Report.pdf";
byte[] responseStream = _reportServiceProxy.GetRenderStream();

try{
FileStream stream = File.OpenWrite(fullFileName);
stream.Write(responseStream, 0, responseStream.Length);
stream.Close();
}
catch(Exception ex)
{
//catch and log exception;
}

Creating Report Snapshot Using SQL Server 2005 Reporting Service In C#

While working in the project "BI Site For Car Dealers" we need to generate report from our application instead of depending on reporting server scheduler.Here i will describe the steps
that need to be followed to generate report snapshot from c# code.


1. Add an web reference to the url http://MyServer/ReportServer/ReportService2005.asmx.
2. Now call the following function to generate report snapshot.

public string CreateAndGetNewSnapShotID(int year)
{
ReportingService2005 reportingService = new ReportingService2005();

CredentialCache credentials = new CredentialCache();
NetworkCredential serviceCredential = new
NetworkCredential(Settings.Default.ReportUserName, Settings.Default.ReportPassword);
credentials.Add(new Uri(reportingService.Url), "Basic", serviceCredential);

reportingService.Credentials = serviceCredential;

string report = "/myReport";

ReportParameter[] reportParameters;

// I have used only 1 parameter year. It is possible to use multiple parameter as filter.
reportParameters = new ReportParameter[1];
reportParameters[0] = new ReportParameter();
reportParameters[0].Name = "Year";
reportParameters[0].DefaultValues = new string[] { year.ToString() };

try
{
reportingService.SetReportParameters(report, reportParameters);
Warning[] warnings;
return reportingService.CreateReportHistorySnapshot(report,out warnings);
}
catch (Exception ex)
{
throw new Exception(string.Format("Error:Failed To Create New Subscription ErrorMessage:{0}", ex.Message));
}
}


Few things to note here:
1. User name and password is the windows creadential where the reporting server is hosted.
2. To pass the authentication process successfully it is required to configure the host which I described in an earlier post.

Report Viewer 404 Error In Asp.Net Application IIS-7.0 and Sql Server 2008-- File or directory not found

we had to face this problem while we were trying to show reprot in asp.net page with report viewer. Our website was hosted on windows 2008 server and we used Sql Server 2008 reporting service.

To overcome the issue we followed the steps described below.

1.Open IIS Manager
2.double-click on Handler Mappings icon.
3.At the Action pane on your right, click on Add Managed Handler.
4.At the Add Managed Handler dialog, enter the following: Request path: Reserved.ReportViewerWebControl.axd Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd Click OK.

Then add the following lines of code in your web config file in the section.

<add name="Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*"
type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified"/>

Fix 401 error while accessing sql server reporting service with c# code

While working with reporting service in our last project we had to face 401 issue while accessing reporting service from remote computer(using .net code).After lot of digging in the net i found the following solution which worked for me on both windows server 2003 and server 2008.

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.

Enable Scheduling and Delivery Service For SSRS

While I was working in a SSRS project i had to spent two days why scheduling and delivery process is not working in the report server.Then I found the following thisng in msdn which helped be to get rid of this problem.

Set the following values to TRUE in rsreportserver.config file.

1. IsSchedulingService
2. IsNotificationService
3. IsEventService

In my case these values had been set to False.

"Ambigious Match Found" - Works fine in develoment environment

A few days ago I ran into this problem while i was working for a project.Everything was working well in development environment.But when this was deployed in the production server things were not going as expected. After consulting with sohan and with the help of asp.net forum we figured out that my aspx page contained TextBox control "Email" and string variable email in the codebehind file

private string email;

and these two variables were raising ambiguity in the production environment though the two variables were of different types and of different casing.

After changing name of one variable the page was not raising any exception in the production environment.

Difference between Managed & Unmanaged Code

This might be old topic. But anybody who is looking for what managed and unmanaged code mean can find it here.