Category: SQL Server 2005

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...

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...

SSIS error 0xC0202071

If you are importing to a remote SQL server, you may recieve a 0xC0202071 error code. While this works locally on a SQL Server, the only way I've found to get around this for remote servers is to replace the destination of 'SQL Server Destination' with 'OLE DB Destination'.No cause yet determined and BIDS truncates the error description so...

Unable to delete publication

If you need to iterate over each table, sp_MSForEachTable will do the trick.  I recently needed to mark each table as replicated to that I could change the log (not a production server).  This did the trick: exec sp_MSForEachTable @command1 = " EXEC sp_msunmarkreplinfo '?'"