Chaitanya's Blogs -- SQL Developer findings

Reporting Services Permissions Sample

The functionality of managing permissions via SQL Server Management Studio had been removed in Reporting Services 2008.

A new sample, RSPermissions which demonstrates how to write an application which manages Reporting Services permissions via the Reporting Services Web Service API is currently available http://msftrsprodsamples.codeplex.com/Wiki/View.aspx?title=SS2008%21RSPermissions%20Sample%20Application.

This had been included as a part of SQL Server 2008 (Katmai) Product Samples Refresh 3.

How to run 32-bit UDL file on a 64-bit Operating System

Once we create an UDL File and run it on 64 Bit OS, it will list down all the 64 bit OLE DB providers installed on the machine.

The reason behind for this is simple. When you double click on a UDL file on a 64 bit machine, it’ll enumerate only the 64 bit OLE DB Providers and if we have the 32 bit Ole DB providers installed, we will not be able to find that in the enumerated list.

Create a udl file with the name test.udl under the path, C:\. 

when we have created a UDL file on a 64 bit machine and try to open it,  the following 

"C:\Program Files\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile  C:\test.udl

Command will be called through C:\windows\system32\rundll32.exe.

Here both Oledb32.dll and rundll32.exe are 64 bit and will not enumerate the 32bit Dlls.

Then how to run the UDL which lists down the 32bit Dlls? 

We can find the 32bit version of Oledb32.dll under the path: C:\Program Files (x86)\Common Files\System\Ole DB

and 32 bit version of rundll32.exe under the path: C:\WINDOWS\SysWOW64.

We’ll need to  execute the command below from a command line or Start/Run :

C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\test.udl

Check the paths of rundll32.exe and oledb32.dll while running this command! 

Here you have your 32 bit OLE DB Providers.

Filtering Data Dynamically on a ReportViewer Control

Question:
How to display Dynamic Data in the ReportViewer Control.

Scenario:
Imagine having a ReportViewer Control on either Winforms or Webform.
We use a SQL Query to fetch the data and have a dataset returned as a result of executing the SQL Query on the Database.

We have this Dataset assinged to the ReportViewer Control. We have some filter criteria mentioned on the form and based on the filter criteria the data on the ReportViewer Control needs to be refreshed.

Solution:
Imagine having a button that generates the report. Assuming that all the datasets are previously populated with information, here is how I apply my filter and reassign my binding source.

Its not needed for us to touch the reportviewer control (other than to tell it to render) as it should already pointing to the binding source for its data.

Me.DataSet.DataTable.DefaultView.RowFilter = ""  // Mention the filter condition you would like to use to filter the Data
Me.DataTableReportBindingSource.DataSource = Me.DataSet.DataTable.DefaultView
Me.ReportViewer1.RefreshReport()

Useful Links:

http://msdn2.microsoft.com/en-us/library/ms252125(VS.80).aspx [How to: Filter Data in a ReportViewer Report] 

Conditional PageBreak With Rectangles in a Report

If we are trying to insert a page break after a Rectangle based on a Conditional Expression, that does not work. 

Work around:

You need to next two rectangles inside one another. 

Apply the conditional (apply the conditional expression) formatting to the outside rectangle.  Then apply the page break to the inner rectangle.
You have now separated the conditional formatting from the page break.

Since the inside rectangle is only visible when the conditional statement is true, it only applies the page break when it is visible.

This might be a single scenario, but we can apply it to many others reports where we wanted to Force a page break.

Japanese Characters are displayed as "?" when we export report to either PDF or TIFF format

Problem Description:

=======================================================================

-       We had a report that displays the Japanese characters. When we try to export the report to either PDF or TIFF format we don’t see these Japanese characters, instead they are replaced by the character “?”

Cause & Resolution:

=======================================================================

-       The Arial Unicode MS font allows the display of characters in most languages. This font contains glyphs for all code points within the Unicode Standard, version 2.1.

-       We will not have this font installed by default and would generally come with Microsoft Office. Else we will need to have it installed explicitly using an Office setup CD.

-       Once we have this installed on the Report Server machine, then we need to make sure we install files for East Asian Languages. We do this as per the following steps:

o    Go to Control Panel. Select “Regional and Language Options”

o    Go to Languages Tab, check “Install files for East Asian Languages”. Once installation is done we have to reboot the machine.

o    We have to use Arial Unicode MS instead of Arial

