Author Archive
Monday, September 17th, 2007
File Under: Reporting Services Tricks
Wondering which SQL Job runs which subscription? Here’s how to find out.
select a.name, d.name, d.path, c.description, c.laststatus, c.lastruntime
from msdb.dbo.sysjobs a
join reportserver..reportschedule b on a.name=cast(b.scheduleid 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
No Comments » - Posted in SQL Server by Shane
Monday, July 2nd, 2007
Faster Backups AND Smaller Files
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 SQLSafe. [...]
No Comments » - Posted in SQL Server, Utilities by Shane
Friday, June 29th, 2007
Massive In-Place Delete
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, [...]
No Comments » - Posted in SQL Server, SQL Tricks by Shane
Thursday, June 28th, 2007
BULK UPDATE
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 a 2hr [...]
No Comments » - Posted in SQL Server, SQL Tricks by Shane
Thursday, June 28th, 2007
Favorite New SQL 2005 Syntax
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.
No Comments » - Posted in Other, SQL Server by Shane
Thursday, April 19th, 2007
See Everything, Everywhere
My friend Gary is fulfilling his life-long dream to take a trip around the world. This being the internet age, we get to follow along from the comfort of our living rooms without having to get the shots. I encourage you to check it out. Gary is one of the funniest and smartest people I [...]
No Comments » - Posted in Other by Shane
Monday, April 2nd, 2007
BI Humor
Here’s a little BI humor for anyone from one of my favorite BI writers: Donald Farmer.