Shane on September 17th, 2007

Wondering which SQL Job runs which subscription? Here’s how to find out. select,, d.path, c.description, c.laststatus, c.lastruntime from msdb.dbo.sysjobs a join reportserver..reportschedule b on as nvarchar(255)) join reportserver..subscriptions c on b.subscriptionid=c.subscriptionid join reportserver..catalog d on c.report_oid=d.itemid order by c.lastruntime desc

Continue reading about File Under: Reporting Services Tricks

Shane on July 2nd, 2007

Now that our datawarehouse database is over 250GB, using the built-in SQL Server tools are not an option. I have experience with Veritas BackupExec and NetBackup, and here is my typical experience: backups are scheduled overnight and usually take the entire night. Restores are no faster. Here’s a product that’s worth looking in to: Idera [...]

Continue reading about Faster Backups AND Smaller Files

Shane on June 29th, 2007

My latest debacle loading this massive table has to do with removing duplicates before I try adding a primary key to the table. I had to restart the load process a couple times before I finally settled into a method that worked well. (Running multilpe instances of DTExec on different subsets of the source data, [...]

Continue reading about Massive In-Place Delete

Shane on June 28th, 2007

Dear Shane, Next time trust me. SELECT INTO is always faster than UPDATE when you’re talking about updating the entire table. Even if the table has three NVARCHAR(MAX) fields and you’re only updating one 4 byte INT column. Rather than run an update for 1hr 6min before you decide to cancel said query and trigger [...]

Continue reading about BULK UPDATE

Shane on June 28th, 2007

When I was working with Oracle, I got hooked on using MINUS and INTERSECT. Well, it turns out that Microsoft implemented these functions in a slightly different way in SQL 2005. Jeff Smith lays out a nice example here.

Continue reading about Favorite New SQL 2005 Syntax

Shane on November 2nd, 2006

The holy grail of a former .NET web developer is how to combine the techniques of agile and test-driven development with ETL tools and data volumes. Mark Rittman shows that he is a brother-in-arms with my quest.

Continue reading about Testing Framework for ETL

Shane on November 2nd, 2006

This excellent piece in DMReview deliberates on some criteria for selecting (or justifying) an ETL tool. We are struggling with this right now. Our shop uses a combination of Informatica and SQL Server DTS for our ETL jobs. With Oracle Warehouse Builder 10gR2 and SQL Server Integration Services both released as viable alternatives, we need [...]

Continue reading about Why Do We Use Informatica?