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 I looked at other methods.  Method 1) Use T-SQL to generate a script using the metadata or method 2) use SMO (SQL Server Management Objects).  Although I like doing things with T-SQL, I thought I’d give SMO a try and below is the result.  I just hope this is made easier in future releases.

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Text;
using System.Collections.Specialized;

namespace SQLRMOSMO
{
    class SMOTest   // Scripts all indexes for a specified database
    {
        static void Main(string[] args)
        {
            string servername;
            string databasename;
            servername = "<server\\instance>";
            databasename = "<database>";

            Server server = new Server(servername);
            Database db = server.Databases[databasename];
            ScriptingOptions so = new ScriptingOptions();
            so.ScriptDrops = false;
            so.Indexes = true;
            so.IncludeIfNotExists = false;
            so.DriForeignKeys = false;
            so.FileName = "c:\indexes.sql";
            so.AppendToFile = true;
            foreach (Table t in db.Tables)
            {
                foreach (Index i in t.Indexes)
                    i.Script(so);
            }

        }
    }
}

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 a pre-requisite). 

The SSIS team have added a BULK INSERT option on the ADO.NET Destination in SQL Server 2008 R2 to improve performance, this blog entry covers the details: http://blogs.msdn.com/mattm/archive/2009/11/12/something-new-for-ssis-in-sql-server-2008-r2-november-ctp.aspx

On the topic of indexes, it looks like there is a bug in the scripting engine in SQL Server 2008 R2.  Scripting the index as DROP to an SSMS window returns the following however the ONLINE syntax is not supported with SQL Azure

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

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 db
create database trigtestdb
go

use trigtestdb
go
create table tblextrig (eventid int identity, eventdata xml)
go

-- create event driven trigger
create trigger trig_ddl
   on database
   for create_table, drop_table, alter_table
as
   insert tblextrig values (eventdata())
go

-- do some ddl
create table ben (id int)
drop table ben
create table ben (id int)
go

-- check if DML events have been logged by trigger
select eventid, eventdata from tblextrig
go

-- clean up
use master
go

drop database trigtestdb

Two new whitepapers from the SQL Storage Team:

Working with TempDB
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
 
Physical Database Storage
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/PhysDBStor.doc

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 the following directory tree C:\Program Files\Microsoft Platform SDK for Windows XP SP2\Bin. It's provides a simple yet useful interface for browsing and analyzing those log files which are generated by failed setups and service pack installs. 

I've recently corrected this post (thanks to Matt Tisdel) as I included an incorrect URL (the correct one is shown above).  Further information on WiLogUtl.exe can be found in MSDN here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msi/setup/wilogutl_exe.asp and here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msi/setup/windows_installer_development_tools.asp

On a slightly different topic, my new favourite RSS reader is now Sauce Reader from Synop http://www.synop.com/Products/SauceReader/Home - this is great for consolidating RSS reads.

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

There are some good katmai whitepapers appearing on the the katmai site.  The list is available here http://www.microsoft.com/sql/2008/learning/whitepapers.mspx. 

Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_perfandscale.mspx 

Manageability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_manageability.mspx

Delivering location intelligence with spatial data http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx

SQL Server 2008 Reporting Services http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_ssrs.mspx

What's new for XML in SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx

Managing unstructured data http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx

Server consolidation http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_srv_consol.mspx

Data warehousing http://www.microsoft.com/sql/techinfo/whitepapers/sql-for-datawarehousing_2008.mspx

Data Programmability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_dp.mspx

I find it frustrating that the SQL Server 2005/2008 default trace is continually overwritten and there is no way to store x number of files or x MBs of data.  As a workaround, I developed an SSIS package to monitor the \LOG folder and automatically archive the default trace file whenever a new file is created.

This consists of a FOR LOOP container, a Script Task and a File System Task plus a whole bunch of variables and property expressions.

