Have you ever noted down the difference when you open a CSV file in Excel which is exported from REPORTING SERVICE 2005 and REPORTING SERVICE 2008?
The one which is exported from REPORTING SERVICE 2005 shows all the values for each row in a single column separated by comma. Whereas the one which is exported from REPORTING SERVICE 2008 shows each row values which are separated by comma in an individual columns.
The reason here is,
The default encoding for CSV has changed from REPORTING SERVICE 2005 to REPORTING SERVICE 2008.
REPORTING SERVICE 2005 : http://msdn.microsoft.com/en-us/library/ms155365(SQL.90).aspx (Encoding - One of the character encoding schemas: ASCII, UTF-7, UTF-8, or Unicode. The default value is Unicode.)
REPORTING SERVICE 2008 : http://msdn.microsoft.com/en-us/library/ms155365.aspx (Encoding - One of the character encoding schemas: ASCII, UTF-7, UTF-8, or Unicode. The default value is UTF-8.)
Whenever a file with encoding UTF-8 is opened in Excel, it automatically converts the comma separated values in to individual columns.
So if you want your REPORTING SERVICE 2008 exported CSV to shows all the values for each row in a single column separated by comma (Just as REPORTING SERVICE 2005), you need to change the encoding to Unicode as follows.
1. Open the Reporting ServiceReportServer.config file (Located @ C:\Program Files\Microsoft SQL Server\MSREPORTING SERVICE10.SSREPORTING SERVICE2008\Reporting Services\ReportServer).
2. Locate the element, <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"/>
3. Replace the entire line with the following,
< Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<Configuration>
<DeviceInfo>
<Encoding>Unicode</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
4. Save the file and Restart the Report server windows service (From Reporting Service configuration manager).
5. Now open the report manager, run the report and export the same to CSV.
6. Then open it in Excel and you’ll see the file getting opened in the way you expected (Values listed in one column with comma separated).
On the other hand if you want your REPORTING SERVICE 2005 exported CSV to show each row values which are separated by comma in an individual columns (Just as REPORTING SERVICE 2008), you need to change the encoding to UTF-8 as follows.
1. Open the Reporting ServiceReportServer.config file (Located @ C:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer).
2. Locate the element, <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>
3. Replace the entire line with the following,
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>UTF-8</Encoding>
</DeviceInfo>
</Configuration>
</Extension>
4. Save the file and Restart the Report server windows service (From Reporting Service configuration manager).
5. Now open the report manager, run the report and export the same to CSV.
6. Then open it in Excel and you’ll see the same behavior that you see in REPORTING SERVICE 2008.
HTH!
Selva.
I was working on an interesting case where the reporting service was moved back to Windows authentication from Forms based authentication (FBA).
There were lot of subscriptions which were created using a forms based user account.
Now when the reporting service was moved back to Windows authentication, the reports which contains all these subscriptions we couldn’t click on the Subscriptions tab. When you click it, it was failing with the following error and call stack.
w3wp!ui!5!9/2/2009-11:01:29:: e ERROR: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.
at Microsoft.ReportingServices.Authorization.SdAndType.PrepareToRestoreBinaryPolicy(Byte[] secDescBlobStored, Hashtable& secDescHash)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.InnerCheckAccess(String userName, IntPtr userToken, SecurityItemType itemType, Byte[] secDesc, UInt32& rightsMask, ReportSecDescType rptSecDescType)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)
at Microsoft.ReportingServices.Library.SubscriptionManager.ListSubscriptions(String user, String path, Boolean pathIsSite)
at Microsoft.ReportingServices.Library.ListSubscriptionsAction.PerformActionNow()
at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()
at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSubscriptions(String Path, Boolean pathIsSite, String Owner, Subscription[]& SubscriptionItems)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSubscriptions(String Path, Boolean pathIsSite, String Owner, Subscription[]& SubscriptionItems)
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSubscriptions(String Report, String Owner, Subscription[]& SubscriptionItems)
w3wp!ui!5!9/2/2009-11:01:29:: e ERROR: HTTP status code --> 200
-------Details--------
System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.
at Microsoft.ReportingServices.Authorization.SdAndType.PrepareToRestoreBinaryPolicy(Byte[] secDescBlobStored, Hashtable& secDescHash)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.InnerCheckAccess(String userName, IntPtr userToken, SecurityItemType itemType, Byte[] secDesc, UInt32& rightsMask, ReportSecDescType rptSecDescType)
at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)
at Microsoft.ReportingServices.Library.SubscriptionManager.ListSubscriptions(String user, String path, Boolean pathIsSite)
If you look at the above call stack, the current user who is a Windows user account is getting validated against the person who created the subscriptions for the report, which is nothing but the FBA user and that is the place it is failing (Authorization section).
To confirm this I created the following SQL statement,
select s.*,c.Name as [Report Name],u.* from Subscriptions s, [Catalog] c, Users u where s.Report_OID = c.ItemID and s.OwnerID = u.UserID
What is does is, it will give me the list of subscriptions and their details from the Subscriptions table, then display the corresponding Report Name for which it was created, from the Catalog table and then display the user details who created the report from the Users table.
When I ran this SQL statement, it clearly showed that the subscription was created by a FBA user and the AuthType showed 3 which is nothing but custom authentication.
Now there is nothing much we can do here other than dropping the subscriptions and recreating them. But if you’re having too many then that is always going to be a problem.
So the only other remaining option is to DIRECTLY EDIT THE SUBSCRIPTION TABLE’s USERID column, which is NOT RECOMMENDED and FALLS UNDER MICROSOFT UNSUPPORTED SCENARIO.
But Since there are no other option let, the RISK was taken by the END USER himself and implemented the following.
Back up the ReportServer database.
Go to the corresponding subscription in the subscription table and change the value UserID value to one of the valid windows USERID listed in the Users table.
That bought back the subscriptions and things were back on track.
Recently I was looking for a sample that will help me with the concept of Passing parameters to sub reports in RDLC. I didn’t get one. So I thought of exploring more on to this and started off the journey.
The tricky part with the RDLC is, even though it gives you the option of creating report parameters, you cannot assign values from a query.
More over you cannot make the parameter prompts visible as you do in RDL.
So the trick here is, the person who is designing the report should use the ASP.NET controls for parameters and pass it along with the query that you’re using it for filing your report data set.
The other way is to define parameters, pass the values to parameters from the ASP.NET control to the Report Viewers Local report parameter collection and then just create filters in the table / matrix / tablix. But the problem here is, your performance of the report can be impacted as the query might bring large number of data for each request (based on the query).
I’ll be explaining both the concepts. But let’s get started with the passing the parameters from the ASP.NET controls to the query and get the filtered data for the report data set.
In our sample we’ll be using ADVENTUREWORKS database. The tables which I’ve selected are Department and EmployeeDepartmentHistory.
The objective here is, the main report REPORT1.rdlc will show the Department table details and contains a sub report component with REPORT2.rdlc which displays EmployeeDepartmentHistory. All the data are based on the parameters that we choose in the ASP.NET page which contains the ReportViewer control.
1. Open VS 2005 / 2008 and create a ASP.NET web application. (Choose either VB.NET / C#. I’ll be talking from C# perpective.)
2. Add a new report item called REPORT1.RDLC.
3. In the Website Data Sources, click on the Add New Datasource icon.
4. Leave the dataset name as Dataset1.
5. Point the connection to the appropriate SQL Server and map it with the Department table.
6. Create another new datasource (step 3) and name it as Dataset2.
7. Map it to the same SQL Server as in step 5 and map it with the table EmployeeDepartmentHistory table.
8. Now from the toolbox drag and drop a table component in to REPORT1.
9. From Dataset1, drag and drop the DepartmentID and DepartmentName columns in to the report table.
10. Now goto the solution explorer and add a new item, REPORT2.RDLC.
11. Drag and drop a table component in to REPORT2. Then drag the columns DepartmentID, ShiftID and ShiftDate.
12. Then add a group based on DepartmentID which displays the DepartmentID in the Group Header.
13. Go back to REPORT1.rdlc and drag a sub report component from the toolbox. Point the sub report to REPORT2.rdlc.
14. With this we completed the design of the Dataset1, Dataset2, Report1.rdlc and Report2.rdlc. Now lets design the ASP.NET page.
15. Switch to the Default.aspx page design layout. There you’ll already find Objectdatasource1 and Objectdatasource2.
16. Drag and drop a Listbox control, ListBox1 (For multi value parameters).
17. Go to the properties of the ListBox1 (Click on the > icon that appears to the top right on the control) and click on Choose datasource link.
18. Choose the following,
Datasource: ObjectDataSource1
Data field for display : Name
Data field for Value : DepartmentID
19. Make sure the selection mode for the ListBox1 is set to Multiple.
20. Drag and drop the report viewer control, ReportViewer1 from the tool box.
21 . Go to the properties of the ReportViewer1 (Click on the > icon that appears to the top right on the control) and choose Report1.rdlc from Choose Report drop down.
22. Now drag a button control, btnView adjacent to the ListBox1.
This brings us to the end of the design phase. Lets step in to the coding part. The coding part needs to be done little carefully. The reason, it has to follow a specific order to get the report properly. The sequence is as follows,
1. Get the list of parameters selected.
2. Build the SQL query with the parameters and get the result in to the corresponding datasets.
3. Bind the datasets back to the reports (Main and sub report).
4. Refresh the control to show the updated report.
Lets have a look at the code within the PageLoad event of the default.aspx page. Inline Comments are provided to explain the code pieces.
protected void Page_Load(object sender, EventArgs e)
{
//Eveytime you add a data source to the report, it adds itself in to //a collection. Reason, report allows more than one data source to be //used within itself.Unless you specify the data source with index, //DataSources[0] will be taken automatically since our report has only //one data source. In our case for every refresh we re-execute the //query and get different result set. So to make sure we keep the //current dataset as default and to prevent data sets getting bulged //up, we’re making sure to remove the last available data set and then //add the current data set.
if (this.ReportViewer1.LocalReport.DataSources.Count > 0)
{
this.ReportViewer1.LocalReport.DataSources.RemoveAt(0);
}
//Adding the newly filled data set to the report. After this the Data //Source count will show 1. The first parameter is the name of the data //set bound with the report** and the next is the call to method which //will execute the QUERY and return a data table that contains the //current data.
this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1_Department", GetDepartmentData()));
//For the sub report to be processed, we need to add the sub report //processing handler to the main report and that is what is shown //below. The data set processing definition for the sub report will be //defined with in that method.
this.ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(localReport_SubreportProcessing);
}
Lets have a look at the SubreportProcessing event handler:
//All we’ve done here is, we’re binding the data set to appropriate report data source**.
void localReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
{
e.DataSources.Add(new ReportDataSource("DataSet2_EmployeeDepartmentHistory", GetEmployeeData()));
}
** To view the name of the data set (i.e DataSet1_Department) Go to the Report1.rdlc -> Report menu -> Data Sources.. -> Under Report Data Sources, you’ll see the list of data sources, if the report contains more than one.
Now we’re going to look at the method GetEmployeeData () that fetches the data to the data set DataSet2_EmployeeDepartmentHistory for Report2.rdlc / ObjectDataSource2.
private DataTable GetEmployeeData()
{
//Since string is immutable, we’re using StringBuilder as we’ll be //changing the data frequently based on the parameter selection.
StringBuilder param = new StringBuilder();
//We need to get the selected parameters seperated by “,” and enclosed //within the “’” (i.e ‘1’,’3’,’7’). This is to make sure we‘ve the //proper list of multi value parameters.
foreach (ListItem list in ListBox1.Items)
{
if (list.Selected)
{
param.Append("'" + list.Value + "',");
}
}
string paramvalues = string.Empty;
//Just initializing the parameter values with a default value. This //will come in to picture only during the first exectution as we need //to show some data as soon as the report loads for the first time.
if (param.Length == 0)
{
paramvalues = defaultValue;
}
else
{
//Making sure the last “,” which got added in the above foreach loop is //removed before the values are passed to the queries.
paramvalues = param.ToString().Remove(param.ToString().LastIndexOf(",")).ToString();
}
//Creating a new SQL server connection object.
using (SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=Adventureworks;Integrated Security=SSPI"))
{
//Creating a new SQL Adapter along with the SQL query and the //connection.
SqlDataAdapter adap = new SqlDataAdapter("SELECT EmployeeID, DepartmentID, ShiftID, StartDate, EndDate, ModifiedDate FROM HumanResources.EmployeeDepartmentHistory where DepartmentID IN (" + paramvalues + ")", sqlConn);
//Create the typed data set of EmployeeDepartmentHistory and fill it //with the data.
DataSet2 ds = new DataSet2();
adap.Fill(ds, "EmployeeDepartmentHistory");
//Return the filled Data Table.
return ds.EmployeeDepartmentHistory;
}
}
Lets look at the method GetDepartmentData () that fetches the data to the data set DataSet1_Department for Report1.rdlc / ObjectDataSource1.
private DataTable GetDepartmentData()
{
//Since string is immutable, we’re using StringBuilder as we’ll be //changing the data frequently based on the parameter selection.
StringBuilder param = new StringBuilder();
//We need to get the selected parameters seperated by “,” and enclosed //within the “’” (i.e ‘1’,’3’,’7’). This is to make sure we‘ve the //proper list of multi value parameters.
foreach (ListItem list in ListBox1.Items)
{
if (list.Selected)
{
param.Append("'" + list.Value + "',");
}
}
string paramvalues = string.Empty;
//Just initializing the parameter values with a default value. This //will come in to picture only during the first exectution as we need //to show some data as soon as the report loads for the first time.
if (param.Length == 0)
{
paramvalues = defaultValue;
}
else
{
//Making sure the last “,” which got added in the above foreach loop is //removed before the values are passed to the queries.
paramvalues = param.ToString().Remove(param.ToString().LastIndexOf(",")).ToString();
}
//Creating a new SQL server connection object.
using (SqlConnection sqlConn = new SqlConnection("Data Source=Localhost;Initial Catalog=Adventureworks;Integrated Security=SSPI"))
{
//Creating a new SQL Adapter along with the SQL query and the //connection.
SqlDataAdapter adap = new SqlDataAdapter("SELECT DISTINCT DepartmentID, Name FROM HumanResources.Department where DepartmentID IN (" + paramvalues + ")ORDER BY DepartmentID", sqlConn);
//Create the typed data set of Department and fill it
//with the data.
DataSet1 ds = new DataSet1();
adap.Fill(ds, "Department");
//Return the filled Data Table.
return ds.Department;
}
}
Finally, whenver whenever we click on the View Report button, the query has to be executed with the new set of parameters and then the entire above operation as to be executed. The below code invokes it.
protected void btnView_Click(object sender, EventArgs e)
{
this.ReportViewer1.LocalReport.Refresh();
}
That pretty much concludes our sample. I’ve uploaded the sample please change the connections string values and try executing the report. Happy programming!!.
1. How Duplicate SPN's in IIS can come in to picture?
An SPN is a service principal name and usually uniquely identified in a domain with it's service name.
for eg: If we have a machine name "test" in the domain "contoso.com" and we have a web site (ws1) which is served by an application pool in IIS running under a user "dummy"
we will set the SPN for it as follows:
setspn -A HTTP/test contoso\dummy
setspn -A HTTP/test.contoso.com contoso\dummy
If there is another site (ws2) which is served by a different application pool identity of "dummy1"
we will set the SPN for it as follows:
setspn -A HTTP/test contoso\dummy1
setspn -A HTTP/test.contoso.com contoso\dummy1
Similarly , if there are multiple sites, we would be setting the SPN's for the same SERVICE under DIFFERENT USER CONTEXT
Seems to be a right approach if we are setting the sites for kerberos delegation...right?
We searched around and found the following KB article: http://support.microsoft.com/kb/871179
The following section is important to understand:
An SPN for a service can only be associated with ONE DOMAIN ACCOUNT. Therefore, any other application pool that is running under a different domain user account cannot be used with Integrated Windows authentication only.
What this means is, the above two SPN's that we set are duplicate even though they are running under two different domain accounts.
Then how can we enable kerberos for both the websites?
Well there are two ways out there.
1. To change all the application pools account (Of all the web sites that you want to use Kerberos) to run under the same account.
2. To create a DNS record to be used as host header on the websites.
So , if we create a host header known as hh1.contoso.com and use it for the website ws2
The SPN's for ws2 becomes:
setspn -A HTTP/hh1.contoso.com contoso\dummy1 (so we have only one SPN to set)
This would resolve the issue of dupliacte SPN's.
PROBLEM:
When you try to run a report (or) Generate a model from an existing Data Source (or) Open a Model using Report Builder from the Report Server, you get the following error,
An attempt has been made to use a data extension 'SQL' that is not registered for this report server.
ROOT CAUSE:
One possible cause of this issue is that the registered SQL extension in your report server config file would have got modified / removed.
SOLUTION:
Open RsReportServer.config file located under <installation path of reporting service> / reportserver folder.
Locate the element,
And make sure it is there as below,
<Extensions> <Data>
<
Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,
Microsoft.ReportingServices.DataExtensions"/>
<
SemanticQuery>
<
Extension Name="SQL" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQL.MSSqlSQCommand,
Microsoft.ReportingServices.SemanticQueryEngine">
PROBLEM:
1. Consider a report (rptParent) with a multi value query based parameter and it has links (table cell action “Go to report”) to rptChild.
2. The report (rptChild) renders pased on the parameter value it receives from rptParent.
3. When viewed on report server all the links work, both rptParent and rptChild works too.
4. Now either EXPORT or set up subscription to rptParent to MHTML / WORD etc.
5. When clicking the links on the exported / subscribed report rptParent, the report server complains about one of the parameter missing and would not render the report rptChild.
EXPLANATION:
This will work perfectly fine in Reporting Service 2005. The issue got introduced only in Reporting Service 2008.
This is because the way the URL is constructed. Notice the Urls generated.
Failing: http://localhost/ReportServer?%2fParametersTest&ParamValue=1%2c2&rs%3aParameterLanguage=&rc%3aParameters=Collapsed
Working: http://localhost/ReportServer?%2fParametersTest&ParamValue=1&ParamValue=2&ParamValue=3aParameterLanguage=&rc%3aParameters=Collapsed
WORKAROUND:
As there are currently no plans to fix this in Katmai, follow the below workaround.
Try to construct the Drillthrough link manually as a hyperlink. The expression might look something like:
=Globals.ReportServerURL & "?" & Uri.EscapeDataString(Globals.ReportFolder & "/" & <<<TargetReportName>>> ) & <<ParametersAsString>>
For example, if the target report name is "ParametersTest" and you wanted to pass values 1, 2 and 3 to parameter "ParamValue", the URL would be:
=Globals.ReportServerURL & "?" & Uri.EscapeDataString(Globals.ReportFolder & "/ParametersTest") & "&ParamValue=1&ParamValue=2&ParamValue=3"
Recently i was working on an issue.
In Reporting Service 2005 you set FixedHeader property to a table control. When the dataset bound with the table returns no data in combination with Internet Explorer -> Tools -> Internet Options -> Advanced -> Uncheck Disable Script Debugging (Internet Explorer), you'll receive a Javascript error.
---------------------------
Error
---------------------------
A Runtime Error has occurred.
Do you wish to Debug?
Line: 12
Error: Object required
---------------------------
Yes No
---------------------------
This happens with both IE 6 and IE 7.
WORKAROUND:
=============
1. Set the No Rows property of the table with some value like "Sorry no data returned"
OR
2. Add the expression for the visiblity of the table which holds the data.
=IIF((RowNumber("DataSet")=0),True,False)
But consider the situation where you've 100's of reports already deployed in the report server. You'll not be in favor of editing each and every report and re deploy the same.
For that i wrote a piece of C# code which will read each and every deployed report from the report server, scan through the table element and where ever the FixedHeader property is set, it inserts an element <NoRows> and puts the report defintion back at the report server. I've tested the code and it works great.
CODE:
=====
Below is a console application code written in C# which accepts two parameters, one the complete Report Server URL and the other is the message that need to displayed when there are No Rows. (You can leave it blank as well)
Like this: somename.exe "http://machinename/reportserver" "Query didn't return any data "
I've commented the code for easier understanding.
Additional references,
using System.Xml;
using System.Xml.XPath;
using System.IO;
Make sure to add a Web Reference for ReportService.asmx (2005 end point) and include it in the project with the using statement.
class Program
{
static void Main(string[] args)
{
if (args.Length == 2)//Make sure we pass exactly two parameters.
{
try
{
ReportingService rs = new ReportingService();
int totalReports = 0; //To keep track of number of reports that were processed.
int editedReports = 0; //To keep track of number of reports that were edited.
rs.Url = args[0] + "/ReportService.asmx"; //First command line argument, takes the appropriate Report Server URL.
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//Retrieving the list of all the items deployed in the Report Server.
CatalogItem[] catalogitems = rs.ListChildren("/", true);
//Looping through each and every item.
foreach (CatalogItem item in catalogitems)
{
//We're concerned only about the reports and thats were the processing starts.
if (ItemTypeEnum.Report == item.Type)
{
bool rdlModified = false; //To make sure only the modified RDL's are written back.
bool isFixedHeader = false; //To make sure we edit only the RDL's that has Fixed Headers.
string reportPath = item.Path; //Path of the report that is currently being processed.
byte[] reportDefinition = null; //Holds the Original RDL byte array.
byte[] newReport = null; //Holds the Modified RDL byte array from the stream.
XmlDocument xmldoc = null; //To hold the RDL in the XML form.
reportDefinition = rs.GetReportDefinition(reportPath);
Console.WriteLine("Processing the report: " + reportPath);
totalReports++;
using (MemoryStream memOriginalRDL = new MemoryStream(reportDefinition))
{
xmldoc = new XmlDocument();
xmldoc.Load(memOriginalRDL);
memOriginalRDL.Close();
}
XmlNode root = xmldoc.DocumentElement;
XmlNodeList nodelist = root.SelectNodes("descendant::*"); //To make sure we've all the elements under the root are traversed.
foreach (XmlNode node in nodelist)
{
if (node.Name == "Table") //We're only concerned about table.
{
int count = 0;
XmlNodeList childList = node.ChildNodes;
foreach (XmlNode childnode in childList)
{
if (childnode.Name == "NoRows") //Checking if there is NoRows element already present.
{
count++;
}
if (childnode.Name == "Header") //Setting the flag if only we've the Fixed Header enabled.
{
XmlNodeList headerChildList = childnode.ChildNodes;
foreach (XmlNode headernode in headerChildList)
{
if (headernode.Name == "FixedHeader")
{
isFixedHeader = true;
}
}
}
}
if (count == 0 && isFixedHeader) //Only if NoRows is not available, proceed with the RDL edit.
{
Console.WriteLine("\t Editing the report: " + reportPath);
XmlElement newElement = xmldoc.CreateElement("NoRows", root.NamespaceURI.ToString());
newElement.InnerText = args[1].ToString(); //Second argument from the commandline, customized message to display.
node.AppendChild(newElement);
rdlModified = true;
}
}
}
if (rdlModified)//If only RDL is modified.
{
//Get the XML back to memory stream and Convert the memory stream in to byte array.
using (MemoryStream memModifiedRDL = new MemoryStream())
{
xmldoc.Save(memModifiedRDL);
newReport = memModifiedRDL.GetBuffer();
memModifiedRDL.Close();
}
//Put it back in to Report Server database.
ReportService2K5.Warning[] warning = rs.SetReportDefinition(item.Path, newReport);
editedReports++;
Console.WriteLine("\t finished editing the report: " + reportPath);
}
}
}
Console.WriteLine("\n\n================================");
Console.WriteLine("Completed the Processing of {0} reports. Edited {1} reports. Hit enter to QUIT.",totalReports,editedReports);
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine("Exception occured: " + ex.Message.ToString());
}
}
else
{
Console.WriteLine("Please run the EXE from the command line with the ReportServer URL like : EditRDLProgramatically.exe 'http://machinename/reportserver' 'QUERY doesn't return any data'");
Console.ReadLine();
}
}
}
PROBLEM:
When we set the RepeatWith property of a TEXTBOX to the table / data region that holds the data, the textbox is not shown in other pages after rendering the report in PDF / Print Preview / Image.
WHY?
The behavior you are seeing is by design. The RepeatWith functionality is not supported in "physical pagination" renderers: Print, Image, and PDF.
SOURCE:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=349132
WORKAROUND: (Different scenarios discussed)
1) Any controls that need to repeat before the table should be moved into the table header. Each row in the table header should be set to RepeatOnNewPage=True and The RepeatWith property should not be set.
2) Any controls that need to repeat in the table footer or after the table should be moved into a new detail row in the table. The RepeatWith property should not be set. Any controls currently in the table footer that are not set to RepeatWith should be left where they are. RepeatOnNewPage should be False for the rows in the table footer.
3) All the controls that were moved into the new detail row in the table should have the Visibility, Hidden=True set.
4) Create new controls in the Page Footer which reference the hidden controls in the Table. e.g. textbox24.Value = ReportItems!textbox4.Value, where textbox24 is in the Page Footer and textbox4 is a hidden control in the detail section.
ISSUE:
As per definition, http://msdn.microsoft.com/en-us/library/ms251779.aspx
Growing and Shrinking a Text Box
By default, text boxes are a static size. If you want to expand a text box vertically based on its contents, change the CanGrow property for the text box. If you want to allow the text box to shrink based on its contents, change the CanShrink property for the text box. You can access these properties through the Properties window or through the Textbox Properties dialog box.
But when you place the textbox within a data control like TABLE / MATRIX, the CanShrink property is never honored. At the same time the CanGrow property will always work as expected.
EXPLANATION:
This is "By Design" behavior. The table row/column will not shrink below its definition height/width. This is in order to mitigate performance and memory concerns. Also the impact should be minimal, since we allow the row to grow.
You start with a small height for the table row and let it to grow to accommodate the content.
Consider a sample which reads an image from the SQL Server table (Adventure Works database), whose column is VARBINARY(MAX). The code sample goes like this,
Odbc.CommandText = "select ProductPhotoID,LargePhoto from Production.ProductPhoto where ProductPhotoID = '70'"
Dim dr As Odbc.OdbcDataReader = cm.ExecuteReader(CommandBehavior.SequentialAccess) à Happens with the ODBC SQL Server driver and the SQL Native client driver
Dim bufferSize As Int32 = 5000
Dim blob(bufferSize - 1) As Byte
Dim retval As Long = 0
Dim startIndex As Int64 = 0
retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)
With VS.NET 2003:
=================
retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)
The above statement always returns the right number of bytes and the image is properly fetched.
With VS.NET 2005:
=================
retval = dr.GetBytes(1, startIndex, blob, 0, bufferSize)
The above statement always returns 0.
When I change the Dim dr As Odbc.OdbcDataReader = cm.ExecuteReader(CommandBehavior.SequentialAccess) to CommandBehavior.CloseConnection then everything starts working.
When I use SqlClient everything works fine as expected.
RESOLUTION:
-
This is a known issue with .NET 2.0 and it is fixed in VS 2008 SP 1.
-
To work around the issue, either you've to use CommandBehavior.CloseConnection or use System.Data.SqlClient instead of System.Data.ODBC.
PROBLEM:
========
- Consider you've a ASP.NET application that contains Report Viewer control (2005 / 2008) in Local Mode.
- You have an RDLC report file, that loads large amount of data / has lots of expressions. (Both are not recommended in Local mode)
- Everytime you refresh the web page, the Report Viewer stores objects in the session.
- The behaviour of Report Viewer storing objects in the session is by design.
- Each time the report viewer page is refreshed the complete report info object is added to session.
- These objects obviously gets deeply rooted in session and so Garbage collector never collects them untill the complete app unloads itself.
- And that is apparently going to increase the memory pressure in multiple folds, ending up with System.OutOfMemoryException.
RESOLUTION: (Please note: This doesn't guarantee to resolve the exception. The Out of Memory exception can be caused due to different reasons and the below workaround is for one such scenario, which can help to avoid this error to a certain extent.)
===========
== In the page_load event, add this,
== VB.NET
If Session.Count > 0 Then
For i As Integer = 0 To Session.Count - 1
If Session(i).GetType().ToString() = "Microsoft.Reporting.WebForms.ReportHierarchy" Then
Session.RemoveAt(i)
End If
Next
End If
== C#,
if(Session.Count > 0)
{
for (int i = 0; i < Session.Count; i++)
{
if (Session[i].GetType().ToString() == "Microsoft.Reporting.WebForms.ReportHierarchy")
{
Session.RemoveAt(i);
}
}
}
Consider the following scenario in Microsoft SQL Server 2005 Reporting Services. You use SQL Server Business Intelligence Development Studio to create a report model project. In the report model project, you create a data source against a SQL Server 2005 database. The database has the ALLOW_SNAPSHOT_ISOLATION option enabled. You set the Isolation option to Snapshot in the Data Source Designer dialog box for the data source. In this scenario, when you use a report generated from this report model, you may find the Isolation option has no effect, neither does the Query timeout option.
For more details, please refer http://support.microsoft.com/?id=952112
Created a sample code.
== It has the ConnectionLifetime attribute in the connection String set to 10 secs.
== Now in the code, i'm having a loop which will open the connection, executes a statement.
== Waits for 15 secs and then closes the connection.
== Then waits for 15 secs and then tries to reopen the connection.
== Ironically speaking, after 10 secs, the Pool Manager should flush the existing connection based on the connection lifetime.
== Then for the second connection request, a new connection has to be opened.
== But in this case i don't see it. When i open a new connection, it still pulls up from the existing pool.
== Here is the CODE,
SqlConnection sqlConn = new SqlConnection("Data Source=*****;Integrated Security=SSPI;Application Name=XXXX;Connection Lifetime=10");
SqlCommand comm = new SqlCommand("Use Northwind");
for (int i = 0; i < 3; i++)
{
sqlConn.Open();
comm.Connection = sqlConn;
comm.ExecuteNonQuery();
System.Threading.Thread.Sleep(15000);
sqlConn.Close();
System.Threading.Thread.Sleep(15000);
}
== As per the definition of ConnectionLifetime states that, (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.71).aspx)
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online.
A value of zero (0) causes pooled connections to have the maximum connection timeout.
== But the behavior is not as mentioned.
Microsoft has confirmed the same as a BUG and will be investigating more in to this.
When you browse the Report Manager, you're getting the above error message.
To resolve this issue, Do the following,
Search for the key
---- MaxActiveReqForOneUser in RSREPORTSERVER.config file under the ReportServer directory,
And set the value to 400 that would look like this
<Add Key =" MaxActiveReqForOneUser" Value="400"/>
Save the rsreportserver.config file and restart the IIS
<Explanation>
Specifies the maximum number of simultaneous and in-progress connections that a single user can open to Report Manager. Once the limit is reached, further connection requests from the user are denied. This configuration setting is intended to mitigate a denial of service attack.
Valid values are 0 to a maximum integer. 0 indicates no limit on the number of connections. The default is 20.
Note that most requests process very quickly so it is unlikely that a single user will have more than 20 open connections at any given time. If users are opening more than 15 process-intensive reports at the same time, you might need to increase this value.
</Explanation>
ActiveX Data Objects (ADO) was originally designed as a simple and relatively lightweight COM based database API for use with Active Server Pages (ASP) on Internet Information Server (IIS). ADO was designed from the ground up to be thread-safe, highly memory efficient, and easily remotable (which is highly desired when using any API with IIS). Also, ADO is the first high level API from Microsoft that supports the new OLE DB core API (OLE DB is Microsoft’s new COM based database core API which is destined to eventually replace the ODBC API). The Microsoft Data Access Group felt that it was a better idea to create a total new API rather than try to extend the existing DAO and RDO API’s to support OLE DB and the various other “IIS Friendly” characteristics. DAO and RDO were written when all of the database clients were single threaded monolithic applications – hence they were not written from the ground up to be thread safe. Microsoft will continue to support DAO and RDO but no new functionality is planned. The long term plan is to get our customers to migrate to ADO and to focus our efforts on making ADO the high level API of choice for all database clients.
The ADO object model is very simple (3 major objects and 4 minor objects) and fairly simple and intuitive compared to Microsoft’s previous database object models:
Connection - Maintains connection information with the data provider. Includes methods for conducting database transactions (BeginTrans, CommitTrans, RollbackTrans). Includes an Execute method for executing SQL Data Manipulation Language (DML) and Data Definition Language (DDL) statements that do not require parameters. Includes an OpenSchema method for querying a database system for schema (sometimes called meta) information such as lists of system tables, indexes on tables, etc…
Command - Maintains information about a command, such as a query string, parameter definitions, etc. You can execute a command string on a Connection object or a query string as part of opening a Recordset object, without defining a Command object. The Command object is useful where you want to define query parameters, or execute a stored procedure that returns output parameters. Basically, the Command object is useful for preparing a SQL statement and providing parameters to the statement if needed.
Recordset - A set of records returned from a query and a cursor into those records. You can open a Recordset (i.e., execute a query) without explicitly opening a Connection object. However, if you do first create a Connection object, you can open multiple Recordset objects on the same connection. The ADO Recordset is very similar to DAO’s Recordset and RDO’s rdoResultset object. The ADO Recordset has the additional advantage of allowing complete disconnection from the back-end database system (this is called a “disconnected” Recordset) as well as built-in cross-process and cross-machine remotability.
Field - Contains information about a single column of data within a Recordset. The Recordset object features a Fields collection to contain all of its Field objects. With the Field object you can gather information about a column in a table, such as the data-type of the column, nullability, and the amount of data it can hold (maximum characters for example). The ADO Field object is similar to both DAO’s and RDO’s Field/rdoField objects.
Parameter - A single parameter for a parameterized Command. The Command object features a Parameters collection to contain all of its Parameter objects. ADO’s Parameter’s collection also has a handy “auto-populate” feature which makes it much easier to use with SQL Server stored procedures and for parameterized SQL statements if the driver is sophisticated enough to provide parameter meta-information (SQL Server and Oracle Drivers are good in this respect -- the Microsoft Access ODBC Driver is not as good).
Error - Contains extended error information about an error condition raised by the provider. Since a single statement can generate two or more errors, the Errors collection can contain more than one Error object at a time, all of which result from the same incident. Similar to Error/rdoError in DAO/RDO.
Property - A provider-defined characteristic of an ADO object. Every ADO object contains a properties collection which can be iterated programmatically.
To connect to a database in ADO, you can use one of two general methods. The first method is to use the Connection.Open method. The resulting opened connection object can be used directly or passed to other ADO objects.
Note that ADO provides access to both OLE DB providers and ODBC drivers. ADO does this by using a special OLE DB provider that translates ADO’s OLE DB calls to correspondingly equivalent ODBC calls. This translator provider is code named “Kangera”and it’s provider name is “MSDASQL”. The MSDASQL provider is the default provider for ADO. In other words, if you do not explicitly specify a provider, then the MSDASQL provider is used automatically. This makes using ADO with ODBC drivers very straightforward. If the customer understands ODBC connection strings, then connecting to an ODBC driver using ADO will be very simple. Just pass the ODBC connection string as the first parameter to the Connection.Open method:
Sub ADO_ODBC_CONNECTION_TEST()
Dim conn As New ADODB.Connection
conn.Open "DSN=LocalServer;DATABASE=pubs;UID=sa;PWD=;"
If conn.State = adStateOpen Then
Debug.Print "Connection successfully opened."
Else
Debug.Print "Connection failed."
End If
End Sub
If the customer wants to use an OLE DB provider, then the specific provider name must be set to over-ride the MSDASQL default provider. This can be set individually (by using the Provider property of the Connection object) or by adding a PROVIDER= statement to the OLE DB connection string as below:
Sub ADO_OLEDB_CONNECTION_TEST()
Dim conn As New ADODB.Connection
conn.Provider = "SQLOLEDB"
conn.Open "SERVER=UKDUDE;DATABASE=Pubs;UID=sa;PWD=;"
If conn.State = adStateOpen Then
Debug.Print "Connection successfully opened."
Else
Debug.Print "Connection failed."
End If
End Sub
Once the Connection object is open, you can then pass the connection to an ADO Command or Recordset object by setting the next object’s ActiveConnection property to the connection object:
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
‘ Open connection ...
Set rs.ActiveConnection = conn
Please note the use of the “Set” keyword here which is required when assigning object type variables in VBA.
ADO also has a short-hand method of opening a database connection that bypasses the connection object altogether. Both the recordset and the command object allow you to pass in a connection string instead of a connection object to their respective ActiveConnection properties. Just set the Command/Recordset’s ActiveConnection property to the desired connection string, and the object is ready to use.
Sub ADO_COMMAND_CONNECTION_TEST()
Dim cmd As New ADODB.Command
Dim rs As ADODB.recordset
Dim strConn As String
cmd.ActiveConnection = " DRIVER={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "byroyalty"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 25
Set rs = cmd.Execute
' Recordset now has authors with 25% royalty.....
End Sub
Note that here I did not use the “Set” keyword as in the previous example. I am assigning a string to a property which is not the same as assigning a VBA object.
Note that this second method is actually a more desired approach than explicitly using a connection object in code. In general, most ODBC drivers do not support more than one active statement per connection. This means that sharing a single Connection object over more than one Command/Recordset object can cause errors due to multiple active statements running. If the customer uses the connection string approach, then each Command/Recordset object will have it’s own individual connection object internally and “multiple active statements per connection” errors will be avoided.
Many customers are confused by ODBC and OLE DB connection strings. One of the most helpful articles I found to explain various connection strings is the “Setting Connection String Parameters in DAO” whitepaper.
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_MicrosoftOfficeDeveloperForumConnectionStringParametersinMicrosoftAccess.asp
Here are some simple examples to connect to various ODBC drivers and OLE DB Providers.
Microsoft Access:
ODBC = “DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\NW.MDB”
OLE DB = “PROVIDER=Microsoft.JET.OLEDB.3.51;DATA SOURCE= C:\NW.MDB”
Microsoft SQL Server:
ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
OLE DB = PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
Microsoft Oracle
ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
OLEDB = “PROVIDER=MSDAORA;SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"
Microsoft Excel
ODBC= "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Book1.xls"
Microsoft FoxPro
ODBC=”DRIVER={Microsoft FoxPro Driver (*.dbf)};DBQ=C:\FoxFiles;”
The ADO Command object is used when one needs to provide parameter information to a stored procedure, a SQL DML statement, or a SQL select statement. Parameters are indicated by embedding a single question mark in the location when the parameter is desired. Parameters can only replace literal values in a SQL statement, they cannot be used to indicate a variable field names for example.
Here are some example SQL statements that use parameters:
select * from authors where au_id=’243-11-2334’
select * from authors where au_id=?
insert into MyTable (field1,field2) values (1,’hello’)
insert into MyTable (field1,field2) values (?,?)
{call MyStoredProcedure(‘la’,’dee’,’dah’)}
{call MyStoredProcedure(?,?,?)
Parameter markers are simply replaced from left to right as encountered. The first parameter encountered is parameter 0 (parameter’s collection is zero based).
ADO provides an “auto-populate” feature for parameters which is quite handy. If the driver supports it, you can call ADO’s Parameters.Refresh method and this will automatically build the parameters collection for you (rather than manually adding each parameter one at a time programmatically).
Sub ADO_PARAM_TEST()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
cmd.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "select * from authors where au_id=?"
cmd.CommandType = adCmdText
cmd.Parameters.Refresh ‘ Auto-populate here…
cmd.Parameters(0).Value = "213-46-8915"
Set rs = cmd.Execute
' Read record here...
End Sub
For more information on manually populating the parameters collection, see the online examples in the ADO documentation. Unfortunately, the Microsoft Access ODBC and OLE DB drivers do not currently support automatic parameter population. This has been submitted as a feature request for future versions of the driver.
When calling a stored procedures using the Command object, set the Command’s CommandText to just the name of the stored procedure, then set the CommandType property to the adCmdStoredProc constant to let ADO now that the SQL statement in the CommandText property is a stored procedure.
Sub ADO_STORED_PROC_TEST()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.recordset
cmd.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
cmd.CommandText = "byroyalty"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
' Skip parameter 0 which is the return value!
cmd.Parameters(1).Value = 25
rs.Open cmd, , adOpenStatic, adLockOptimistic, -1
End Sub
You can determine which parameters are bound by running the code example in the following KB article against your particular stored procedure:
HOWTO: Determine How ADO Will Bind Parameters
http://support.microsoft.com/kb/q181199/
The ADO Recordset object is very similar to the Recordset objects in Microsoft’s previous database API’s. The Recordset has the concept of a “current record” or “record pointer” which points to the currently selected record. The programmer can move the current record pointer forwards and backwards by using the MoveNext and MovePrevious methods. In most cases the developer simply loops through a set of records until the Recordset EOF flag is set to True:
Dim rs As New ADODB.Recordset
rs.ActiveConnection = "Driver={SQL Server};" & _
"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"
rs.Open “select * from authors”
While Not rs.EOF
‘ Process record here…
Rs.MoveNext
Wend
The developer can also use the Fields collection to dynamically gather more information about the various columns in the recordset. The Fields collection of the Recordset object allows the “For Each” VBA collection syntax which makes coding quite efficient:
Dim rs As New ADODB.Recordset
Dim f As ADODB.Field
‘ Open recordset...
While Not rs.EOF
For Each f In rs.Fields
‘ Display various field properties.
Debug.Print f.Name & “=” & f.Value
Next f
Rs.MoveNext
Wend
