40,000 days since the beginning of ‘time’

This is cool...A SQL Server trip through the 1900's by Rob Farley:

40,000 days since the beginning of ‘time’

Next Tuesday feels like a significant day. Not only is it 7/7, and the fourth anniversary of the London bombings (in 2005), but it’s 40,000 days since the beginning of time. Well, not proper time, but since the start of the 1900s.

SELECT DATEDIFF(day,'19000101','20090707')+1 AS DaysOf1900s

So if Day 1 is Jan 1, 1900 (as many systems tend to use – SQL actually considers it Day 0), Day 40000 is 7/7/2009.

I actually rate the start of the twentieth century as a very significant event in the human race. In 1900 the world was changing faster than it had ever done before, and no-one can deny the significance of the twentieth century in history. I’m sure change will continue to become faster, but I found it interesting recently when I noticed that we were approaching this ‘milestone’ of sorts.

http://msmvps.com/blogs/robfarley/archive/2009/07/01/40-000-days-since-the-beginning-of-time.aspx

UPDATE: This gets crazier...
http://msmvps.com/blogs/robfarley/archive/2009/07/05/excel-dates-counted-differently-and-a-reliable-way-of-working-out-the-day-of-the-week.aspx

I'm A VB (VBTeam)

:-)

I'm A VB: Mark Wisecarver
http://blogs.msdn.com/vbteam/pages/i-m-a-vb-mark-wisecarver.aspx

Are you a VB? http://www.surveymonkey.com/s.aspx?sm=dRItygroketO20qGr5fcPA_3d_3d

The MVC T4 template is now up on CodePlex, and it does change your code a bit

Short version: the MVC T4 template (now named T4MVC) is now available on CodePlex, as one of the downloads in the ASP.NET MVC v1.0 Source page.

Poll verdict: it’s ok for T4MVC to make small changes

Yesterday, I posted asking how people felt about having the template modify their code in small ways.  Thanks to all those who commented!  The fact that Scott Hanselman blogged it certainly helped get traffic there :)

The majority of people thought that it was fine as long as

• It’s just those small changes: make classes partial and action methods virtual. Don’t mess with ‘real’ code!
• It asks for permission, or at least tells you what it’s doing

I started looking for a way to pop up a Yes/No dialog, but ended up going with a slightly different approach: T4MVC adds a warning line for every item it modifies.  e.g. when you run it, you might see these in the warnings area:

Running transformation: T4MVC.tt changed the class DinnersController to be partial
Running transformation: T4MVC.tt changed the action method DinnersController.Index to be virtual

Some people were worried about version control.  I tried using TFS, and everything worked fine.  i.e. when the template modifies files, VS automatically checks them out.  We’ll need to see how that works for folks using different systems.

What’s new in this version?

The template on CodePlex (version 2.0.00 at the top of the file) supports what I described in my previous post, and and some new goodies.

Refactoring support for action methods

One of the big issues before was the lack of refactoring support.  e.g. when you wrote:

return RedirectToAction(MVC.Dinners.Details(dinner.DinnerID));

This looked like a call to you Details controller action, but it was actually an unrelated method by the same name.  Hence, if you renamed your action method and refactored, this call was not modified.  It would give a compile error, and had to be hand fixed.

Now the template takes a drastically different approach:

• It extends the controller class
• It overrides the action method (hence the need for it to be virtual!)
• The override never calls the base (that would be very wrong), but instead returns a special ActionResult which captures the call (controller name, action name, parameter value).
• The template emit a new RedirectToAction (or ActionLink, …) overload which understands this special ActionResults, and turns the call data into a ‘regular’ RedirectToAction call.

Pretty tricky stuff, but it works quite well.  Some credit to my manager Mike Montwill for coming up with this crazy idea!

Because the method you call is an override of the real action method, refactoring works perfectly.  Also, if you F12 (Go To Definition) on the call, it’ll go straight to your Action method and not some generated code.

Unfortunately, Visual Studio doesn’t support refactoring in Views, but 3^rd party tools like Resharper and CodeRush do, so if you use one of those, you’re fully covered.

The T4 file can automatically run whenever you build

This was the other big painful issue I was up against: every time you made a change to your code that affect the generated code (e.g. new Action, new View, …), you had to manually save the .tt file to cause it to regenerate the new helper code.

This was a really hard issue, and I must warn you that what I ended up with is more of a workaround than a fix.  However, it is pretty effective, so until we find a better solution, it’ll have to do.

Here is how it works.  Warning: reading this has been shown to cause headaches in lab rats:

• As part of its execution, the T4 file finds itself in the VS project system (it had to do that anyway)
• It then runs the magic instruction ‘projectItem.Document.Saved = false;’, which causes it to become dirty.
• It then proceeds to do its code generation, leaving its file in an unsaved state
• Next time you Build your project, VS first saves all the files
• This causes the ‘dirty’ T4 template to execute, marked itself as dirty again, and redo its code generation
• You get the idea!  If you feel like the lab rats, this may help.

One caveat is that you have to initial the sequence by opening and saving T4MVC.tt once.  After you do that, you don’t need to worry about it.

Support for strongly typed links to static resources

Credit for this idea goes to Jaco Pretorius, who blogged something similar.

The template generates static helpers for your content files and script files.  So instead of writing:

<img src="/Content/nerd.jpg" />

You can now write:

<img src="<%= Links.Content.nerd_jpg %>" />

Likewise, instead of

<script src="/Scripts/Map.js" type="text/javascript"></script>

You can write:

<script src="<%= Links.Scripts.Map_js %>" type="text/javascript"></script>

One unfortunate thing is that for some reason, VS doesn’t support intellisense in the view for parameter values.  As a workaround, you can type it outside of the tag to get intellisense and then copy it there.

More consistent short form to refer to a View from a Controller class

Previously, it supported an _ based short form inside the controller:

return View(View_InvalidOwner);

That was a bit ugly.  Now, the short form is:

return View(Views.InvalidOwner);

Here, Views.InvalidOwner is the same as MVC.Dinners.Views.InvalidOwner, but can be shortened because ‘Views’ is a property on the controller.

Many bug fixes

I also fixed a number of bugs that people reported and that I ran into myself, e.g.

• It supports controllers that are in sub-folders of the Controllers folder and not directly in there
• It works better with nested solution folder

I’m sure there are still quite a few little bugs, and we’ll work through them as we encounter them

http://blogs.msdn.com/davidebb/archive/2009/06/26/the-mvc-t4-template-is-now-up-on-codeplex-and-it-does-change-your-code-a-bit.aspx

If VS2010 ends up hosing Vista. . .

It happens to the best of us, right?

OK, OK, I should be using VM for things like this but I admit that I was so excited to test VS2010 that I jumped right on the Web install from Microsoft.
At first everything was fine.
I use two Vista SP2 systems, Ultimate for my Webcasts and Expression Studio plus VS2008 and SQL Server 2008. . .
Thing is...I tested VS2010 on my Vista Home Premium machine. . .Which I actually like a lot more than the Ultimate system, especially the way the UAC works, better.

