Category: SQL Server

Newest DDL edits on SQL Server

After coming back to work after the holidays, I needed a reminder of where I left off before taking 2 weeks off. SQL Server makes it easy to recall what I last worked on. Below is how to determine the last time a user table was altered or when it was created. SELECT                ,        ,...

SQL Server: How to retrieve the check constraints on columns in a specific table

sys.check_constraints contains a row for each CHECK constraint 1. parent_column_id – column id for which CHECK constraint is defined 2. parent_object_id – id of the object for whose column the constraint is defined. 3. create/modify_date – creation/modification date 4. definition – constraint’s criteria Since the name of the column the constraint is built on is not present in sys.check_constraints, you...

Setting SQL Server ROWGUIDCOL property on [uniqueidentifier] (GUID) columns

I’ve been converted – I now use GUID (UNIQUEIDENTIFIERS) instead of INT for most database tables’ primary key.  If there is only going to be 100 or less rows, I don’t bother but most everything else I feel justifies the extra bytes of GUID. It ensures the key is unique which greatly simplifies merging data in the future. A...

Convert values stored in a local server time column (default GETDATE()) to UTC time

I am upgrading a legacy application that used GETDATE() on columns (describing the row created date) in many tables. We rename the column (our column names indicate local or UTC) Set the default to now be GETUTCDATE() Convert the old values. It then becomes quite trivial to update the legacy values (step 3) to what they would have been if this had been...

SSRS: Could not load type ‘Microsoft.ReportingServices.UI.GlobalApp’

I suspect some MS patch caused this as no one has touched this server in months yet today, we got the following:   Server Error in ‘/Reports’ Application. Parser Error Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately....

Service Unavailable on SQL Services Reporting Services after Microsoft Patch

Last night, my SSRS server failed on the first access attempt after Multiple Windows patches were installed by WSUS overnight (SQL Server 2005 EE, Windows 2003 SP2). This is not a critical server for me so it doesn't get tested before patches are applied. As such, it gets all patches immediately without testing and, in fact, is a test for such...