Access Report Viewer DLL Files for your Application

If an application requires the use of Report Viewer Redistributable and needs access to specific .dlls for the application to run, you’ll find this post helpful, http://drowningintechnicaldebt.com/blogs/dennisbottjer/archive/2006/10/16/Hacking-Report-Viewer-Redistributable.aspx.

It explains how to extract the necessary .dll files so you can reference them in your application and avoid GAC security errors (e.g., Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The module was expected to contain an assembly manifest.)

I will outline the steps below to avoid another click. ;) I have also added links for the most recent versions of Report Viewer.

1. Download Report Viewer Redistributable
   1. Report Viewer 2005 - http://www.microsoft.com/downloads/details.aspx?familyid=8a166cac-758d-45c8-b637-dd7726e61367&displaylang=en
   2. Report Viewer 2008 - http://www.microsoft.com/downloads/details.aspx?familyid=CC96C246-61E5-4D9E-BB5F-416D75A1B9EF&displaylang=en
   3. Report Viewer 2008 SP1 - http://www.microsoft.com/downloads/details.aspx?familyid=BB196D5D-76C2-4A0E-9458-267D22B6AAC6&displaylang=en
2. Use favorite Zip Utility to extract the MSI.exe to a folder of your choice
3. Find the file ReportV1.cab in extract folder from step #2
4. Use favorite Zip Utility to extract ReportV1.cab to a folder of your choice
5. Open the new folder from step 4 and find 4 files
6. Rename: FL_Microsoft_ReportViewer_Common_dll_117718_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.Common.dll
7. Rename: FL_Microsoft_ReportViewer_ProcessingObject_125592_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.ProcessingObjectModel.dll
8. Rename: FL_Microsoft_ReportViewer_WebForms_dll_117720_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.WebForms.dll
9. Rename: FL_Microsoft_ReportViewer_WinForms_dll_117722_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.WinForms.dll
10. Copy these dlls to your smart client project and reference them
11. Now they will be part of the Smart Client's Build Output and Click Once Deployment

I hope this makes someone’s life a little easier!

SQL Server 2008: Truncate Log While Maintaining the Database Recovery Model

How important is it to understand SQL Server Wait Statistics??

How important is it to understand SQL Server Wait Statistics??

To be clear, it is very important to understand wait statistics.  It is a key part of tracking down application and server related performance issues.  If your application is having a performance issues or your server resource usage is high, this is will prove to be an important part of troubleshooting and resolving the issue.

About Wait Statistics

Sys.dm_os_wait_stats is a DMV that allows DBAs to track and troubleshoot requests that could not execute immediately.  When a request comes into SQL Server that can't immediately execute, the system puts it into a wait state. The SQL Server engine internally tracks the time spent waiting, totals it, and retains it in memory.  The sys.dm_os_wait_stats DMV displays information on waits that have completed. This DMV does not show current waits. 

View definition (Column names and descriptions)

wait_type - Name of the wait type.

waiting_tasks_count - Number of waits on this wait type. This counter is incremented at the start of each wait.

wait_time_ms - Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.

max_wait_time_ms - Maximum wait time on this wait type.

signal_wait_time_ms - Difference between the time the waiting thread was signaled and when
it started running.

The resultset of the Sys.dm_os_wait_stats DMV is the values of the wait statistics that signifies the total waits for all processes that have finished since the counters were last reset. SQL Server resets the counters to 0 each time the SQL Server service is restarted. The command below will allow a manual reset of the values.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

Each time the DMV is queried, a snapshot at that point in time is the resultset, which includes the total waits on the SQL server instance.

Understanding the Columns

The waiting_tasks_count column is an indication of how many times the processes have waited for that particular wait type.

In general, the most important column is the wait_time_ms column.  It is an indication how many total milliseconds passed for each wait type. The longer the wait time, the less efficient the process becomes.

The max_wait_time_ms is self explanatory and gives more insight into the issue. It can help measure the severity of a particular wait type.

Another aspect of waits that is equally important is signal_wait_time.  The time signal_wait_time is the elapse time from the time the resource is available to the actual use of the resource.  Further, it is the time it took for the thread to be loaded from the queue onto the processor for processing.  Note that it is also included in the overall wait time, which appears in the DMV's wait_time_ms column.  A lot of signal waits indicate excessive CPU pressure, while fewer signal waits put the bottleneck on a specific wait type.

Find Bottlenecks

Run the DMV periodically to view the statistics.  It is best to order the report in descending order of most to least waits.  That will allow a quick view as to where the largest bottlenecks are and where to investigate problems to determine how to minimize waits.

Additional information on how to read the wait statistics:

