Shane on January 31st, 2007

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

Continue reading about Extracting Metadata From SQL Server

Shane on January 31st, 2007

Here is a useful query for getting a list of failed jobs from SQL Server. If you are responsible for tracking and logging job failures, this administrative work can be overwhelming in a busy week. This query will list all jobs that failed during a given time period. It uses a function that I created, [...]

Continue reading about SQL Server Failed Jobs

Shane on January 18th, 2007

Loading your target incrementally offers a huge performance benefit over running full truncate/reloads, but there is a danger of missing inserts or updates in your source system. It can take hours or days to track down the source of these problems (if it can be done at all!) and problems are generally not found until [...]

Continue reading about Audit Your ETL With CHECKSUM