Yes as you can tell by the title above things did start going haywire, possibly because I test just about everything Microsoft sends me. ;-)
All of a sudden everything in my Office 2007 apps started bombing out with .NET erorrs, including Outlook Pro 2007. (My poor RSS feeds.)

At the same time I got an offer from Microsoft to beta test Microsoft Security Essentials. (Which I love so far.)
Once installed I was getting the same .NET errors. . .
MSVCR100.dll was not found
\v4.0.20506\mscoreei.dll could not be loaded

Most of the time these were recursive error dialogs and you could not escape them, you know, Cancel, Close, Cancel, Close...Grrrr

So I should have Repaired the .NET framework, right? I did, multiple times, or at least tried. The Repair and Uninstall functions would all rollback.
Look at the logs, right? Yup, and Oh my gosh! I could fall asleep reading those logs. :-)

What I did was uninstall VS2010 and reinstall it. Errors gone. Office apps back, MSE working.

Then this morning I see the very helpful post pasted below by Stephen Boots, in the Microsoft Social forums:

To remove a corrupt .Net Framework install, use this tool: http://astebner.sts.winisp.net/Tools/dotnetfx_cleanup_tool.zip

The download of .Net Framework 2.0 can be found here:

http://www.microsoft.com/downloads/details.aspx?familyid=0856eacb-4362-4b0d-8edd-aab15c5e04f5&displaylang=en

To repair .Net Framework 2.0 and 3.0 on Vista, see this blog entry:

http://blogs.msdn.com/astebner/archive/2007/03/26/how-to-repair-the-net-framework-2-0-and-3-0-on-windows-vista.aspx

Note that after repairing or reinstalling .Net Framework, you will need to reapply the .Net Framework 3.5 SP1 update via Windows Update.

Wow. . .This is one of those times when we really need tips like that. ;-)
  All the best,
    Mark
   http://twitter.com/msftwise

SQL Server Index Defrag Script

Hiya. . .

Some of you may have already seen this, but just in case you haven't;

Some excellent SQL Server work from Michelle Ufford, also known as "SQL Fool".
http://sqlfool.com/2009/06/index-defrag-script-v30/

/* Drop Table Scripts:
Drop Table dbo.dba_indexDefragLog;
Drop Table dbo.dba_indexDefragExclusion;
*/
IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables 
    WHERE [name] In (N'dba_indexDefragLog', 'dba_indexDefragExclusion'))
BEGIN
 
    CREATE TABLE dbo.dba_indexDefragLog
    (
          indexDefrag_id    INT IDENTITY(1,1)   Not Null
        , databaseID        INT                 Not Null
        , databaseName      NVARCHAR(128)       Not Null
        , objectID          INT                 Not Null
        , objectName        NVARCHAR(128)       Not Null
        , indexID           INT                 Not Null
        , indexName         NVARCHAR(128)       Not Null
        , partitionNumber   SMALLINT            Not Null
        , fragmentation     FLOAT               Not Null
        , page_count        INT                 Not Null
        , dateTimeStart     DATETIME            Not Null
        , dateTimeEnd       DATETIME            Null
        , durationSeconds   INT                 Null
 
        CONSTRAINT PK_indexDefragLog 
            PRIMARY KEY CLUSTERED (indexDefrag_id)
    );
 
    PRINT 'dba_indexDefragLog Table Created';
 
    CREATE TABLE dbo.dba_indexDefragExclusion
    (
          databaseID        INT                 Not Null
        , databaseName      NVARCHAR(128)       Not Null
        , objectID          INT                 Not Null
        , objectName        NVARCHAR(128)       Not Null
        , indexID           INT                 Not Null
        , indexName         NVARCHAR(128)       Not Null
        , exclusionMask     INT                 Not Null
            /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
 
        CONSTRAINT PK_indexDefragExclusion 
            PRIMARY KEY CLUSTERED (databaseID, objectID, indexID)
    );
 
    PRINT 'dba_indexDefragExclusion Table Created';
 
END
ELSE
    RAISERROR('One or more tables already exist.  Please drop or rename before proceeding.', 16, 0);
 
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.dba_indexDefrag_sp;
    PRINT 'Procedure dba_indexDefrag_sp dropped';
END;
Go
 
 
CREATE PROCEDURE dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
      @minFragmentation     FLOAT           = 5.0  
        /* in percent, will not defrag if fragmentation less than specified */
    , @rebuildThreshold     FLOAT           = 30.0  
        /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
    , @executeSQL           BIT             = 1     
        /* 1 = execute; 0 = print command only */
    , @DATABASE             VARCHAR(128)    = Null
        /* Option to specify a database name; null will return all */
    , @tableName            VARCHAR(4000)   = Null  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
    , @scanMode             VARCHAR(10)     = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
    , @onlineRebuild        BIT             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @maxDopRestriction    TINYINT         = Null
        /* Option to restrict the number of processors for the operation; only in Enterprise */
    , @printCommands        BIT             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @printFragmentation   BIT             = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
    , @defragDelay          CHAR(8)         = '00:00:05'
        /* time to wait between defrag commands */
    , @debugMode            BIT             = 0
        /* display some useful comments to help determine if/where issues occur */
    , @rebuildStats         BIT             = 1
        /* option to rebuild stats after completed index defrags */
 
AS
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
 
      @minFragmentation     defaulted to 10%, will not defrag if fragmentation 
                            is less than that
 
      @rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
                            greater than 30% will result in rebuild instead
 
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
 
      @database             Optional, specify specific database name to defrag;
                            If not specified, all non-system databases will
                            be defragged.
 
      @tableName            Specify if you only want to defrag indexes for a 
                            specific table, format = databaseName.schema.tableName;
                            if not specified, all tables will be defragged.
 
      @scanMode             Specifies which scan mode to use to determine
                            fragmentation levels.  Options are:
                            LIMITED - scans the parent level; quickest mode,
                                      recommended for most cases.
                            SAMPLED - samples 1% of all data pages; if less than
                                      10k pages, performs a DETAILED scan.
                            DETAILED - scans all data pages.  Use great care with
                                       this mode, as it can cause performance issues.
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
 
      @defragDelay          Time to wait between defrag commands; gives the
                            server a little time to catch up 
 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
 
      @rebuildStats         Affects only statistics that need to be rebuilt
                            1 = rebuild stats
                            0 = do not rebuild stats
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials	Version Description
    ----------------------------------------------------------------------------
    2007-12-18  MFU         1.0     Initial Release
    2008-10-17  MFU         1.1     Added @defragDelay, CIX_temp_indexDefragList
    2008-11-17  MFU         1.2     Added page_count to log table
                                    , added @printFragmentation option
    2009-03-17  MFU         2.0     Provided support for centralized execution
                                    , consolidated Enterprise & Standard versions
                                    , added @debugMode, @maxDopRestriction
                                    , modified LOB and partition logic  
    2009-06-18  MFU         3.0     Fixed bug in LOB logic, added @scanMode option
                                    , added support for stat rebuilds (@rebuildStats)
                                    , support model and msdb defrag
                                    , added columns to the dba_indexDefragLog table
                                    , modified logging to show "in progress" defrags
                                    , added defrag exclusion list (scheduling)