Specific types of wait times while a query is executing can indicate bottlenecks or stall points within the query. Also, high wait times and/or wait counts on the server as a whole can indicate bottlenecks or hot spots in within query interactions on the server.  For instance, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.

Other relevant links:

Information on Preemptive wait types:
http://sqlblogcasts.com/blogs/christian/archive/2008/06/06/new-sql-server-2008-wait-types-preemptive-and-ft.aspx

References:

http://www.sqlmag.com/Article/ArticleID/96746/sql_server_96746.html
http://msdn.microsoft.com/en-us/library/ms179984.aspx

SQL Server 2008 Web Edition?

As always, Microsoft always keeps us on our toes...  With the release of SQL Server 2008, Microsoft has released a new edition of SQL Server: SQL Server 2008 Web Edition. 

It is specifically designed for web hosting providers and has more robust capabilities than previous versions of SQL Server.  SQL Server 2008 Web Edition provides a low-cost option to support large-scale, highly available Web applications or hosting solutions to customers.

SQL Server 2008 Web Edition comes with:

• Four-CPU support
• No limits on memory
• No limits on database size

Log shipping increases database availability by automatically backing up, copying, and restoring transaction logs on standby servers.

SQL Server 2008 Web supports the new improved SQL Server Driver for PHP for PHP applications deployed on the Windows platform.

SQL Server 2008 Web Edition also includes new tools for ease deployment and administration:

• A database publishing tool
• Reduced administration time with Policy-Based Management and performance data collection tools

Availability gotchas!!

SQL Server 2008 Web Edition is available in only two Microsoft licensing channels:

• Select licensing program and Services Provider License Agreement (SPLA).
• Client access licenses (CALs) are not applicable to SQL Server 2008 Web.

SQL Server 2008 Web can only be used to host and support public and internet-accessible web pages, sites, applications, and services.

SQL Server 2008 Web may not be used to support business applications that are not accessed directly from the internet.  If additional capabilities are needed, consider using SQL Server 2008 Standard or SQL Server 2008 Enterprise which includes all features available in SQL Server 2008 Web Edition.

Feature list gotchas!!

Microsoft has posted a list of supported features per SQL Server 2008 edition listed here, http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx.  Be sure to review it carefully as there are quite a few features removed from this installation.

ORCS Web Supports SQL Server 2008 for Hosting Clients on Day One

CHARLOTTE, NC - August 6, 2008 – ORCS Web is pleased to announce it is supporting Microsoft’s SQL Server 2008 on July 31, the first day of its release. This is possible through ORCS Web’s close working relationship with Microsoft, which has enabled extensive testing by the ORCS Web technical team since 2007, including beta-level client sites for several months.

ORCS Web’s web hosting customers who are currently running SQL Server 2005 can be upgraded free of charge, with the transition managed through cooperation with individual site administrators to ensure a smooth migration. All new servers will support SQL Server 2008. Existing ORCS Web customers should email webteam@orcsweb.com to request a new SQL Server 2008 database or a copy of their existing database running SQL Server 2008. New customers should contact sales@orcsweb.com or visit the company’s website at http://www.orcsweb.com for information on the range of web hosting solutions that will best meet their needs.

SQL Server 2008, Microsoft's next-generation database software, delivers a platform that allows users to build an application with increased performance, availability, and security while providing many new tools to manage the server and its resources. 

About ORCS Web
ORCS Web is a Charlotte-based web hosting firm that has provided managed hosting solutions since 1996 for clients in more than 70 countries who develop and deploy their applications on Microsoft Windows platforms. Services include shared hosting, dedicated hosting, and webfarm hosting, with specialty in .Net, SQL Server, and architecting highly scalable solutions. ORCS Web’s dedication to being Number One in customer service offers an Unrivaled Windows Web Hosting Experience. ORCS Web is a Gold Certified Microsoft Partner.

Enable Filestream Changes in SQL Server 2008 RC0

Hi,

SQL Server 2008 changes the way FILESTREAM is enabled.  I didn’t find the information as quickly as I would like have, so I wanted to post the information in case someone else runs into the same issue. 

There are two changes that are needed. 

The first change required, is within the SQL Server Service:

1. Open SQL Server Configuration Manager, click on SQL Server Services and then locate the SQL Server service in the right pane.

2. Right-click the service and then click Properties.

3. In the SQL Server Properties dialog box, click the FILESTREAM tab.

4. Select the Enable FILESTREAM for Transact-SQL access check box.

5. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

7. Click Apply.

The second change required, is to execute the code below within a New Query window.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Rendering a Report in Reporting Services using SOAP APIs

