March 2007 - Posts

Microsoft SQL Server Samples & Community Projects

Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects. The portal page for SQL Server on Codeplex catalogs samples included with Product Releases, samples from the community, and independent SQL Server Community projects.

Posted by sqldbatips | with no comments
Filed under: ,

A new release of Reporting Services Scripter is now available. This releases fixes all the outstanding issues that have been reported as well as adding new functionality to both the GUI and commandline versions (details below). As always, please use the feedback links in the tool or readme file to report any bugs or request new functionality.

Download Reporting Services Scripter 2.0.0.7 

Fixes in Release 2.0.0.7

  • Fix for null reference exception when scripting linked reports with the Include Subscriptions option enabled
  • Fix to generated batch file to ensure all errors are captured in the load log
  • Fix for off-by-one offset error in generated scripts for reports and resources
  • Fix for duplicate variable names in generated scripts for reports when both History and Execution use a shared schedule

New Functionality in Release 2.0.0.7

  • Added the ability to preserve Linked Report page size and margins in generated scripts
  • Added the ability to reverse engineer a Visual Studio Report Project from a Report Server
  • Added the ability to generate a Visual Studio Report Project shared datasource definition (.rds file) from a Report Server shared datasource
  • Added the ability to bulkload RDL (report) and RDS (datasource) files from a source filesystem folder to a Report Server folder using the commandline. This option automatically fixes up shared datasource references for loaded reports if the shared datasource is in the same folder as the reports.

 

If you're not already aware, you can submit bug reports and suggestions for product improvements on the SQL Server Connect Website. You can also vote on and validate other users bugs and suggestions. One thing I've noticed is that a lot of people are creating feedback items but not adding their own vote! When you submit feedback, make sure you vote on it yourself to indicate it's importance to you.
Posted by sqldbatips | with no comments
Filed under:

An updated version of Reporting Services Linked Report Generator is now available (1.0.0.7). This release adds a fix for a NullReferenceException when enumerating a Report Server catalog. Thanks to those users who helped out in testing this fix as I have never been able to reproduce this error until now and it seems to have only affected a very small subset of users. The actual error was due to a case sensitivity issue with CatalogItem paths not matching the case of their parent folders. I'm still not entirely sure how this situation can arise within a ReportServer as I was only able to reproduce the issue by directly updating the Catalog table in the ReportServer database.

Download Reporting Services Linked Report Generator

 

Posted by sqldbatips | with no comments
Windows Server SP2 has been released. The download page is here. There is a performance improvement for SQL Server 2005 around the winsock API but it's most noticeable in higher end (>8CPU + multi NIC) hardware running workloads that show a high kernel time where at least some of it can be attributed to network traffic.
Posted by sqldbatips | 1 comment(s)

An updated version of Reporting Services Linked Report Generator is now available (1.0.0.4). This release adds the ability to keep server side report parameters for existing linked reports. This is useful in situations where you hard code or hide certain report parameters depending on the folder it's in. Previously these customisations would revert back to the source report when refreshing an existing linked report. A new option allows the preservation of existing server side parameters when refreshing a linked report

Get the latest version here

 

Posted by sqldbatips | with no comments

As I've previously blogged about, SQL 2005 SP2 allows users to write custom reports that are hosted in SSMS and are available via the Reports context menu in Object Explorer. Microsoft have now released a group of reports that can be used to troubleshoot performance issues.

Download SQL Server 2005 Performance Dashboard Reports

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:

  • CPU bottlenecks (and what queries are consuming the most CPU)
  • IO bottlenecks (and what queries are performing the most IO)
  • Index recommendations generated by the query optimizer (missing indexes)
  • Blocking
  • Latch contention

** Update: A hotfix is now available to address this issue here for SQL versions 3042 through 3053**

** Update: A hotfix is now available to address this issue here for SQL versions 3150 through 3158**

Tony has picked up on another issue with SP2 in regard to maintenance plans in that integrity checks are only ever checking the master database. I've done some checking on an RTM and SP1 server and this bug wasn't present so it's definately a regression. Note that this could lead to undetected database corruption if you rely on Maintenance Plans and SSIS Check Database Integrity Tasks. This is not fixed in the SP2 Hotfix I blogged about earlier. The workaround is to uncheck the Include Indexes checkbox in the Check Database Integrity Task however that of course means that you won't get the same level of checks for nonclustered indexes on user tables. My advice - check your database integrity yourself using a simple TSQL job - don't rely on your maintenance plans if you're on SP2!

As a response to the bug introduced in SP2 with regard to maintenance plan cleanup tasks (KB 933508) Microsoft have released an updated build of SQL Server 2005 SP2 which replaces the originally released service pack. The updated release is available here (Note the Date Published has changed). Below is the statement regarding the issue which includes the link to the hotfix for customers who had already installed SP2 using the original release. KB 933508 contains details of how to check whether you are affected by this issue if you are unsure of when exactly you downloaded SP2.

An issue was recently discovered with SQL Server 2005 SP2 which might impact customers using cleanup tasks in Maintenance Plans and SQL Server Integration Services packages. The issue is described in detail at http://support.microsoft.com/kb/933508. We have created a General
Distribution Release (GDR) #50000939 to address this issue which is available for download and we have included the GDR in a refresh of the SP2
installation package.

Customers who download SP2 starting Monday, March 5th, 2007 will not need to need any further action since the refreshed SP2 installation package includes the necessary GDR.

We recommend that everyone who has downloaded and installed SP2 prior to March 5th, 2007 apply the GDR either from Microsoft Update or by downloading it directly from http://go.microsoft.com/fwlink/?LinkId=85124. As described in the KB article (http://support.microsoft.com/kb/933508), this is primarily for customers who use cleanup tasks in maintenance plans and SQL Server Integration Services packages.

Maintenance Plan Changes in SQL Server 2005 SP2 Users of SQL Server 2005 maintenance plans and SQL Server Integration Services should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.

Who is affected?
You are affected if you use cleanup tasks in Maintenance Plans and SQL Server Integration Services packages, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.

What is the issue?
When SQL Server 2005 was released, cleanup intervals were measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans. These include an enhancement that lets users specify the cleanup interval in hours.The change in intervals caused the initial release of SQL Server 2005 SP2 to misinterpret existing cleanup task intervals in maintenance plans and Integration Services packages. This can lead to the files being cleaned up earlier than intended. The issue is described in detail at http://support.microsoft.com/kb/933508.

What if I have already installed SP2 on my server?
If you downloaded SQL Server 2005 SP2 before Monday, March 5th, 2007, install a general distribution release (GDR) update for those SQL Server 2005 SP2 installations. The GDR is available from the Microsoft Download Center at http://go.microsoft.com/fwlink/?LinkId=85124

After you install this GDR, you might have to restart your computer.

This GDR is also available through Microsoft Update, and will be applied automatically if you are using automatic updates. However, if you are using failover clustering, you must download and apply the GDR manually. Microsoft Update will not apply the GDR to failover cluster nodes.

What if I haven't yet installed SP2 on my server?
SQL Server 2005 RTM and SQL Server 2005 SP1 do not have this issue, and the current SQL Server 2005 SP2 download is updated to resolve this issue. If you have not installed the initial release of SP2, you can download the current release and install it.

Note: If you try to apply the SP2 refresh over a previous SP2 installation, no files are updated. Instead, use the general distribution release (GDR) update below.

Posted by sqldbatips | 2 comment(s)
Filed under: ,