Matticus Blog

One step closer..... SQL 2012 RC0 is released

2011-11-18T16:34:00.002+10:00

Well today we are one step closer to the release of SQL 2012 (aka Denali) with the release of RC0.... actually it was yesterday but I was burried in opperational support yesterday. Anyway this is very exciting to me as there are so many features that offer great new capabilities.

Here's some links to what others are writing about this because why should I re-invent the information:

http://blogs.technet.com/b/sqlman/archive/2011/11/18/sql-server-2012-rc0-available-now.aspx

http://blogs.msdn.com/b/analysisservices/archive/2011/11/17/announcing-microsoft-sql-server-2012-release-candidate-0-rc0.aspx

http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx

You can get the RC0 download at http://www.microsoft.com/download/en/details.aspx?id=28145

Enjoy!

Recovering/Restoring the Resource system database in SQL Server 2005

2011-08-01T14:48:00.005+10:00

Here is the scenario I have been working through in the LAB today:

The Resource database (mssqlsystemresource) has become corrupt on a SQL 2005 SP4 instance. You are not sure why the database has become corrupt but when you try and start the SQL Server DB Engine the following is reported in the SQL Server ERRORLOG:

2011-08-01 14:57:23.89 spid5s Starting up database 'mssqlsystemresource'.
2011-08-01 14:57:23.91 spid5s Error: 5172, Severity: 16, State: 15.
2011-08-01 14:57:23.91 spid5s The header for file '\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf' is not a valid database file header. The PageAudit property is incorrect.
2011-08-01 14:57:23.95 spid5s Error: 945, Severity: 14, State: 2.
2011-08-01 14:57:23.95 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-08-01 14:57:23.97 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

We know that we cannot backup/restore the Resource database using SQL Server (as per http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx) and you could restore the physical file from the server's file system backups. Unfortunately though the file is unable to be recovered from the server's file system backup for an unknown and unrelated external factor.

Never fear there is a simple fix to this. Reinstall the Service Pack as that will recreate the Resource database.... Hurray

Here's the steps and things to note when performing the Service Pack install though:
1. Launch the Service Pack install
2. Accept the terms and conditions and click Next
3. When asked to select the Feature to update you will notice that the Database Services for the required instance will already report "Upgraded". Simply select the instance though as this will re-apply the service pack.
4. Complete the service pack install

Once the Service Pack has been installed you should be able to start the SQL Server Service for the required Instance.

TIPS: You can use the following TSQL to grab information about your SQL Instance and Resource Database. These were taken from http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx

SELECT @@VERSION
SELECT SERVERPROPERTY('ResourceVersion');
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

SQL 2008 / SQL 2008 R2
For the process to rebuild the Resource database in SQL 2008 follow the instructions at http://msdn.microsoft.com/en-us/library/dd207003.aspx

Happy Disaster Recovering everyone :)

Search Stored Procedure Code

2011-07-25T17:40:00.002+10:00

Came across an interesting situation. The database that I was working with was missing from CVS, and I needed to be able to check what Stored Procedures were using Temporary tables. Thankfully I found a blog by one of my favorite authors which saved the day.

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

TSQL to list all Permissions within a Database

2011-06-09T16:50:00.002+10:00

Came across a situation today while trouble shooting a problem with replication where the subscription database has continually been getting modified by an unknown source and thus breaking replication. To assist with investigating this issue there was a need to look at all the permissions assigned to all users and roles within the database both at the Publisher and the Subscriber..... so here is the TSQL I wrote to help with this.