*********************************************************************************
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1;
*********************************************************************************/																
 
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
 
BEGIN
 
    IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait;
 
    /* Declare our variables */
    DECLARE   @objectID             INT
            , @databaseID           INT
            , @databaseName         NVARCHAR(128)
            , @indexID              INT
            , @partitionCount       BIGINT
            , @schemaName           NVARCHAR(128)
            , @objectName           NVARCHAR(128)
            , @indexName            NVARCHAR(128)
            , @partitionNumber      SMALLINT
            , @fragmentation        FLOAT
            , @pageCount            INT
            , @sqlCommand           NVARCHAR(4000)
            , @rebuildCommand       NVARCHAR(200)
            , @dateTimeStart        DATETIME
            , @dateTimeEnd          DATETIME
            , @containsLOB          BIT
            , @editionCheck         BIT
            , @debugMessage         VARCHAR(128)
            , @updateSQL            NVARCHAR(4000)
            , @partitionSQL         NVARCHAR(4000)
            , @partitionSQL_Param   NVARCHAR(1000)
            , @LOB_SQL              NVARCHAR(4000)
            , @LOB_SQL_Param        NVARCHAR(1000)
            , @rebuildStatsID       INT
            , @rebuildStatsSQL      NVARCHAR(1000)
            , @indexDefrag_id       INT;
 
    /* Create our temporary tables */
    CREATE TABLE #indexDefragList
    (
          databaseID        INT
        , databaseName      NVARCHAR(128)
        , objectID          INT
        , indexID           INT
        , partitionNumber   SMALLINT
        , fragmentation     FLOAT
        , page_count        INT
        , defragStatus      BIT
        , schemaName        NVARCHAR(128)   Null
        , objectName        NVARCHAR(128)   Null
        , indexName         NVARCHAR(128)   Null
    );
 
    CREATE TABLE #databaseList
    (
          databaseID        INT
        , databaseName      VARCHAR(128)
        , scanStatus        BIT
        , statsStatus       BIT
    );
 
    CREATE TABLE #processor 
    (
          [INDEX]           INT
        , Name              VARCHAR(128)
        , Internal_Value    INT
        , Character_Value   INT
    );
 
    IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait;
 
    /* Just a little validation... */
    IF @minFragmentation Not Between 0.00 And 100.0
        SET @minFragmentation = 10.0;
 
    IF @rebuildThreshold Not Between 0.00 And 100.0
        SET @rebuildThreshold = 30.0;
 
    IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
        SET @defragDelay = '00:00:05';
 
    IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
        SET @scanMode = 'LIMITED';
 
    /* Make sure we're not exceeding the number of processors we have available */
    INSERT INTO #processor
    EXECUTE XP_MSVER 'ProcessorCount';
 
    IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
        SELECT @maxDopRestriction = Internal_Value
        FROM #processor;
 
    /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
    IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) 
        SET @editionCheck = 1 -- supports online rebuilds
    ELSE
        SET @editionCheck = 0; -- does not support online rebuilds
 
    IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait;
 
    /* Retrieve the list of databases to investigate */
    INSERT INTO #databaseList
    SELECT database_id
        , name
        , 0 -- not scanned yet for fragmentation
        , 0 -- statistics not yet updated
    FROM sys.databases
    WHERE name = IsNull(@DATABASE, name)
        And [name] Not In ('master', 'tempdb')-- exclude system databases
        And [STATE] = 0; -- state must be ONLINE
 
    IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait;
 
    /* Loop through our list of databases */
    WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0
    BEGIN
 
        SELECT TOP 1 @databaseID = databaseID
        FROM #databaseList
        WHERE scanStatus = 0;
 
        SELECT @debugMessage = '  working on ' + DB_NAME(@databaseID) + '...';
 
        IF @debugMode = 1
            RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
       /* Determine which indexes to defrag using our user-defined parameters */
        INSERT INTO #indexDefragList
        SELECT
              database_id AS databaseID
            , QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
            , [OBJECT_ID] AS objectID
            , index_id AS indexID
            , partition_number AS partitionNumber
            , avg_fragmentation_in_percent AS fragmentation
            , page_count 
            , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
            , Null AS 'schemaName'
            , Null AS 'objectName'
            , Null AS 'indexName'
        FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
        WHERE avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
            And index_level = 0 -- leaf-level nodes only, supports @scanMode
        OPTION (MaxDop 2);
 
        /* Keep track of which databases have already been scanned */
        UPDATE #databaseList
        SET scanStatus = 1
        WHERE databaseID = @databaseID;
 
    END
 
    CREATE CLUSTERED INDEX CIX_temp_indexDefragList
        ON #indexDefragList(databaseID, objectID, indexID, partitionNumber);
 
    /* Delete any indexes from our to-do that are also in our exclusion list for today */
    DELETE idl
    FROM #indexDefragList AS idl
    Join dbo.dba_indexDefragExclusion AS ide
        ON idl.databaseID = ide.databaseID
        And idl.objectID = ide.objectID
        And idl.indexID = ide.indexID
    WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) > 0;
 
    SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
    FROM #indexDefragList;
 
    IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
    /* Begin our loop for defragging */
    WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0
    BEGIN
 
        IF @debugMode = 1 RAISERROR('  Picking an index to beat into shape...', 0, 42) WITH NoWait;
 
        /* Grab the most fragmented index first to defrag */
        SELECT TOP 1 
              @objectID         = objectID
            , @indexID          = indexID
            , @databaseID       = databaseID
            , @databaseName     = databaseName
            , @fragmentation    = fragmentation
            , @partitionNumber  = partitionNumber
            , @pageCount        = page_count
        FROM #indexDefragList
        WHERE defragStatus = 0
        ORDER BY fragmentation DESC;
 
        IF @debugMode = 1 RAISERROR('  Looking up the specifics for our index...', 0, 42) WITH NoWait;
 
        /* Look up index information */
        SELECT @updateSQL = N'Update idl
            Set schemaName = QuoteName(s.name)
                , objectName = QuoteName(o.name)
                , indexName = QuoteName(i.name)
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_id
            Inner Join ' + @databaseName + '.sys.schemas As s
                On o.schema_id = s.schema_id
            Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
                And i.type > 0
                And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10));
 
        EXECUTE SP_EXECUTESQL @updateSQL;
 
        /* Grab our object names */
        SELECT @objectName  = objectName
            , @schemaName   = schemaName
            , @indexName    = indexName
        FROM #indexDefragList
        WHERE objectID = @objectID
            And indexID = @indexID
            And databaseID = @databaseID;
 
        IF @debugMode = 1 RAISERROR('  Grabbing the partition count...', 0, 42) WITH NoWait;
 
        /* Determine if the index is partitioned */
        SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                    From ' + @databaseName + '.sys.partitions
                                    Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                                        And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
            , @partitionSQL_Param = '@partitionCount_OUT int OutPut';
 
        EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;
 
        IF @debugMode = 1 RAISERROR('  Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait;
 
        /* Determine if the table contains LOBs */
        SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
                            From ' + @databaseName + '.sys.columns With (NoLock) 
                            Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
                                And (system_type_id In (34, 35, 99)
                                        Or max_length = -1);'
                            /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
                , @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
 
        EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;
 
        IF @debugMode = 1 RAISERROR('  Building our SQL statements...', 0, 42) WITH NoWait;
 
        /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
        IF @fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1
        BEGIN
 
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                + @schemaName + N'.' + @objectName + N' ReOrganize';
 
            /* If our index is partitioned, we should always reorganize */
            IF @partitionCount > 1
                SET @sqlCommand = @sqlCommand + N' Partition = ' 
                                + CAST(@partitionNumber AS NVARCHAR(10));
 
        END;
 
        /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
        IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
        BEGIN
 
            /* Set online rebuild options; requires Enterprise Edition */
            IF @onlineRebuild = 1 And @editionCheck = 1 
                SET @rebuildCommand = N' Rebuild With (Online = On';
            ELSE
                SET @rebuildCommand = N' Rebuild With (Online = Off';
 
            /* Set processor restriction options; requires Enterprise Edition */
            IF @maxDopRestriction IS Not Null And @editionCheck = 1
                SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')';
            ELSE
                SET @rebuildCommand = @rebuildCommand + N')';
 
            SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                            + @schemaName + N'.' + @objectName + @rebuildCommand;
 
        END;
 
        /* Are we executing the SQL?  If so, do it */
        IF @executeSQL = 1
        BEGIN
 
            IF @debugMode = 1 RAISERROR('  Executing SQL statements...', 0, 42) WITH NoWait;
 
            /* Grab the time for logging purposes */
            SET @dateTimeStart  = GETDATE();
 
            /* Log our actions */
            INSERT INTO dbo.dba_indexDefragLog
            (
                  databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , dateTimeStart
            )
            SELECT
                  @databaseID
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart;
 
            SET @indexDefrag_id = SCOPE_IDENTITY();
 
            /* Execute our defrag! */
            EXECUTE SP_EXECUTESQL @sqlCommand;
            SET @dateTimeEnd  = GETDATE();
 
            /* Update our log with our completion time */
            UPDATE dbo.dba_indexDefragLog
            SET dateTimeEnd = @dateTimeEnd
                , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd)
            WHERE indexDefrag_id = @indexDefrag_id;
 
            /* Just a little breather for the server */
            WAITFOR Delay @defragDelay;
 
            /* Print if specified to do so */
            IF @printCommands = 1
                PRINT N'Executed: ' + @sqlCommand;
        END
        ELSE
        /* Looks like we're not executing, just printing the commands */
        BEGIN
            IF @debugMode = 1 RAISERROR('  Printing SQL statements...', 0, 42) WITH NoWait;
 
            IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!');
        END
 
        IF @debugMode = 1 RAISERROR('  Updating our index defrag status...', 0, 42) WITH NoWait;
 
        /* Update our index defrag list so we know we've finished with that index */
        UPDATE #indexDefragList
        SET defragStatus = 1
        WHERE databaseID       = @databaseID
          And objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
 
    END
 
    /* Do we want to output our fragmentation results? */
    IF @printFragmentation = 1
    BEGIN
 
        IF @debugMode = 1 RAISERROR('  Displaying fragmentation results...', 0, 42) WITH NoWait;
 
        SELECT databaseID
            , databaseName
            , objectID
            , objectName
            , indexID
            , indexName
            , fragmentation
            , page_count
        FROM #indexDefragList;
 
    END;
 
    /* Do we want to rebuild stats? */
    IF @rebuildStats = 1
    BEGIN
 
        /* Build our SQL statement to update stats */
        SELECT TOP 1 @rebuildStatsSQL = 'Use ' + databaseName + '; ' + 
                                        'Execute sp_updatestats;'
                , @rebuildStatsID = databaseID
        FROM #databaseList
        WHERE statsStatus = 0;
 
        SET @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL;
 
        IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
        /* Execute our stats update! */
        EXECUTE SP_EXECUTESQL @rebuildStatsSQL;
 
        /* Keep track of which databases have been updated */
        UPDATE #databaseList 
        SET statsStatus = 1
        WHERE databaseID = @rebuildStatsID;
 
    END;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    DROP TABLE #indexDefragList;
    DROP TABLE #databaseList;
    DROP TABLE #processor;
 
    IF @debugMode = 1 RAISERROR('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) WITH NoWait;
 
    SET NOCOUNT OFF;
    RETURN 0
END
Go
 
SET Quoted_Identifier OFF 
SET ANSI_Nulls ON
Go

------------------------------------------------------------

Very cool ;-)