Hi,

I want to post information on rendering reports within your web application without having it prompt for a user name and password. 

Microsoft posts step-by-step instructions here, http://support.microsoft.com/?kbid=875447, so I won’t repost what is there, but I want to point out a couple gotchas that our clients have experienced. 

Gotcha #1:

Be sure to consume the correct web service.  And, don’t forget to use https:// if Reporting Services is configured to use a SSL Certificate.

SQL Server 2000 Reporting Services:
http://ReportServerName/ReportServer/ReportService.asmx

SQL Server 2005 Reporting Services:
http://ReportServerName/ReportServer/ReportExecution2005.asmx

Gotcha #2:

When passing credentials, if you are using a domain account, be sure to add it within the code behind page. 

        ReportExecutionService rs = new ReportExecutionService();
        rs.Credentials = new System.Net.NetworkCredential("user", "password", “domain”);

In the end your code behind page should look similar to this:

using com.domain.ssrs;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void RenderTest_Click(object sender, EventArgs e)
    {
        ReportExecutionService rs = new ReportExecutionService();
        rs.Credentials = new System.Net.NetworkCredential("user", "password", “domain”);

        // Render arguments
        byte[] result = null;
        string reportPath = "/My Reports/test/test";
        string format = "PDF";
        string historyID = null;

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

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

        rs.ExecutionHeaderValue = execHeader;

        execInfo = rs.LoadReport(reportPath, historyID);

        String SessionId = rs.ExecutionHeaderValue.ExecutionID;

        result = rs.Render(format, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

        Response.ClearContent();
        Response.AppendHeader("content-length", result.Length.ToString());
        Response.ContentType = "application/pdf";
        Response.BinaryWrite(result);
        Response.Flush();
        Response.Close();

    }
}

The server principal 'A' is not able to access the database 'B' under the current security context (Microsoft SQL Server, Error:916)

Hi All!

I ran into an issue that I could not resolve for two days and, with the help of MS, it is finally resolved. It was enough of a thorn in my side that I thought I should post it online in hopes that it will help some resolve it a lot faster.

The error:

The server principal 'A' is not able to access the database 'B' under the current security context (Microsoft SQL Server, Error:916)

The issue:

This issue occurs with SQL Server 2008 RC0 tools.  Using SQL authentication, I could connect to database engine but I could not expand the ‘Databases’ node.  It would result in the error above.  The issue is with SQL Server Management Studio itself, not the database engine or any other aspect of SQL Server.  I could connect to the SQL installation from another server running SQL Server 2008 RC0, using SQL authentication, and expand the ‘Databases’ node without any errors.  But I couldn’t connect locally using SQL authentication.

In a nutshell:

Server A – While logged in locally on Server A, I could not connect and expand the ‘Databases’ node successfully using SQL authentication.

Server B – I could connect to the SQL instance on Server A and expand the ‘Databases’ node using SQL authentication successfully.

Based on my current understanding of the issue at hand, there are several work-a-rounds to correct this issue.  Hopefully, one of them will work for you.

Work around #1:

1. Within SQL Server Management Studio, view Object Explorer Details window by selecting View, Object Explorer Details in menu (or hitting F7)
2. Right-click on the column header and unselect 'Collation.'
3. Refresh the server in Object Explorer and enumerate the databases

Work around #2:

1. Within SQL Server Management Studio, view Object Explorer Details window by selecting View, Object Explorer Details in menu (or hitting F7)
2. In Object Explorer window click at Databases folder
3. In Object Explorer Details window right-click at the column header and select ‘Reset view’. 
4. Refresh Databases folder

Work around #3:

1. Check the database options for the “Auto Close” setting. If it is set to “True,” change it to “False” and test again.

Further explanation:

Collation for an offline database is returned as NULL by the engine, and SSMS currently interprets NULL values as a permissions issue.  The error will occur even if Object Explorer Details view is not open.  The same issue will occur if you try to enable a setting that you don't have permission to access on all databases, for example data space used.

Other helpful links:
• https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355238
• http://sqlblog.com/blogs/aaron_bertrand/archive/2008/07/07/a-little-management-studio-oops.aspx
• https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354322
• https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354291&wa=wsignin1.0

I hope this helps!!

Reporting Services 2005: How to create a model for Report Builder using Report Manager

Report Builder is a new tool built into SQL Server Reporting services 2005 that allows easy report building using Report Manager. Before you can use Report Builder to build reports, you must first create a report model for Report Builder to use. Report models can be created using Microsoft Office SharePoint Server 2007, Report Manager or SQL Server Management Studio. I will explain how to create a report model using Report Manager.