select sys.database_permissions.class_desc
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, sys.database_principals.name as [grantee_principal_name]
, ISNULL(sys.schemas.name, '') as [schema_name]
, ISNULL(AllObjects.name, '') as [object_name]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'GRANT ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'GRANT ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Grant_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'DENY ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'DENY ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Deny_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'REVOKE ' + sys.database_permissions.permission_name + ' FROM [' + sys.database_principals.name + '];'
ELSE 'REVOKE ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] FROM [' + sys.database_principals.name + '];'
END) as [Revoke_Perms_Stmnt]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
--AND sys.database_principals.name = 'public' --uncomment this line to restrict the output to a single principal
LEFT JOIN (
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.objects
UNION
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
ORDER BY sys.database_principals.name
, sys.database_permissions.class
, sys.schemas.name
, AllObjects.name

The trend of Human Language verse Storage Size

2010-11-09T12:06:00.000+10:00

I watched The Social Network movie the other night and after being reminded of the pre-Facebook day's of LiveJournal and other blog sites it got me thinking about the changes in the digital world. I am sure there have been plenty of other people write about this topic but I had quite a simple thought, the human language continues to become a "hybrid" language, yet the amount of storage we require is growing at an unprecedented amount.

If you think about the "paper" days, the time before every household had a family computer, and more importantly every kid had a laptop or smart phone. Most written communication between friends was done through letters. Be it between too long time friends sending each other letters to update each other on how the family is or a couple of students writing letters to each other to exchange between classes. It was pen ink on paper and usually uncommon for the letter to be read by someone it was not intended for, at least without the recipient actually handing it to the additional person(s). Of cause occasionally a letter would be read by more than the intended audience, typically in a school environment, either by accident or theft.

Then enter the age of blogs, all of a sudden people started being able to express their thoughts online (much like I am doing here), and while they generally only gave their friends the URL so they knew the intended audience, it was the start of the public information era. Blogs, at least in the early days, still represented much of the letter writing styles with an intended addressee/recipient.

Then the perfect timing of Facebook, in an age when every household and school has a computer with most having >2, and every kid having some sort of smart phone. The blog was a thing of the past, except at least for technical or news articles, and enter the age of the status updates. Here we saw people replace large rants with a few sentences and be able to express that opinion to a large group of people without much thought of the impact (other than wanting to be noticed).

And in a time when people quickly became use to the concept of status updates, people just as quickly became use to the concept of the Tweet. Now this is where the point to my thoughts were. With the 140 character limit on a tweet people quickly started adapting from the already established online chat languages to develop a whole new form of language using even less characters to say more (e.g. m8 = mate).

At the same time as this was happening a massive change in the Computer Hardware world was occurring, Computers were getting much faster and the cost of Storage was dropping dramatically. Making it much more possible for the common home computer to have >300Gb in storage (and these days ~500Gb - 1Tb).

So in an age where online communication has become it's own language, with shorter and shorter terms replacing English words all the time, and in an I.T. industry with very cheep storage costs. There is a definite trend that more and more Storage is being requested and historical data being kept for much longer, yet the size of text on individual records being stored is becoming less and less.

Infrastructure Saturday - Wrap Up

2010-11-08T10:34:00.003+10:00

Firstly a huge thank-you to everyone that attended the session on Saturday, and also a thank-you to BenF for assisting with the presentation (and letting me out of the cage for the day).

I am working with the organizers of Infrastructure Saturday to make the slides available for download, so stay tuned for more information on that front.

Some interesting questions were raised during the session and I thought I would provide some additional information here for those that were interested.

During the session some questions/discussions were raised about Virtalisation. I won't go into this in this post because I am actually expecting some interesting news on that front to come out of the SQLPASS which is being held this week in Seattle which although I am not attending I will be following closely.

At the end of the session I was approached with the question "We currently have multiple SQL 2005/2008 servers but our monitoring is only of the server layer, can you suggest/recommend how we could monitor the SQL layer".

In my experience a combination of monitoring solutions can offer a much more holistic view of your environment. This also helps when monitoring multiple different Operating Systems (e.g. Windows and Linux). Of cause this is based on my experience with monitoring 220+ servers.

The reason a combination of monitoring solutions can offer more depth is because some of monitoring platforms offer better network monitoring than others, and other monitoring platforms offer better Application / Software monitoring.

The challenge with monitoring a SQL Server is that you not only need to monitor the Infrastructure (e.g. ICMP, TCP Port tests, etc) but you need to be able to check Event Logs and that Services are running. Most monitoring systems can provide those tests, however you also need to monitor the actual SQL Instance layer and some of the best ways of doing that is through some form of script which can run a TSQL statement or use SMO to check aspects of the environment and return a status code to the Monitoring environment.

Some of the key SQL layer aspects that a good monitoring solution should be able to check are:
- SQL Server Memory
- SQL Server Error Log
- SQL Server Settings
- Database File Free Space
- Database Status
- Database Settings (for enforcing environment settings)
- Blocking Locks

My experience has been largely about using Microsoft Operations Manager (aka MOM) and custom VBScripts to ensure that our business rules are enforced. This has also allowed a number of automated actions to be implemented while allowing for the dynamics of the farm.

Here are some links to some monitoring platforms to look at:

Microsoft System Center Operations Manager (aka SCOM, the latest release of MOM)
http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx
If I had to recommend an enterprise software it would be SCOM, only because that is where my experience has been with. Of cause there are other offerings from EMC and other vendors which might be more suitable to your environment.

If your looking for some free monitoring solutions purhaps the following might be of interest:

Zenoss
http://www.zenoss.com/ or http://community.zenoss.org
I have had some experience with using this and it can offer a good monitoring tool for network monitoring (ICMP) and auto discovery. It does take some amount of tweaking to get to work well with Windows (as with any Unix based monitoring tool) however it can provide that first level monitoring quite well and there are a number of ZenPacks available to provide SQL Server monitoring. However I do find it quite challenging to come up with a in-depth SQL monitoring from it, but this is purely because I am use to a Windows Scripting environment.

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
I previously blogged about this (and some other open-source tools). This is still my fav free open-source offering, and a great option if you do not have but budget for SCOM and if you are willing to write your own client front-end, or if you need to plug into an existing monitoring front-end.

I hope this information helps you to find a way to monitor your SQL database environment. Feel free to post comments should you need more info.

Thanks again to everyone that attended the session.

Infrastructure Saturday

2010-11-06T09:03:00.002+10:00

Today is Infrastructure Saturday at Microsoft Brisbane, I'll be there presenting from 12:35pm – 1:20pm in Theater 2......

More info at http://infrastructuresaturday.org/

TSQL to report DB Mirroring status and configuration

2010-08-31T18:27:00.002+10:00

Have you been faced with the need to report on, or verify the configuration of your SQL Server Database Mirroring. Well maybe you have or maybe you haven't, if you only have a small number of databases with DB Mirroring then this may not be very useful, but for me with 50-100 databases with DB Mirroring I needed a way to quickly check what the status and safety level of the mirrored databases were. Not to mention the fact that I needed to easily identify the databases that were no mirrored to report back to the application owners so they knew what was not in a DR configuration.

Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:

SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name

Export the SSRS Report Server's folder / item structure using TSQL

2010-08-02T10:36:00.009+10:00

If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.

Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name

In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:

SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName

When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration

Useful Open Source SQL Tools

2010-07-23T12:33:00.003+10:00

The following are some useful Open Source tools available to assist with managing and administering a MS SQL Server:

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
Also mentioned in http://www.codecapers.com/post/free-scom-alternative-for-sql.aspx

Open DBDiff
http://opendbiff.codeplex.com/

Performance Analysis of Logs (PAL) Tool
http://pal.codeplex.com/

Hopefully others will find them as useful as I have with simplifying my day-to-day DBA tasks.

Client Alias's.... don't forget your 32 bit apps

2010-03-30T15:14:00.002+10:00

I have been caught out by this in the past myself and a colleague wasted a great deal of time trying all sorts of things only to find out there was a simple fix to all his connectivity woes.

Since the introduction of the 64-bit operating system we have seen applications become split into 32-bit (x86) and 64-bit (x64) branches. While the introduction of 64-bit was a god send for the application developer and server administrators who had been looking for ways to push more memory to applications to allow them to handle the huge tasks that today's business (and sometimes even the home user) wants to throw at them. The unfortunate down side to this was that 32-bit applications were cast out like the lepers of the Bible days and given their own registry space (ahhh you say, now he gets to the point of this, well all I can say is that it was worth it!!!)

If you don't know 32-bit and 64-bit applications have different registry spaces, because 32-bit applications cannot read anything within 64-bit memory. If you were to look at the registry on a 64-bit operating system it may not look different to the naked eye but if you look closely you will find that there is a new branch HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node and it is here were all the 32-bit applications store their registry settings.

So why is this important to anyone that has anything to do with database connections, because as you would be aware SQL Server allows you to configure Client Alias's within it's Configuration (of the connectivity tools) and it is through the use of these Alias's that we can map a Server Name (or even a made up friendly name) to a FQDN server address and non-standard TCP port. The problem is that on a 64-bit operating system there are both 32-bit and 64-bit Client Alias and depending on what the application you are connecting from is will depend on which type of alias it tries to read.

The times that I have usually come across this as a problem is when an installation package is asking for the SQL Server details of where it will create a database and the user is unaware if they are using a 32-bit or 64-bit application and create the Client Alias in the wrong branch.

So my rule is:
When you setup a Client Alias always set it up in both the 32-bit and 64-bit branches of the SQL Server Configuration Manager.

SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix

2010-02-05T14:55:00.003+10:00

Came across this issue today and found this blog so useful I thought I would throw a ping back to it and help others

SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix
http://retr0rob.blogspot.com/2009/09/sql-failure-after-upgrade-from-vmware.html

In short the issue is that after an upgrade of VMWare, SQL may not start and the event log will report that a timeout occurred when trying to connect but the failure happens immediately. If you try and start SQL from the command line (e.g. in single user mode) then you will receive an application pop-up error "This application has failed to start because MSVCP71.dll was not found. Re-installing the application may fix this problem". To fix this issue download the missing MSVCP71.dll (get it here) and copy it to %windir%\system32, then reboot.

NOTE: No warrant is held that this is the solution in all cases but it certainly worked for me, and care should be taken when dealing with system files.

Windows 2008 and xp_logininfo

2009-08-21T12:18:00.004+10:00

I've come across an issue which appears to be caused by changes in the security of Windows 2008.

Originally I believed this was related to SQL 2008 however I have replicated the same behavior on SQL 2008 and SQL 2005 when running on a Windows 2008 OS.

Scenario
In an environment where you have user accounts existing in a parent domain (e.g. DOMAIN01) and Computer and Service accounts existing in a Child Domain (e.g. DOMAIN02) you may find that SQL Server running on a computer in the Child Domain will report the following error when you perform specific operations from a SQL Server running either SQL Server 2005 or SQL Server 2008 on a Windows 2008 Operating System.

Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.

Some operations which may cause this are:

Attempting to restore a database using a 3rd party application such as Red-Gate SQL Backup
Creating a schedule for the re-population of a Full Text Catalogue or creating an SQL agent job with the owner set to a domain account within the Parent Domain (e.g. DOMAIN01)
Using xp_logininfo from within an application / script to check the group membership of a domain user account (e.g. exec xp_logininfo 'DOMAIN01\matticus')

This error occurs due to changes in the security of Windows 2008 and specifically changes to the Microsoft Windows API calls used to retrieve information about the Windows domains and the Windows user accounts.

The issue will only occur when SQL Server is attempting to retrieve information regarding Windows Domains and User accounts from the Parent Domain (e.g. DOMAIN01). Queries to AD objects within the same domain (e.g. DOMAIN02) will be successful.

Steps to Reproduce the Issue
The following steps are provided here to reproduce the error within an environment to meets the configuration as stated in the scenario above.

Connect to the SQL Server running in DOMAIN02 using SSMS and an account with SYSADMIN rights to the SQL Instance
Open a New Query window
Run the following TSQL to reproduce the error (replacing the account name with an account within the parent domain)

exec xp_logininfo 'DOMAIN01\matticus'
You should receive the following error:

Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DOMAIN01\matticus', error code 0x5.
Now run the following TSQL to demonstrate that querying within the same domain is not affected (replacing the account name with the service account within the child domain)

exec xp_logininfo 'DOMAIN02\sqlserviceacc01'
This should complete successfully and return a result set. The same would occur for any other accounts within the Child Domain.

Solution 1
At this time the only solution I have found to this problem is to add the service account which the SQL Server service is running on to the "BUILTIN\Windows Authorization Access Group" group within the parent domain. This membership could be inherited through another domain group being added to that BuiltIn group if you have a large number of servers to manage in your organisation.

NOTE: In the past I had found a similar case where membership of the "BUILTIN\Windows Authorization Access Group" group in the parent domain was required. However that was for an issue with the SCOM2007 Service Broker and also SSRS Email Delivery where I believe they had their own methods of querying AD group memberships or had started to implement the changes which MS were planning for Windows 2008 as all servers in that case were running on Windows 2003 OS. These were though due to the accounts for those services being different to the main service account for the related application. That has been the only other case where I have seen this behaviour. I found this article which clearly outlines the SSRS Email Delivery issue.

Reference Material

How to troubleshoot a SQL Server 8198 error
http://support.microsoft.com/kb/834124

xp_logininfo (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190369.aspx

Some applications and APIs require access to authorization information on account objects
http://support.microsoft.com/kb/331951

xp_logininfo fails for agent account
http://www.sqlservercentral.com/Forums/Topic612567-149-1.aspx

Just a few SQL 2008 links

2009-08-19T17:17:00.003+10:00

Just some links relating to SQL 2008 which I have found from fellow bloggers which have some really good information:

Management Studio Improvements in SQL Server 2008
http://www.simple-talk.com/sql/learn-sql-server/management-studio-improvements-in-sql-server-2008/

Activity Monitor – SSMS 2008
http://sql-articles.com/blogs/activity-monitor-ssms-2008/
This is a really good break down of the activity monitor including permissions and the different tabs available.

New Challenge from the guys at "TSQL Challenges"

2009-08-19T13:38:00.001+10:00

The guys at "TSQL Challenges" have released Challenge #12
http://beyondrelational.com/blogs/tc/archive/2009/08/09/tsql-challenge-12-build-sequential-ranges-of-dates-with-propagation-to-missing-values.aspx

When is a Backup Considered Successful?

2009-06-10T10:30:00.003+10:00

Yesterday while reading the many industry related newsletters I subscribe to I came across a really good article which is essentially a DBA's "Best Practise" guide for ensuring they have backups available when they need them and that they are successful. I was please to find that there wasn't anything written in this article which I was not already aware of and performing as part of my DBA role. Of cause performing test restores for the ~13000 databases my team manage is not very realist.
I also found that this was a good article for not just the DBA but also for the end user to read and therefore gain an understanding of exactly what is involved in a Recovery / Restore request and therefore assist in setting a realistic expectation.

When is a Backup Considered Successful?
By Steven Bouffard, 2009/06/08 @ SQLServerCentral.com
http://www.sqlservercentral.com/articles/Backup+and+Recovery/66703/

Useful web site for Report Viewer Control

2009-04-26T00:11:00.002+10:00

Tonight while investigating some issues a client was having with the SRS 2008 Report Viewer control I stumbled across this useful web site. http://www.gotreportviewer.com/

Report Viewer returning "Unable to load client print control" when trying to print a report

2009-04-16T16:28:00.005+10:00

This week I came across an issue with the SRS Report Viewer component which I believed we had fixed back in October 2008.... if you cast your minds back you may recall the issue with MS Update KB 956391 and that the update to Kill Bits stopped the Report Viewer's Print functionality. If you don't then read http://blogs.msdn.com/brianhartman/archive/2008/11/05/client-print-fails-to-load-after-microsoft-update-956391.aspx

During the investigation into the issue we found a number of forums also mentioning that the issue had re-occurred so hopefully this helps point people in the right direction and save them time in their investigation.

So back to the client's issue. As with when MS rolled out update KB 956391 if you were to try and print a report you would receive the "Unable to load client print control" error. After reapplying the hotfix KB 954606 and then also upgrading the SQL Server to SP3 without any impact on the issue, I started to look further into the clients configuration.

The clients environment consisted of a front-end Web server which was serving their web site (which includes a front-end custom built report viewer), and also a back-end SQL Server which also has an installation of SRS's Report Manager web interface (and therefore IIS installed) mostly for administration of the report environment. The problem I found was that we were focusing our attention on the SQL Server as an assumption was made that the Report Viewer control was being launched from there. Unfortunately this was not the case, as after checking the clients application I found that their web site (hosted on the front-end web server) actually contained a custom built report viewing tool built in .Net using the Report Viewer component.

Once I had identified that the issue was related to the front-end web site and not the SQL Server, I was then able to track down the following assemblies for the Report Viewer component being servered by their web application's /bin directory, and what I found that these were actually the incorrect versions and were in fact pre hotfix KB954606.

Name: Microsoft.ReportViewer.WebForms.dll
Version: 8.0.50727.42

Name: Microsoft.ReportViewer.Common.dll
Version: 8.0.50727.42

When I compared these to the version of the assemblies in the GAC on the SQL Server I found that these were quite out dated:

Microsoft.ReportViewer.WebForms.dll
8.0.50727.1843

Microsoft.ReportViewer.Common.dll
8.0.50727.1843

TIP: To check the versions of a DLL in the GAC correctly, right click the assembly and open it's properties. Then click the Version tab and the correct version will be reported. The version reported on the General tab and also in the GAC list is not reported correctly.

Once we replaced the out dated DLL's with the latest DLL's from the SQL Server the Print functionality started working again.

On a side note to this I actually found that the Developer had not patched his/her system with either hotfix KB954606 or SP3 for SQL Server and therefore when they published an update to the application they broke the Print functionality again. A lesson should be learnt here to make sure all of your patching is kept up to date, for not only your production systems but also development systems.

To give credit where credit is due this post definitely helped me on my way to identifying the cause.

2009-04-14T14:47:00.003+10:00

Recently I came across the an issue with MOM 2005 Agents reporting communication issues when trying to contact the Management Server. While investigating the issue I found a number of posts online which indicated had the same symptoms.

Most of what was being commented about online related to MS KB 885416 which involves the following events being reported in the Agent's Application Event Log.

Event Type: Error
Event Source: Microsoft Operations Manager
Event Category: None
Event ID: 21293
User: NT AUTHORITY\SYSTEM
Description: The agent was unable to send data to the MOM Server at ManagementServerName. The error code is 10054. An existing connection was forcibly closed by the remote host.

Event Type: Information
Event Source: Microsoft Operations Manager
Event Category: None
Event ID: 26021
User: NT AUTHORITY\SYSTEM
Description: The agent has restored communication to ManagementServerName

Unfortunately like the MS KB 885416 everything pointed to needing to install a hotfix or Service Pack 1, however like other people posting about this issue I had already installed Service Pack1. In fact this issue is the first thing fixed by Service Pack 1 according to http://support.microsoft.com/kb/905420

After hitting a brick wall with my investigation I ended up contacting Microsoft support for assistance and after providing their technician with a very clear overview of everything I had already investigated he immediately found that there was another error also being logged. This error which he found was:

Event ID: 26009
Category: None
Source: Microsoft Operations Manager
Type: Error

Machine: ComputerName
Message:
The agent could not connect to the MOM Server FQDN.MOM.Server. The error reported is 'No connection could be made because the target machine actively refused it.'. Verify the management group name is correct, the MOM Server is running, that it is listening on port 1270, and that any firewalls between this agent and the MOM server are configured to pass TCP and UDP traffic on port 1270.

Where the MS tech found this error is still beyond me as I could never see it but it was either in the Event Log or the MOM log files from the Agent.

Once this error was detected Microsoft directed me to the MB KB 934441 which matched the problem we were having.

After downloading the Hotfix as described in MB KB 934441 and applying it to the affected Management Server the communication issue was immediately corrected and monitoring restored.

I am still not 100% sure of the cause of this as Microsoft never provided a definite cause for why the errors were being reported but the hotfix has definitely corrected almost every case of this issue we came across.

NOTE: After patching the affected Management Server I also then patched all of my other Management Servers (6 in total) and then proceeded to patch the Agents, which I am still working through.

Querying the pending actions for agents in MOM 2005

2009-04-14T13:37:00.002+10:00

Recently I came across a situation where I had patched our MOM2005 Management Servers with a MS hotfix to correct a problem we were experiencing and needed to then rollout the patch to all of our agents. As our environment is a combination of agents with either Control Levels Full or None I needed to determine and keep track of which agents I had patched.

To do this I wrote the following TSQL statement to query the OnePoint database and allow me to export the results to Excell to make it easier to keep track of what was still to be patched.

SELECT C.[Name] AS Name,
C.[Domain],
CM.[Name] AS ConfigManagerName,
(CASE Convert(varchar(20), C.ManagedType)
WHEN '0' THEN 'Unmanaged'
WHEN '2' THEN 'Agent-Managed'
ELSE Convert(varchar(20), C.ManagedType)
END) as 'Management Mode',
(CASE IsNull(dbo.fn_GetControlLevel(C.idComputer),'FUll')
WHEN 'Full' THEN 'Full'
WHEN 'Group' THEN 'None'
ELSE 'UNK'
END) As ControlLevel,
(CASE Convert(varchar(20),C.PendingAction)
WHEN '0' THEN ''
WHEN '1' THEN 'Approve Manual Agent Install'
WHEN '2' THEN 'Uninstall Agent'
WHEN '4' THEN 'Install Agent'
WHEN '5' THEN 'Requires Patching'
ELSE Convert(varchar(20),C.PendingAction)
END) AS PendingAction
FROM
[dbo].[Computer] C
LEFT OUTER JOIN [dbo].[Computer] CM ON C.[idConfigManager] = CM.[idComputer]
WHERE C.IsAddedByServiceDiscovery = 0
ORDER BY C.Name