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
Continue reading about File Under: Reporting Services Tricks
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 [...]
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, [...]
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 [...]
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.
Also on the subject of checksum testing, this query will generate a checksum query for a given table. Use this query to generate the checksum for each table, then customize the sum() clauses if you did any transformations on your data. select case a.xtype when 167 then ‘SUM(LEN(‘+a.name+’)) as sum_len_’+a.name+’,’ when 175 then ‘SUM(LEN(‘+a.name+’)) as [...]
select upper(b.name) as table_name, upper(a.name) as column_name, a.colid, a.length, a.xprec, a.xscale, a.xtype,(CASE WHEN a.xtype=167 then ‘varchar’ WHEN a.xtype=175 then ‘char’ WHEN a.xtype=61 then ‘datetime’ WHEN a.xtype=106 then ‘decimal’ END) as col_type from syscolumns a, sysobjects b where a.id=b.id and b.xtype=’U’ and upper(b.name) ‘DTPROPERTIES’ order by b.name, a.name, a.colid