Clear SQL Server Cache and Stored Procedure Cache

To clear SQL Server’s cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.

I never seem to have this info handy when I actually need to use it. Posting here for easy reference.

Source:
http://www.devx.com/tips/Tip/14401

SharePoint 2010 Beta 2 SQL Server Version

I ran into some trouble getting SQL Server installed on the same box as SharePoint. It think it may have something to do with the order of installing applications. As a general rule of thumb make sure that you install from the oldest to the newest apps. This is especially true when working with beta software.

Running the configuration wizard I discover that  SharePoint 2010 requires the latest SQL Server updates be installed.

SQL Server 2005:

• SQL Server 2005 Service Pack 3
• Cumulative update package 3 for SQL Server 2005 Service Pack 3

SQL Server 2008:

• SQL Server 2008 SP 1
• Cumulative update package 2 for SQL Server 2008 Service Pack 1

Posting it here hoping it will save someone else some trouble.

Troubleshooting Orphaned Users in SQL Server 2005 / 2008

A couple of days ago, I was tasked with moving a database from an older server to a new souped-up box. Unfortunately the transition was not as smooth as I had hoped for. After restoring the db into the new server I started running into trouble with orphaned users.

An orphaned user is basically just that, a database user without a corresponding server login.  This usually happens when trying to restore a database into a new server, like what I was doing or accidently deleting the server login.

Luckily this issue can be solved relatively easy using the sp_change_users_login stored procedure.

First I get a list of orphaned users and their SIDs using the following procedure:

sp_change_users_login @Action='Report';

Next, I create the missing login from scratch (e.g. “NewLoginName”). The server mappings are not required as the database user roles are preserved in the database.

Then execute the stored procedure again with the following parameters:

sp_change_users_login
      @Action='Update_One',
      @UserNamePattern='dbUserName',
      @LoginName='NewLoginName';

This re-jigs the broken “dbUserName” user with the newly created Server Login “NewLoginName”.

Its worth mentioning that even though there might be a login name existing on the new server identical to the database user name, they are not the same until linked together. You can find out for sure using the above stored procedure with the “REPORT” parameter.

Finally, hereare some instances where sp_change_users_login cannot be used:

• Cannot be used to map database users to Windows-level principals, certificates, or asymmetric keys.
• Cannot be used with a SQL Server login created from a Windows principal or with a user created by using CREATE USER WITHOUT LOGIN.

More information:

• Troubleshooting Orphaned Users
• sp_change_users_login
• Fixing Orphaned Users in SQL Server Database

SSRS - Unable to load client print control

One of the customer sites I work at was suddenly unable to print SSRS reports any more. This issue was occurring for only a few of their client boxes, so this rules out the possibility that it might have been a server issue.

It turns out that SSRS uses an activex control to provide some enhanced printing options. Here's some more information on it (taken from the MSDN site):

The Microsoft ActiveX control, RSPrintClient, provides client-side printing for reports viewed in a browser. The control displays a custom print dialog box that supports features common to other print dialog boxes, including print preview, page selections for specifying specific pages and ranges, page margins, and orientation.

After some investigating it looks like a conflict with KB956391. More information on it here:

http://support.microsoft.com/kb/956391

I'm guessing that this hot fix may have been pushed out by windows update, which explains it's sudden manifestation.

This is the proper way of fixing the issue; as explained in Brian Hartman's blog:

http://blogs.msdn.com/brianhartman/archive/2008/11/05/client-print-fails-to-load-after-microsoft-update-956391.aspx

Or alternatively just uninstall KB956391.

More useful discussions on the "Unable to load client print control" issue:

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=332145&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4006280&SiteID=17

http://wills-blog.com/?p=220

http://forums.microsoft.com/msdn/showpost.aspx?postid=4006172&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=4

Abstracting underlying data sources for SSIS packages using Views

In a perfect world, we'd never have changes to the schema once it has been defined. But in practice this is almost never the case.

I stumbled upon this excellent article which provides a solution to minimize the effects of change.

http://bi-polar23.blogspot.com/2008/09/views-as-for-ssis.html

Retrieving SQL Server 2005 Product Version

Here’s the query used to retrieve the SQL Server Production Version information.

Pretty useful when diagnosing problems in multiple environments.

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')