Getting Started with jQuery in Visual Studio 2008

Very helpful..Thanks Guy. ;-)

...  

Getting Started with jQuery in Visual Studio 2008

In this post I’ll talk about adding jQuery IntellSense in Visual Studio 2008, and how to add jQuery to a simple Web Application inside Visual Studio 2008.

jQuery IntelliSense in Visual Studio 2008 - KB946581

In order to use jQuery in Visual Studio 2008 with IntelliSence a hotfix for Visual Studio 2008 must be installed:

1. Download the hotfix from Connect
2. Run the executable and extract its contents to a folder in your hard drive.
3. Make sure all instances of Visual Studio 2008 are closed, and run VS90-KB946581.exe from the above folder.

Download the Latest jQuery Library + Documentation

To use jQuery in Visual Studio 2008, and enjoy its IntelliSense, you should download 2 javascript files. One contains the actual jQuery library, and the second contains the library with documentation for Visual Studio 2008 to display its IntelliSense.

1. Go to jQuery Official Download Page
2. Scroll down and find the Current Release section.
3. Download the Uncompressed version (jquery-1.2.6.js) and the documentation for Visual Studio (jquery-1.2.6-vsdoc.js).

Using jQuery in a Visual Studio 2008 Web Application

In a new Web Application or inside an existing one, add the jQuery scripts into a certain folder.

In a web page (or a master page), add a reference to the jQuery library:

Then, in any javascript function you can start using jQuery functions and enjoy the IntelliSense in Visual Studio 2008.

A Simple Example of Using jQuery Functions

For example, Assuming that you have a page with the following content in it:

This form contains a single textbox followed by a button.

The handleButtonClick() function handles the button onclick event.

The way jQuery works is by selecting DOM elements and then doing something with them, such as executing a function or applying some properties. For example:

The above method uses the selector function $ to select DOM elements (in this case – a single element with id = txtName) and to apply a style property of a red border. Running this page and clicking the button results in this output:

Summary

In this post I talked about the steps you should follow in order to use jQuery in Visual Studio 2008 with InstelliSense support. Then, we used jQuery in a simple web application.

http://blogs.msdn.com/bursteg/archive/2009/06/05/getting-started-with-jquery-in-visual-studio-2008.aspx

C# for Kids ;-)

Hi :-)
 This is both funny and educational...Don't be shy.