The guts of the package is really in the Script Task as this is where I use a WMI query to monitor the \LOG folder for .trc files.  The file is then renamed (date-time-servername-file) to another folder\share which can be a UNC structure e.g. \\server\share.  This way I have a permanent record of the basic server activity for root cause analysis/troubleshooting. 

The screenshot below shows the basic structure of the package.

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 but I prefer C# so modified the code and made some adjustments to suit my (debugging) needs.  I set two package variables, RemoteURI and LocalFileName, to store the URL (source) and filename (destination).

This works really well and I can change the variables at run-time using property expressions

public void Main()
       {
           WebClient myWebClient;
           string RemoteURI;
           string LocalFileName;
           bool FireAgain = true;

           Dts.Log("entering download..", 999, null);

           try
           {
               myWebClient = new WebClient();

               RemoteURI = Dts.Variables["User::vPipeline"].Value.ToString();
               LocalFileName = Dts.Variables["User::vLocalFileName"].Value.ToString();

               MessageBox.Show(RemoteURI);
               MessageBox.Show(LocalFileName);

               // Notification
               Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);

               // Download the file
               myWebClient.DownloadFile(RemoteURI, LocalFileName);

               Dts.TaskResult = (int)ScriptResults.Success;

           }

           catch (Exception ex)
           {
               // Catch and handle error
               Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
               Dts.TaskResult = (int)ScriptResults.Failure;
           }

       }

Loading IIS logs (text files with fixed width spaces) was fairly challenging due as the column definitions would alter throughout.  I therefore used the .NET StreamReader class to read the metadata and detect changes in the fields. 

The data flow is shown below.

A snippet of the code in the Load Data (Source Script Component) is presented below

Extracting the data from the file was relatively straightforward.  I placed the string into an array based on the fixed spacing between fields.  From the data, I was able to extract useful information such as browser type i.e. Safari, Chrome, IE and even browser version. 

IP addresses were mapped to geolocation using the free GeoLite information http://www.maxmind.com/app/geolite (CSV data imported into the database).  I converted longitude and latitude to the spatial geography data type and presented this against a world map (I wanted to validate that the GeoLite data correctly mapped to the IP address e.g. country/city to IP address).

   1:  USE BiKitchen;

   2:   

   3:  DECLARE @SRID int = 4326

   4:  DECLARE @pLat nvarchar(max)

   5:  DECLARE @pLong nvarchar(max)

   6:  DECLARE @g geography

   7:   

   8:  -- Check longitude and latitude for London

   9:  SET @pLat = (SELECT CAST(Latitude AS nvarchar(max)) FROM [GeoData].[GeoLiteCity-Location] WHERE locid = 13547)

  10:  SET @pLong = (SELECT CAST(longitude AS nvarchar(max)) FROM [GeoData].[GeoLiteCity-Location] WHERE locid = 13547)

  11:   

  12:  SET @g =  geography::STPointFromText('POINT(' +

  13:          @pLong + ' ' +

  14:          @pLat + ')', @SRID).BufferWithTolerance(11000, 1000, 0)

  15:   

  16:  SELECT @pLat AS Latitude, @pLong AS Longitude

  17:  -- SELECT @g.Lat, @g.Long

  18:   

  19:  -- Map the geography type to base world map data

  20:  -- View the result in the spatial tab to validate coordinates

  21:  SELECT @g AS spatiallocation

  22:  UNION ALL SELECT geog FROM World_Borders

The star schema was built and deployed in SQL Server 2012 Analysis Services (UDM).  I found named calculations to be incredibly powerful way of extending the data model and making attributes more meaningful for end-users

The data was presented using Excel 2010, a screenshot is shown below.  I found slicers to be extremely useful

I thought it would be interesting to see what Excel 2013 had to offer so I tried to create a Power View report but this is not currently supported against the UDM.   There are however some nice enhancements to chart types so I’ll be looking at this in more detail.

I’ve recently been asked for details regarding the SSIS package I wrote to copy and archive the default trace file which is generated by SQL Server.  The contents of the file can be quite useful for troubleshooting or diagnosis purposes.

