SQL Server
How to determine the current database context in SQL Server
I like to run a check script I’ve crafted that tells me stats on the database before I upgrade and perform schema changes. To make the script more universal, I wanted it to report the name of the database the stats are for (for printing purposes). I knew how to check the database server hostname [...]
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 [...]
Powershell + 7zip + SQL Server backups
I needed a way to shrink the backups in SQL Server 2005 (native compression doesn’t exist until the 2008 version). 7zip compresses better than most anything, it’s free (even for commercial use), its stable, and it’s small. Powershell is my favorite tool builder. This is the result: #set alias to 7zip – adjust path as [...]
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 [...]
SQL server date formats
How many different ways are there to format a date/time? Too many. Here is a list of formats and the specific command to get them: http://www.sql-server-helper.com/tips/date-formats.aspx
Non numeric custom ID generated in SQL Server
Sometimes the Identity Column will not suffice – you need to create something custom. This article will explain how to handle that. It demonstrates how to create IDs that auto-increment and yet use custom rules like XXYYY for the result.
Remove characters from a column
I needed to scrub a column of power meters. I needed to remove certain characters that had found their way into the system. SELECT dbo.site.power_meter , REPLACE(REPLACE(REPLACE(REPLACE(dbo.node.power_meter, ' ', ''), '-', ''), '/', ''), '.', '') AS better_power_meter [...]
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 [...]
Prepare SQL Server for master database rebuild
How do you prepare for a master rebuild? This tutorial will make sure you have what you need to get yours back online easier: Part 1, Part 2, Part 3
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 '?\'"