As Sam points out below many of us learned on our own, I'm a big fan of that method.
. . .

Need help understanding C#, admit it, this isn’t easy

I have been working with software forever, ok, since I was 16, and I am 55 now.  So why ain’t I rich?  It’s a long and boring story, so stay focused…Oh wait a minute, I am pretty well off.  Software is the way to make money.  But you got to be willing to LEARN on your own and with your friends, schools can’t keep up.  If my discussion about DLLs was confusing, then take a look at this web site (I won’t tell): C# for Sharp Kids.  If you don’t understand how to use C#, you won’t be able to understand my discussions about XNA and programming mobile devices.

This is hosted completely on MSDN, and it is very well done online learning.  As you know from my writings on this blog, I bring up LINCOS from time to time, one of the early software languages that was designed as a language that could be used to communicate with aliens, space aliens that is.  This is the language that SCHEME is based on.

C# is based on another approach to software, one of the designers is Erik Meijer who is a really funny person, who is fun to hang around with.  He helped create the C# language.

The XNA Game Studio requires that you use C# to be able to use the XNA templates and to for your code to be loaded on the XBox 360.

As to classes and DLLs, please take a look at the C# for Sharp Kids link, I will start going over the DLLs at a high level, but want to make sure that you are up to speed with what a class is.  I will review what abstract classes, base classes and how to use them.

http://blogs.msdn.com/devschool/archive/2009/06/05/need-help-understanding-c-admit-it-this-isn-t-easy.aspx

PowerShell and SQL Server 2008

The Msft Scripting Guys rock! ;-)

First of all. . .Did you know you can Email your Microsoft Scripting questions to them? -> scripter@microsoft.com, Subject: "Hey, Scripting Guy!"

Additional Resources

Now to the topic at hand. . .

How Does Windows PowerShell Make It Easier to Work with SQL Server 2008?

Hey, Scripting Guy! I am a SQL database administrator, and in the past I have enjoyed using VBScript for many tasks related to my computers that are running SQL Server. What capabilities does Windows PowerShell provide to make working with SQL Server 2008 easier?

- OS

Hi OS,

Ed is neck deep in work for the 2009 Summer Scripting Games. He is sipping a cup of Prince of Wales tea, and listening to some Bach on his Zune (yes, we also suspect Ed gets paid a small remuneration each time he mentions his Zune). Ed has been re-reading his heavily worn limited edition of The Adventures of Tom Sawyer recently (not sure if that fact is relevant or not).

Because Ed does not consider himself to be a SQL Server 2008 guru, he decided to pass this question off to Buck Woody, SQL Server Technical Specialist for the Microsoft Corporation. He is a former Program Manager on the SQL Server team and the author of seven books about SQL Server. (Compared to Buck, Ed cannot even find SQL in the dictionary.)

Yesterday, we explained that Windows PowerShell is a shell, a set of new commands, and a way to script out tasks. But database assistants are a practical lot and demand more. They already have several tools to work with SQL Server. Why learn an additional tool? Because Windows PowerShell does more than just let you type commands into SQL Server. You have the whole Server Management Objects (SMO) library at your disposal—the same library that SQL Server Management Studio uses. Anything that you can imagine doing in SQL Server Management Studio, you can do with Windows PowerShell—in a script.

Let us start where we left off last time and see what we can do with this newly found power. Remember that we learned how to load the SMO libraries, and then we learned how to connect to a server and reference it as a variable:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

Now we have a connection to a SQL Server object, and we can do a lot with that simple variable. We can see all the actions we can perform (known as methods) and the attributes we can list or set (known as properties) of the database object by using variables, piping, and the commands from Windows PowerShell:

$sqlServer | 
get-member | 
MORE

As you page down the screen, you might notice a property or two that would be useful to have. One of them is the number of processors installed and usable by SQL Server. With the SQL Server Instance declared as a variable, we can just ask for that by putting a period after the server name and then adding the property we want to see:

$sqlServer.Processors

That is very cool. We can ask about other properties additionally, such as whether the Instance is running:

$sqlServer.Status

To this point, we have been working with the SQL Server Instance. However, because this is the SMO library, we actually have access to almost everything in SQL Server. Let us say that you are interested in the databases on the Instance. This is somewhat different from what we have done already. You see, the things we have asked for have all been a single value—a property on the Instance in this case. The SMO library has not only a single object such as an Instance that you can work with. But those objects can hold other objects. The Status property we asked for is an example of that. But some objects have multiple children underneath them called collections, and you can tell them by their names. The objects that hold even more objects have an “s” at the end of the name, at least most of the time (Processors is an exception, for example). The databases that the Instance holds are a collection because there are other databases underneath it.

To access the objects in a collection, we can use brackets and the number that refers to it, such as this:

$sqlServer.Databases[0].Size

That is somewhat useful, but there is no real way to tell which database this is. Luckily, Windows PowerShell can also take the name of the database in quotation marks for a more friendly display, such as this:

$sqlServer.Databases[“Adventureworks”].Size

That is better. And what can we see about databases, or any object, for that matter? Well, we can turn back to our old friend, Get-Member:

$sqlServer.Databases | get-Member

But what if an object holds even more objects? For example, an Instance has databases, which have tables. How can we see a property there? Simple. We just keep adding those child objects. In this case, even a table is a collection because it holds columns and so on. Here is an example. Let’s discover how many rows are on a table:

$sqlServer.Databases[“Adventureworks”].Tables[“AWBuildVersion”].RowCount

(By the way, this command works because my default schema is dbo together with this table. More about how to browse other schemas later)

But let us say we want to go even further. We would like to examine all the tables and show the row counts. For that, we will have to use another Windows PowerShell feature: functions. A function in Windows PowerShell is exactly like functions in other languages: It is just the name of the function followed by some parameters usually wrapped in a pair of parentheses. In this case, we want to get information about a group of items. We must have a function that reads a group of items and does work on each item in the group. In Windows PowerShell, we have the foreach function. This is followed by any instructions we want.

The interesting thing is that Windows PowerShell does not require us to set up a variable to walk through the list of items like many other languages. Here is what the format of that command resembles:

forEach (BrandNewVariable in SetOfObjects) {do some work} 

Let us put that to work. We’ll use the forEach() function together with our database reference variable ($sqlServer), the AdventureWorks database, and the tables object to list out all the names:

forEach ($table in $sqlServer.Databases["AdventureWorks"].Tables) {$table.Name}

Now let us add the row counts to the previous command. To do this, we query the rowcount property from the table object:

