How can I generate a T-SQL script for just the indexes?

I was trying to work out the best way to generate a T-SQL script for only the indexes and I thought that this would be really easy in the Management Studio interface.  I was mistaken.  SQL Server Management Studio also generates scripts for the tables when you want the indexes.  This is not great so…

5

How do I find out if Large Values out of Row is enabled?

I thought the SQL Server 2005 Large Values out of Row would be a sp_tableoption config value similar to text in row – apparently not!  It took a while to find out that this is hidden in sys.tables.  The following query returns the value I was looking for: SELECT [name] AS tablename, large_value_types_out_of_row FROM sys.tables

4

Whitepapers: Working with TempDB & Physical Database Storage

Two new whitepapers from the SQL Storage Team:   Working with TempDBhttp://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc Physical Database Storagehttp://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc

4

SQL Server 2005 Extended Triggers

Ok, I am impressed with everything in SQL Server 2005, especially extended triggers to capture DDL actions.  I was playing around with these today and I can see how this will be a nice feature, especially for audit trails.  I’ve included a simple script below. — create sample dbcreate database trigtestdbgo use trigtestdbgocreate table tblextrig…

4

Using SSIS with SQL Azure databases

I’m quite surprised how easy it is to setup an SSIS package to read and write data to a SQL Azure database.  A simple data flow task with an ADO.NET Destination is easy to setup, just make sure the SQL Azure table has a clustered index otherwise writing data will not work (as this is…

4

Building a data mart to analyse web log traffic

I recently had the opportunity to build a data mart in order to analyse web logs from IIS servers.  The solution utilised SQL Server 2012 Database Engine, Integration Services, Analysis Services and Excel 2010 (to slice/dice the data).    Loading IIS logs (text files with fixed width spaces) was fairly challenging due as the column definitions…

4

Using a C# script task in SSIS to download a file over http

I recently had a requirement to automate downloading a file from an website and then perform ETL on the data in the file.  Fortunately, this is possible via the script task in SSIS (note that this is using SQL Server 2008 Integration Services).  I found a couple of web references to do this in VB.NET…

3

Transposing Columns onto Rows

After a long period of absence, I have returned to posting some blog articles.  This one popped up last week from an Oracle DBA while I was onsite with a customer.  The conversation went something along the lines of: Oracle DBA: “How can I place columns values on rows? I have a limited page width…

3

Analysing SQL Server Error Logs; Useful Tool

I recently found quite a useful tool hidden in the Windows Installer SDK developer Kit called WiLogUtl.exe which can be used to analyze / parse not just SQL Server error logs, but any form of text-based log file.  The tool is embedded in the Windows Platform SDK (XPSP2) which can be downloaded from here http://www.microsoft.com/msdownload/platformsdk/sdkupdate/.  The binary file is installed under…

3

New cumulative update++

Just noticed that the a new cumulative update for SQL Server 2005 service pack 2 has been released.  The KB is here http://support.microsoft.com/kb/943656.  The incremental servicing model has also been clearly defined in the following knowledge base article http://support.microsoft.com/kb/935897

3