I’ve updated the package to work with SQL Server 2008 R2 and SQL Server 2012.  

The detection of a new trace file is implemented using a Script Task which watches the \Log folder for new files.  When a new file is detected it copies the previous file to an archive location.   The logic is embedded in Visual Basic.NET (not my personal choice although I was limited in SQL Server 2005 when I wrote the package). 

The archive process renames the file with the date and time and then copies the file to a chosen location.   I should point out that I use expressions on a few variables to alter outputs such as the filename i.e. date_time_filename. 

I also noticed that the service needs appropriate permissions to both access the \LOG directory and also copy to the target directory, in my scenario, this was \\server\share.  When I was testing, I launch SSDT (SQL Server Data Tools) using Administrator privileges for testing purposes as a quick workround to permission issues).

Here is the code for the Script Task (apologies for the word wrap, the Live Writer plug-in seems to do this to fit it on the page).  I have commented out some of the writeline commands I was using to debug the package when it was initially developed.

I hope you find this useful.

I will try and attach the SSIS package to this post later.

After a few late nights, some coffee and a few review cycles, a new article has just been published on the SQLCAT site which provides an overview of the subscriber initialisation techniques for transactional replication and, more specifically using an array-based snapshot http://sqlcat.com/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx

The Microsoft Certified Master (MCM) program was announced this week at Tech-Ed in the US.  The public URL is now live and all the information can be viewed here http://www.microsoft.com/learning/mcp/master/default.mspx

I had unfortunately picked up the (bad) habit of debugging my SSIS packages by using messagebox.show at design time.  The SSIS data flow Script component does not allow debugging using this approach so I had to find an alternative.  I discovered that it is possible to output parameters at design time using the FireInformation in the Script component.  This puts the parameters in the progress window.

// Output parameters into the SSIS progress window
bool FireAgain = true;
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of time is " + Row.ParamValueTime.ToString(), "", 0, ref FireAgain);
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of environment is " + Row.ParamValueEnvironment.ToString(), "", 0, ref FireAgain);

I like this approach although I should probably output the parameters to one of the other console windows.

I'm now a big fan of database mail in SQL Server 2005 - it is so much better than SQLMail.  For starters, it is now cluster-aware (customers have been asking for this for a while now), supports SMTP and takes advantage of service broker.  When database mail is configured, it is at the user database level.  Various objects are installed in the database being enabled for mail.  The stored procedure, sp_send_dbmail, is one of these objects and it takes a variety of parameters e.g. queries can be used and the results can be attached as a file.  The following script provides a simple example:

EXEC dbo.sp_send_dbmail

@profile_name = 'YUKON01_Database_Mail',

@recipients = '<email address>',

@body = 'This is a query test',

@subject = 'Query Test',

@query = '<T-SQL Statement>',

@attach_query_result_as_file = 1 ;

There are also two DMV's which can be queried to look at the metadata and determine when the last message was sent; these are sysmail_mailitems & sysmail_log.  Please feel free to post any comments on your thoughts regarding database mail. 

As you probably know, Service Pack 2 for SQL Server 2005 introduces the vardecimal storage format.  Below is a step-by-step example using the AdventureWorks database.  There are a few incorrections in BOL SP2 CTP2 although these are not major problems and can be easily resolved; I imagine discussions are ongoing about naming conventions for the vardecimal options. 

USE master ;
GO

-- Enable vardecimal on database
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON' ;
GO

-- Check the vardecimal storage format state for all databases in the instance
EXEC sp_db_vardecimal_storage_format
GO

-- Enable vardecimal compression at the table level
USE AdventureWorks
GO
-- Note: The BOL example incorrectly references 'decimal data compression'
EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 1
GO

-- Does not show vardecimal properties
EXEC sp_help 'Sales.SalesOrderDetail'

-- So, use the TableHasVarDecimalStorageFormat objectproperty
USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO

-- Under the covers, this uses sys.dm_db_index_physical_stats to calculate the stats
-- Documented in BOL CTP2 as sp_estimatedecimalcompression
EXEC sp_estimated_rowsize_reduction_for_vardecimal 'Sales.SalesOrderDetail' ;

-- Clean-up / disable vardecimal storage format
USE AdventureWorks
GO

-- Disable table-level storage format
EXEC sp_tableoption 'Sales.SalesOrderDetail', 'vardecimal storage format', 0
GO
USE master;
GO

-- Disable database property
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'OFF' ;
GO

The sysinternal tools are now on TechNet.  I know this is not specifically related to SQL Server however, but they are very useful tools which can help diagnose performance problems.   I particularly like process explorer and process monitor (and they work on x64 & Vista too!).  Get them here: http://www.microsoft.com/technet/sysinternals/processesandthreadsutilities.mspx

In case you were wondering, Longhorn & Vista will only support SQL Server 2005 Service Pack 2. This should be out as a Community Technology Preview (CTP) build sometime soon (I'll blog about the forthcoming changes once we have publicly announced the CTP details). A more detailed explanation on SQL Support for Longhorn & Vista is posted here: http://www.microsoft.com/sql/howtobuy/sqlonvista.mspx

You can determine the formatted sector size of the database using DBCC FILEHEADER ('<dbname>').  The DBCC command actually returns quite a lot of information but we are only interested in the sector size column.  

Hi all

Apologies for not updating my blog as often as possible - I've been on vacation for the past couple of weeks and managed to lose most of my possesions when the villa was broken into!  Fortunately, my wallet and passport has been recovered in Valencia last week - they were found dumped in a skip - so I'm in the process of trying to recover these from the Spanish Guardia Civil (which is not easy)!

Everything is almost sorted out and I'm finally getting back into the SQL Server world.  There's a new SQL Server 2005 portal and some excellent whitepapers here: http://www.microsoft.com/sql/2005/techinfo/default.asp

I'm currently working on a few support issues related EMPTYFILE and SQLXML - I'll post my findings once I've done some research and analysis.  It's too early to add further comments at the moment :-)

Cheers

Ben

A few interesting whitepapers comparing SQL Server 2005 to Oracle (and particularly the use of RAC) have just been posted here http://www.microsoft.com/sql/prodinfo/compare/oracle/default.mspx. 

On a slightly different note (non-SQL Server), I am disappointed that PDF support is being pulled from Office 2007. I've been using this a lot in Beta 2 however I hear it might be included as a free download but not sure yet.  Brian has a good post about the reasons for removing PDF support http://blogs.msdn.com/brian_jones/archive/2006/06/02/613702.aspx. This is Adobe's official response http://www.adobe.com/aboutadobe/pressroom/statement.html

PerformancePoint Server 2007 CTP1 is now available via the Connect website https://connect.microsoft.com/site/sitehome.aspx?SiteID=181.  The bits (x86 and x64) are available to download including documentation and sample data etc.  I haven't had a change to look at it yet but will do over the next few days. 

On a slightly different note, my brother has decided to run in the London Marathon (26 miles!).  He is raising money for Concern Worldwide - this is an international charity focused on education, health and emergencies. They work all over the world but concentrate much of their work around Africa.  If you'd like to sponsor him for a good cause, please do so via: http://www.justgiving.com/mattjones.  Thank you. 

I haven't posted for a while (please forgive me) as I've been quite busy over the past few weeks.  I should really try to post more frequently.  Anyway, I am not going to talk about Microsoft software but instead talk about the release of SQL Prompt 3 from Red Gate http://www.red-gate.com/products/SQL_Prompt/index.htm.  I recently received the 'we have a new version' email so decided to take a look and I must say it is looking good. The metadata look-up performance is certainly better than version 2. 

I'm also quite interested in the SQL Refactor tool http://www.red-gate.com/products/SQL_Refactor/index.htm which is also available from Red Gate.  The T-SQL formatter 'Lay Out SQL' option is something I've been looking for ever since I saw something similiar in Toad Formatter Plus for Oracle.