forEach ($tables in $sqlServer.Databases["AdventureWorks"].Tables)`
 {$tables.Name + ": " + $tables.RowCount}

I think that you have the idea. We have now created a variable connection to a SQL Server object or two, and we can do a lot with that simple variable. We can see all the actions we can perform with that Get-Member cmdlet:

$sqlServer.Databases | get-member | MORE

We have been playing around with the properties on the object. So far, we have been just reading them. But many properties can be set equally. This we can see in the second column of the output. Let us change one of the properties on a sample database. Let us change the AutoShrink setting. In this example, we will see the current value, turn it on and then off again:

Note: We cannot tell you how important it is to do this on a testing database, on a testing system. This test system must be something that you do not mind losing, and that no one else is using. No, this should not hurt anything, but our houses should be worth what we paid for them. But they are not. So stop if you are not on a test system.

$sqlServer.Databases["AdventureWorks"].AutoShrink
$sqlServer.Databases["AdventureWorks"].AutoShrink = 1
$sqlServer.Databases["AdventureWorks"].AutoShrink
$sqlServer.Databases["AdventureWorks"].AutoShrink = 0
$sqlServer.Databases["AdventureWorks"].AutoShrink

Of course, this is a fairly simple thing to do. Set a property here and there. How about something that is somewhat more interesting, such as scripting a database? First, let us see whether there is a method (an action) we can perform on the database that sounds somewhat like scripting:

$sqlServer.Databases | 
get-member | 
MORE

There it is in the S’s: Script! Could it be this simple?

$sqlServer.Databases[“AdventureWorks”].Script()

Now, that is just too cool. Hey—we wonder if we could script out multiple databases. Let us use that same loop we tried earlier and see whether we can use it to script out all the databases:

forEach ($database in $sqlServer.Databases) {$database.Script()}

But of course, we need a way to save all this. We can use another cmdlet that is known as Out-File. That cmdlet creates a file on the hard disk, and it has several switches that you can see with the Get-Help cmdlet. All we have to do is add a piping command to the “work” section of our loop, and then we can send each object, one at a time, to a file. Here is an example that bundles up all the database scripts into a single file that is named c:\temp\Scripts.sql:

forEach ($database in $sqlServer.Databases) {$database.Script() |`
 out-File –Filepath c:\temp\Scripts.sql -Append}

But let us say we want to retrieve all the tables in a particular database scripted, instead of just the database itself. We can use another loop to do that. (Warning, this might take some time to run! You can press CTRL+C if you want to stop the script.)