The first step is creating a data source to use for your report model. The second step is generating the report model. Once that is done you can use the report model to create reports.

Creating a data source:

1.    In your Web browser, type the URL for your report server in the address bar.

2.    Click “New Data Source.”

3.    In the “Name” box, enter a name for the data source.

4.    Optionally, enter a brief description of the mode in the “Description” text box.

5.    Verify that the “Enable this data source” check box is selected.

6.    In the Connection type list, select the data source type to which you want to connect. The connection type must be one of the following: Oracle, Microsoft SQL Server or Microsoft SQL Server Analysis Services.

7.    In the Connection string box, enter the connection string that points to the database.

8.    Select the connection method that Report Builder users will need to use to connect to the database.

9.    Windows Authentication: Select this option when you want the operating system to authenticate SQL Server users. This option allows SQL Server to use Windows security features, such as password encryption, to authenticate users. It is strongly recommended that you select this option.

10. SQL Server Authentication: Select this option when you want users to use a SQL Server login account that you created. Users must supply a valid SQL Server login name and password.

11. Click “OK.”

Creating a report model from the data source:

1.    In Report Manager, select the data source that you want to use for your model.

2.    The “Properties” page is displayed.

3.    Verify that you want to use the options specified for the data source.

4.    Click “Generate Model.”

5.    In the “Name” box, enter a name for the report model.

6.    In the “Description” box, enter a brief description of the model.

7.    To specify a new location to save the report model to, click “Change Location.”

8.    By default, the report model is saved to Report Manager Home.

9.    Click “OK.”

At this point you are ready to create a report using Report Builder. Click the “Report Builder” button on the toolbar within Report Manager. This will launch Report Builder and display report models that can be used to build the report. Once the report is completed, save it and then review it in Report Manager.

Exporting SSE database data to a remote SQL server

When ASP.Net v2.0, Visual Studio 2005, and Visual Web Developer were released to manufacturing, ORCS Web began supporting those technologies making them immediately available to our clients.  Visual Web Developer 2005 has a very cool feature that will allow you to dynamically create a SQL Server Express database for use when developing web applications, which is great for developers.  But, after the web application is developed, how is the database schema and data uploaded to your production database on a remote SQL Server? Dilemma, dilemma.  It turns out that there are a few options:

Update the web.config of your application to point to your remote SQL Server database and develop using it directly.

Send a copy of your .mdf/.ldf files to your web host and have them attached directly to your production SQL Server, replacing your existing database.

Develop on your local machine, and then use the Import/Export wizard of SQL Server Management Studio to copy the data to your remote SQL Server database. 

Option #1 is ok but I recommend that you don’t use a production database for development. You should have a separate database for development and production.  And, Option #2 will also work but, it doesn’t give the flexibility to manage your data directly.  Option #3 is a good solution for development and also provides the flexibility to export data to a remote SQL server when necessary.  Here, I will explain how to use option #3 to export database data from your local PC to a remote SQL Server database.

Before I begin, it is important to understand that the SQL Server 2005 Import Export Wizard discussed here only provides minimal transformation capabilities.  It will only allow for setting the name, the data type, and the data type properties of columns in new destination tables and files; SQL Server 2005 Import Export Wizard doesn’t support any column-level transformations. This means that any constraints, indexes, triggers, and primary/foreign keys will not be transferred to the remote database.  The best way to handle this situation is to generate a script of the database objects and then run it on the remote server before exporting the data.

Following the instructions below will allow data to be transferred from your local PC to a remote SQL server.

1.    Open SQL Server Management Studio to connect to the SQL Server Express Database Engine server and expand Databases.

2.    Right-click the database that has the data that needs transferred, point to Tasks, and then select Import Data or Export data.

3.    Choose a data source and a destination.

4.    Set the options for the type of destination that you selected.  If the destination is a SQL Server database you can specify the following:

1.    Choose whether to copy data from tables or views or to copy query results.

2.    Indicate whether the destination table is dropped and then recreated, and whether to enable identity inserts.

3.    Indicate whether to delete rows or append rows in an existing destination table. If the table does not exist, the SQL Server Import and Export Wizard will automatically create it.

5.    Optionally, change the mappings between source and destination columns, or change the metadata of destination columns:

1.    Map source columns to different destination columns.

2.    Change the data type in the destination column.

3.    Set the length of columns with character data types.

4.    Set the precision and scale of columns with numeric data types

5.    Specify whether the column may contain null values.

6.    On the Save and Execute Package screen, ensure Execute immediately is checked.  Optionally, you can save the package on your local SQL Server 2005 installation. 

7.    Click Finish to run the package.