-       Sometimes even if we had followed the above steps, we may run into the issue mentioned in the problem description. Then follow the below stated steps:

o    Go to the IIS Manager and select Reports Virtual directory.

o    Go to the properties of the Report Manager, and Virtual Directory tab, Configuration button. Under Application extensions look for .axd, select and hit on edit.

o    In the .axd extension if we have “Verify if File Exists” is checked, then we have to remove it.

o    Once done, we can do an IIS Reset and check the behavior.

-       Also refer the following articles for reference:

o    http://support.microsoft.com/?id=287247 [Description of the Arial Unicode MS font in Word 2002]

o    http://msdn2.microsoft.com/en-us/library/ms159713.aspx [Designing for PDF Output]

o    http://msdn2.microsoft.com/en-us/library/2wawkw1c.aspx [Web Site File Types]

Report Manager is slow after an IISReset or AppPool restart

Report Manager and Report server may take time for the first time after we do an
IIS Reset or a recycle of the application pool.

For the first time Report Manager might take a few seconds, and that is expected. But if it takes either 2 or 3 minutes then it might be abnormal. 

Even if the SSL is not enabled, Reporting services is looking for the "Certificate
Revocation List Information" for the first time after an IIS Reset or Recycle of
the Application Pool.


CRL are published on a timer. For example the default CRL publish period for a MS
CA is once a week.

When a client makes a secured connection to a server it will check the servers
certificate by downloading the CRL from the CA that issued the cert and checking to
see if that certificate has been revoked. The client will use the cached version
till the cached version expires. It will download the CRL again the next time it
needs to verify a certificate.

If you download the CRL and place it on the client it will only be good for as long
as the publish period for the CRL so it would not be a permanent fix. I checked the
CRL and seems that right now we are publishing the CRL one every 6 months. So I
guess that is not exactly horrible...

Ideally the machine running the application will have access to the Internet and be
allowed to access crl.microsoft.com/*.

Steps to manually install the CRL:

1. From any machine that has access to the internet browse to
<http://crl.microsoft.com/pki/crl/products/CodeSignPCA.crl>

2. Save the CRL file and copy it to the machine that will launch the SQL reports

3. On the machine that will launch the SQL reports application run this command

certutil -addstore CA CodeSignPCA.crl

The customer needs to be aware that once every six months they will need to update
the CRL. The CRL publishing period may change in the future.

Report Builder not Visible

There are scenarios where we do not see the Report Builder visible while trying to
browse the Report Manager.

This can be on the same domain or from a different domain.
If we are accessing from a different domain, (Both the domains are trusted among
each other), everything looks good but only Report Builder is not visible.

Or some times Report Builder is only visible to the Administrators but not to others.

RESOLUTION:
=========================

FOR A SPECIFIC USER--
-------------------------------------
- Logged onto the SQL Reporting Services machine as Admin and browsed to the
Reports Manager page
- Selected the properties tab and added the domain user that needed the access
- We gave the user Report Builder rights
- We then select Site Settings from the top and then selected "System level role
definitions"
- We then created a new Role and assigned the task "Execute Report Definitions" (As

this is the only task required to display the Report Builder button for the
users)
- After creating the rule we then went back to the site settings page and selected

"Configure site-wide security"
- We then added the domain user account that need access and assigned the group we

created in the last step.


FOR A GROUP OF USERS--
------------------------------------------
1. Add the non-Administrative users to the "System Users" group by following these

steps:
a. Open Report Manager as a user that is in the Local Administrators group on
the Reporting Services server.
b. You can open Report Manager
c. Click "Site Settings" in the upper right hand corner.
d. Click "Site Wide Security Settings" near the bottom of the page.
e. Click "New Role Assignment"
f. Type in "Users" in the "Group or User name" field if you would like to add
the local "Users" group.
This group includes "Authenticated Users" which means anyone authenticated on
the domain/local machine will be in this group.

Or if we are facing issues with users from other domain. You may specify any group
you would like for step f. This was just an
example.

h. Check the box next to "System User"
i. Click "Ok"



ARTICLES TO BE REFERRED:
=========================
-- http://msdn2.microsoft.com/en-us/library/ms159667.aspx [Role Assignments for
Report Builder Access]
-- http://msdn2.microsoft.com/en-us/library/ms403427.aspx [Updating Role-Based
Security for Report Builder Access]