forEach ($tables in $sqlServer.Databases["AdventureWorks"].Tables)`
 {$tables.Script()}

All we are doing there is walking through the table objects on the database and sending them to the scripting object. But moving through the SMO object model does have some challenges. As you work through these examples, you will undoubtedly encounter them. Can Microsoft just make this a bit easier? Sure, we can. And next time we will show you what we have done. We cannot wait!

Once again, Buck has hit a home run. Oh, yeah, Ed's Bach prelude is complete, and his tea pot is empty. He has written an absolutely dastardly event for the 2009 Summer Scripting Games. He smiles a crooked little grin and does a passable imitation of Dr. Evil. The Summer Scripting Games will be awesome. Thank you for writing, OS, and join us tomorrow as Windows PowerShell with SQL Server 2008 Week continues. Until then, stay safe. For the latest information about the Summer Scripting Games, follow us on Twitter.

Ed Wilson and Craig Liebendorfer, Scripting Guys
http://www.microsoft.com/technet/scriptcenter/resources/qanda/may09/hey0527.mspx

Woot! -> IIS Search Engine Optimization (SEO) Toolkit

Wow...This is very cool, thanks Joe ;-)

Announcing the IIS SEO Toolkit (beta)

Available for free download today !

Optimize your web sites for Search Engines !

Download the New IIS SEO Toolkit Beta

Today, we are announcing the IIS Search Engine Optimization (SEO) Toolkit Beta – a brand new free toolkit that helps Web developers, hosting providers, and server administrators improve their sites’ relevance in search results by recommending how to make them more search engine-friendly. The SEO Toolkit Beta is available for installation via the Microsoft Web Platform Installer 2.0 Beta at  http://www.microsoft.com/web/downloads/platform.aspx

The IIS SEO Toolkit can:

- Improve the volume and quality of traffic to Web site from search engines

- Control how search engines access and display Web content

- Inform search engines about locations that are available for indexing

The IIS SEO Toolkit includes three modules that integrate with IIS Manager: Site Analysis, which suggests changes that can help improve the volume and quality of traffic to your Web site from search engines; Robots Exclusion, which makes it easier to control and restrict the content that search engines index and display; and Sitemaps and Site Indexes, which can help inform search engines about locations that are available for indexing. The IIS SEO Toolkit Beta can be installed with the Microsoft Web Platform Installer 2.0 Beta for use with IIS 7.0 and IIS 7.5.

The IIS SEO Toolkit Beta is a piece of the larger Microsoft Web Platform strategy, which enables developers and end-users to build great websites, experiences, and achieve success in the Web ecosystem.

For more information visit: http://www.iis.net/extensions/SEOToolkit

http://blogs.iis.net/joestagner/archive/2009/06/03/announcing-the-iis-seo-toolkit-beta.aspx

Note: Here's something new and cool Brian posted too:

new IIS Manager for remote administration

It’s a bit strange that my “back-to-blogging” post is about a new version of Remote Manager considering that my last post, way back in March 2008 was a Remote Manager post as well (I have a good reason for not blogging since then which you’ll see at the end of this post :-)

What’s different?

The new version has some internal changes but the major new feature is the ability to remotely manage IIS 7 on Windows Server 2008 R2 machines as well as Windows Server 2008 machines. Another additional is the ability to use the tool from Windows 7.

Other then these changes everything is the same as before.

How do I get it?

Via Web PI:

The IIS.NET download center:

• IIS Manager for Remote Administration (x86)
• IIS Manager for Remote Administration (x64)

Microsoft.com download center:

• Internet Information Services (IIS) Manager

What happened to Brian Delahunty’s Ramblings?

So, the reason that I haven’t blogged since my last post on the original Remote Manager is pretty straight forward – I changed position in Microsoft back in March 08 from a Program Manager on the IIS team to a Software Design Engineer on the IIS team and have been busy working on projects such as Web Deployment Tool, Database Manager, FTP 7.5, Smooth Streaming, Administration Pack, Advanced Logging, Dynamic IP Restrictions, and more, and of course on IIS 7.5 on Windows 7/Windows Server 2008 R2. More entries to come soon :-)

http://blogs.iis.net/bdela/archive/2009/06/03/new-iis-manager-for-remote-administration.aspx

Get started with VS2010

Get trained on Visual Studio 2010 and .NET Framework 4.0 – Today!

eek! If you missed it, the team that brought you the other cool training kits, has a new version for the beta of Visual Studio 2010 and the .NET Framework 4.0.

28 Presentations, 13 demos and 16 hands on labs with topics ranging from Data Access to Parallel Computing.

Get the training kit today and get ahead!

http://blogs.msdn.com/allandcp/archive/2009/06/01/get-trained-on-visual-studio-2010-and-net-framework-4-0-today.aspx

___________________________________________________________________________________________________

Web Deployment: Web.Config Transformation

We have earlier discussed about Web Deployment and Web Packaging quite a bit, today I wanted to dive into web.config transformation. If you would like to check out the other topics please read through the earlier blog posts below:

• Web Deployment with VS 2010 and IIS
• Web Packaging: Creating a Web Package using VS 2010

   

• Web Packaging: Creating web packages using MSBuild

   

• How does Web Deployment with VS 10 & MSDeploy Work?

   

• Installing Web Packages using Command Line

   

Usually web applications go through a chain of server deployments before being finally being deployed to production environment. Some of these environments can be Developer box (Debug), QA Server, Staging/Pre-Production, Production (Release). While transitioning between these environments various settings of the web application residing in web.config file change, some of these settings can be items like application settings, connection strings, debug flags, web services end points etc.

VS10’s new web.config transformation model allows you to modify your web.config file in an automated fashion during deployment of your applications to various server environments. To help command line based deployments, Web.Config transformation is implemented as an MSBuild task behind the scene hence you can simply call it even outside of deployment realm.

I will try to go through below steps to explain web.config transformation in detail

1. Creating a “Staging” Configuration on your developer box

    

2. Adding a “Staging” Web.Config Transform file to your project

    

3. Writing simple transforms to change developer box connection string settings into “Staging” environment settings

    

4. Generating a new transformed web.config file for “Staging” environment from command line

    

5. Generating a new transformed web.config file for “Staging” environment from VS UI

    

6. Understanding various available web.config Transforms and Locators

    

7. Using Web.config transformation toolset for config files in sub-folders within the project

    

Step 1: Creating a “Staging” Configuration on your developer box

Debug and Release build configurations are available by default within Visual Studio but if you would like to add more build configurations (for various server environments like “Dev”, “QA”, “Staging”, “Production” etc then you can do so by going to the Project menu Build --> Configuration Manager… Learn more about creating build configurations.

Step 2: Adding a “Staging” Web.Config Transform file to your project

One of the goals while designing web.config transformation was to make sure that the original runtime web.config file does not need to be modified to ensure that there would be no performance impacts and also to make sure that the design time syntax is not mixed with runtime syntax. To support this goal the concept of Configuration specific web.config files was introduced.

These web.config files follow a naming convention of web.configuration.config. For example the web.config files for various Visual Studio + Custom configurations will look as below:

Any new Web Application Project (WAP) created in VS10 will by default have Web.Debug.config and Web.Release.config files added to the project. If you add new configurations (e.g. “Staging”) or if you upgrade pre-VS10 projects to VS10 then you will have to issue a command to VS to generate the Configuration specific Transform files as needed.

To add configuration specific transform file (e.g. Web.Staging.Config) you can right click the original web.config file and click the context menu command “Add Config Transforms” as shown below:

On clicking the “Add Config Transform” command VS10 will detect the configurations that do not have a transform associated with them and will automatically create the missing transform files. It will not overwrite an existing transform file. If you do not want a particular configuration transform file then you can feel free to delete it off.

Note: In case of VB Web Application Projects the web.configuration.config transform files will not be visible till you enable the hidden file views as shown below:

The transform files are design time files only and will not be deployed or packaged by VS10. If you are going to xCopy deploy your web application it is advised that you should explicitly leave out these files from deployment just like you do with project (.csproj/.vbproj) or user (.user) files…

Note: These transform files should not be harmful even if deployed as runtime does not use them in any fashion and additionally ASP.NET makes sure that .config files are not browsable in any way.

Step 3: Writing simple transforms to change developer box connection string settings into “Staging” environment settings

Web.Config Transformation Engine is a simple XML Transformation Engine which takes a source file (your project’s original web.config file) and a transform file (e.g. web.staging.config) and produces an output file (web.config ready for staging environment).

The Transform file (e.g. web.staging.config ) needs to have XML Document Transform namespace registered at the root node as shown below:

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
</configuration>

Note: The transform web.config file needs to be a well formed XML.

Inside the XML-Document-Transform namespace two new attributes are defined. These attributes are important to understand as they drive the XML Transformation Engine.

Transform – This attribute inside the Web.Staging.config informs the Transformation engine the way to modify web.config file for specific configuration (i.e. staging). Some examples of what Transforms can do are:

• Replacing a node

   

• Inserting a node

   

• Delete a node

   

• Removing Attributes

   

• Setting Attributes

   

Locator – This attribute inside the web.staging.config helps the Transformation engine to exactly pin-point the web.config node that the transform from web.staging.config should be applied to. Some examples of what Locators can do are:

• Match on value of a node’s attribute

   

• Exact XPath of where to find a node

   

• A condition match to find a node

   

Based on the above basic understanding let us try to transform connection string from original web.config file to match Staging environment’s connection string

Let us examine the original web.config file and identify the items to replace... Let’s assume that the original Web Config file’s connection string section looks as below:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <connectionStrings>
    <add name="personalDB"
     connectionString="Server=DevBox; Database=personal; User Id=admin; password=P@ssw0rd" providerName="System.Data.SqlClient" />
    <add name="professionalDB"
     connectionString="Server=DevBox; Database=professional; User Id=admin; password=P@ssw0rd" providerName="System.Data.SqlClient" />
</connectionStrings>
....
....
</configuration>

NOTE: It is not advisable to keep connection string unencrypted in the web.config file, my example is just for demonstration purposes.

Let us assume that we would like to make following changes to web.config file when moving to staging environment

• For “personalDB” we would like to change the connectionString to reflect Server=StagingBox, UserId=admin, passoword=StagingPersonalPassword”

   

• For “professionalDB” we would like to change the connectionString to reflect Server=StagingBox, UserId=professional, passoword=StagingProfessionalPassword”

   

To make the above change happen we will have to open web.Staging.Config file and write the below piece of code

<?xml version="1.0"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
     <connectionStrings>
        <add name="personalDB"
          connectionString="Server=StagingBox; Database=personal; User Id=admin; password=StagingPersonalPassword"
          providerName="System.Data.SqlClient" xdt:Transform="Replace" xdt:Locator="Match(name)" />
        <add name="professionalDB"
         connectionString="Server=StagingBox; Database=professional; User Id=professional; password=StagingProfessionalPassword"
         providerName="System.Data.SqlClient" xdt:Transform="Replace" xdt:Locator="Match(name)"/>
       </connectionStrings>
</configuration>

The above syntax in web.staging.config has Transform and Locator attributes from the xdt namespace. If we analyze the connection string node syntax we can notice that the Transform used here is “Replace” which is instructing the Transformation Engine to Replace the entire node

Further if we notice the Locator used here is “Match” which is informing Transformation engine that among all the “configuration/connectionStrings/add” nodes that are found, pick up the node whose name attribute matches with the name attribute of <add> node in web.Staging.config.

Also if you notice web.Staging.config does not contain anything else but the connectionStrings section (i.e. it does not have <system.web> and various other sections that web.config file usually has, this is because of the fact that the Transformation Engine does not require a complete web.config file in web.staging.config. It does the merging for you thus saving you duplication of all the rest of the sections in web.config file.

Simplest Approach: If you do not mind replicating the entire web.config file in web.staging.config then you can certainly do so by copying the entire web.config content into web.staging.config and change the relevant nodes inside web.staging.config. In such a situation you will just have to put xdt:Transform="Replace" attribute on the topmost node (i.e. configuration) of web.staging.config. You will not need xdt:Locator attribute at all as you are replacing your entire web.config file with web.staging.config without Matching anything.

So far we have seen one Transform (i.e. Replace) and one Locator (i.e. Match), we will see various other Transforms and Locators further in the post but first let us understand how we can produce the Transformed web.config file for the Staging environment after using original web.config and web.staging.config.

Step 4: Generating a new transformed web.config file for “Staging” environment from command line

Open Visual Studio Command prompt by going to Start --> Program Files –> Visual Studio v10.0 –> Visual Studio tools –> Visual Studio 10.0 Command Prompt

Type “MSBuild “Path to Application project file (.csproj/.vbproj) ” /t:TransformWebConfig /p:Configuration=Staging" and hit enter as shown below:

Once the transformation is successful the web.config for the “Staging” configuration will be stored under obj -->Staging folder under your project root (In solution explorer you can access this folder by first un-hiding the hidden files) :

• In the solution explorer click the button to show hidden files
• Open the Obj folder

   

• Navigate to your Active configuration (in our current case it is “Staging”)

   

• You can find the transformed web.config there

   

You can now verify that the new staging web.config file generated has the changed connection string section.

Step 5: Generating a new transformed web.config file for “Staging” environment from VS UI

Right Click on your project and click Package –> Create Package

The Create Package step already does web.config transformation as one of its intermediate steps before creating a package and hence you should be able to find the transformed web.config file in the same place as described in Step 4

Step 6: Understanding various available web.config Transforms and Locators

xdt:Locators

The inbuilt xdt:Locators are discussed below.

• Match - In the provided syntax sample below the Replace transform will occur only when the name Northwind matches in the list of connection strings in the source web.config.Do note that Match Locator can take multiple attributeNames as parameters e.g. Match(name, providerName) ]

   

<connectionStrings>
     <add name="Northwind" connectionString="connectionString goes    here" providerName="System.Data.SqlClient" xdt:Transform="Replace" xdt:Locator="Match(name)" />
</connectionStrings>

·         Condition - Condition Locator will create an XPath predicate which will be appended to current element’s XPath. The resultant XPath generated in the below example is “/configuration/connectionStrings/add[@name='Northwind or @providerName=’ System.Data.SqlClient’ ]”

This XPath is then used to search for the correct node in the source web.config file

<connectionStrings>
      <add name="Northwind" connectionString="connectionString goes here" providerName="System.Data.SqlClient" xdt:Transform="Replace" xdt:Locator="Condition(@name=’Northwind or @providerName=’System.Data.SqlClient’)" />
</connectionStrings>

·         XPath- This Locator will support complicated XPath expressions to identify the source web.config nodes. In the syntax example we can see that the XPath provided will allow user to replace system.web section no matter where it is located inside the web.config (i.e. all the system.web sections under any location tag will be removed.)

<location path="c:\MySite\Admin" >
    <system.web xdt:Transform="RemoveAll" xdt:Locator="XPath(//system.web)">
    ...
    </system.web>
</location>

xdt:Transform

• Replace - Completely replaces the first matching element along with all of its children from the destination web.config (e.g. staging environment’s web.config file). Do note that transforms do not modify your source web.config file.
  <assemblies xdt:Transform="Replace">
      <add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089" />
  </assemblies>

   

·         Remove - Removes the first matching element along with all of its children
<assemblies xdt:Transform="Remove"></assemblies>

·         RemoveAll - Removes all the matching elements from the destination’s web.config (e.g. staging environment’s web.config file).

<connectionStrings>
    <add xdt:Transform="RemoveAll"/>
</connectionStrings>

·         Insert - Inserts the element defined in web.staging.config at the bottom of the list of all the siblings in the destination web.config (e.g. staging environment’s web.config file).

<authorization>
     <deny users="*" xdt:Transform="Insert"/>
</authorization>

·         SetAttributes - Takes the value of the specified attributes from the web.staging.config and sets the attributes of the matching element in the destination web.config. This Transform takes a comma separated list of attributes which need to be set. If no attributes are given to SetAttributes transform then it assumes that you would like to Set all the attributes present on the corresponding node in web.staging.config
<compilation batch="false"xdt:Transform="SetAttributes(batch)">

   …

   </compilation>

·         RemoveAttributes - Removes the specified attributes from the destination web.config (i.e. staging environment’s web.config file). The syntax example shows how multiple attributes can be removed.

<compilation xdt:Transform="RemoveAttributes(debug,batch)">
</compilation>

• InsertAfter (XPath) - Inserts the element defined in the web.staging.config exactly after the element defined by the specified XPath passed to “InsertAfter()” transform. In the syntax example the element <deny users="Vishal" />will be exactly inserted after the element <allow roles="Admins" /> in the destinationXML.

   

<authorization>
     <deny users="Vishal" xdt:Transform="InsertAfter(/configuration/system.web/authorization/allow[@roles='Admins'])” />

</authorization>

• InsertBefore (XPath) - Inserts the element defined in the web.staging.config exactly before the element defined by the specified XPath passed to “InsertBefore()” transform. In the syntax example the element <allow roles="Admins" />will be exactly inserted before the element <deny users="*" />in the destinationXML.

   

<authorization>
      <allow roles=" Admins" xdt:Transform="InsertBefore(/configuration/system.web/authorization/ deny[@users='*'])" />
</authorization>

Some advanced points to note:

• If the Transformation Engine does not find a xdt:Transform attribute specified on a node in web.staging.config file then that node is ignored for Transformation and the Tranformation engine moves ahead traversing the rest of the web.staging.config.

   

• A xdt:Transform attribute on a parent can very easily impact child elements even if there is no Transform specified for child e.g. If xdt:Transform=”Replace” is put on <system.web> then everything underneath <system.web> node will be replaced with the content from web.staging.config

   

• It is completely valid to place xdt:Locators attributes on arbitrary nodes inside web.staging.config just for filtering purposes. xdt:Locator does not need to be accompanied with xdt:Transform attribute. (great example here is <location> tag which might just be used for filtering… The example code here would be:

   

<location path="c:\MySite\Admin" xdt:Locator="Match(path)">>
        <system.web>
          ... Bunch of transforms written under here will
          .... only apply if location path = C:\MySite\Admin
        </system.web>
</location>

Step 7: Using Web.config transformation toolset for config files in sub-folders within the project

All of the above discussion directly applies to any web.config file present in sub folders of your project (e.g. if you have a separate web.config file for say “Admin” folder then VS 10 will support transforms for them too). You can add transform files within sub-folders and use the same packaging functionality mentioned in all of the above steps to create transformed web.config files for web.config files specific to the sub folders within your project.

I think this has become a rather long post; but I hope it helps!!

Vishal R. Joshi | Program Manager | Visual Studio Web Developer

http://blogs.msdn.com/webdevtools/archive/2009/05/04/web-deployment-web-config-transformation.aspx

http://blogs.msdn.com/webdevtools/archive/2009/06/01/free-web-hosting-to-try-asp-net-4-beta1-vs-2010-beta1-and-ms-web-deployment-tool-rc1.aspx