I just went through the process of integrating Visual SourceSafe with Visual Studio 2005.  A new version of Visual SourceSafe, Visual SourceSafe 2005, is packaged with the Team Edition of Microsoft Visual Studio 2005.  The bad news is that Visual Studio 2005 does not support integration with Visual SourceSafe 6.0, and furthermore, you cannot install Visual SourceSafe 6.0 side-by-side with Visual SourceSafe 2005.  However, the good news is that legacy versions of Microsoft Visual Studio, including Visual Studio 6.0, will integrate with the new version of SourceSafe ...

The installation process was a little tricky on my laptop.  Here are the steps I took:

1.  I uninstalled Microsoft Visual SourceSafe 6.0 via Add/Remove Programs.  This apparently was successful, yet my SourceSafe icon was still installed on my start menu and the application still worked,

2.  I ran Add/Remove Programs on my instance of Microsoft Visual Studio 6.0 and deselected the Visual SourceSafe option.  This was successful, but again, SourceSafe 6.0 was still installed and working,

3.  I went to the /Program Files/Microsoft Visual Studio/Common Files/VSS folder and unregistered all the DLLs in that folder.  Then, I deleted the folder.  I received no warning messages about applications or components being registered or installed,

4.  I installed Visual SourceSafe 2005 from my Visual Studio 2005 Team Edition for Developers disc,

5.  I opened Visual Studio 2005 and selected Microsoft Visual SourceSafe as my installed plug-in from the Tools, Options menu.

To my surprise, not only was Visual Studio 2005 integrated with this new version of SourceSafe, but my Visual Studio 6.0 environment (as well as my Visual Studio 2003) was as well ...

I've received a lot of interest around a previous entry I made about transforming a .NET DataSet to an Excel workbook.  While I did mention that it was possible to transform a DataSet with multiple tables to a workbook with multiple worksheets, my previous example did not demostrate this - it focused on the handling of the DateTime data type in Excel ...

Attached is a zip file that demonstrates this.  The contained stored procedure can be executed against your copy of the Pubs database, just code your connection string accordingly ...

MultiWorksheet.zip (2.02 KB)

I ran the SQL Server 2005 Upgrade Advisor against a test database that I am currently working with.  I'd say it's a pretty typical SQL Server 2000 database: lots of complicated stored procedures, user-defined functions, triggers, etc.  Here are the issues that the upgrade advisor reported:

1. Remove references to undocumented system tables
2. Outer join operators *= and =* are not supported in 90 compatiblilty mode
3. Non-integer constants are not allowed in the ORDER BY clause in 90 compatibility mode
4. Changes to behavior in syslockinfo and sp_lock
5. Other Database Engine upgrade issues

Obviously, #5 bears looking into more.  #2 is the result of that old outer join syntax that I learned when I was working with Sybase way back when.  [I guess I'm really going to have to commit to using the OUTER JOIN clause ...]

None of these issues would prevent the database from being upgraded, or cause the upgrade to fail.  However, they would definitely need to be addressed before going to production (particularly #2 and #3) ...

I've installed SQL Server 2005 on my development machine (which already has the Developer Edition of SQL Server 2000 installed) and the process very smoothly.  My old SQL Server 2000 instance was named the "default" instance, meaning that I had to create a named instance for SQL Server 2005.  Other than that, the installation process went without a hitch ...

One nice feature is that the new SQL Server Management Studio allows me to connect to both versions of SQL Server and browse a list of database objects both in the SQL Server 2000 and SQL Server 2005 databases.  Presumably, it can connect to SQL Server 7 databases as well ...

In a recent post, I talked about quickly transforming a .NET DataSet to Excel XML format using an XSL transform.  One of the problems I encountered is Excel's handling of the DateTime data type.  DataSets serialize a DateTime data type in UTC format.  Excel does not recognize this format; specifically, it does not handle the time zone information that is part of the UTC format ...

To solve this problem, I created an XSL extension object that implements a custom routine to strip the trailing ten characters of time zone information.  Here's a code sample:

        private void button1_Click(object sender, System.EventArgs e)
        {
            // Get a DataSet ...
            string conn = "<your connection string here ...>";
            DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text,
                "select getdate() as ADate, 1.3 as ANumber1, 2.7 as ANumber2, 'Hello world' as AString");

            // Load the DataSet in an XmlDataDocument ...
            XmlDataDocument xmlDoc = new XmlDataDocument(ds);

            // Load the transform ...
            XslTransform xslTran = new XslTransform();
            xslTran.Load("transform.xslt");

            // Add the DateConverter extension object so we can call the FormatDateTime()
            // method from the transform ...
            DateConverter dc = new DateConverter();
            XsltArgumentList args = new XsltArgumentList();
            args.AddExtensionObject("urn:my-scripts", dc);

            // Create an XmlTextWriter for the output Excel workbook ...
            XmlTextWriter tw = new XmlTextWriter("test.xls", Encoding.UTF8);
            tw.Formatting = Formatting.Indented;
            tw.Indentation = 3;

            // Transform the XmlDataDocument ...
            tw.WriteStartDocument();
            xslTran.Transform(xmlDoc, args, tw, null);
            tw.Close();

        }
    }

    public class DateConverter
    {
        public DateConverter() {}

        // Excel cannot handle DateTime in UTC format. We'll simply strip
        // the last 10 characters that contain the time zone info ...
        public string FormatDateTime(string data)
        {
            return data.Remove(23, 10);
        }
    }

To run this sample, create a Windows Forms application with a button.  Use the button click handler and DateConverter class shown above.  The following are the contents of a "transform.xslt" file that you put in the same directory as your executable:

<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:custom="urn:my-scripts" >

<xsl:template match="NewDataSet">

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">

        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s21">
                <Font ss:Bold="1"/>
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            </Style>
            <Style ss:ID="s22">
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            </Style>
            <Style ss:ID="s23">
                <NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(@_)"/>
            </Style>
            <Style ss:ID="s24">
                <NumberFormat ss:Format="Short Date"/>
            </Style>
        </Styles>

        <Worksheet ss:Name="My Worksheet">
            <Table ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
                <xsl:attribute name="ss:ExpandedRowCount" >
                    <xsl:value-of select="count(Table)+1"/>
                </xsl:attribute>

                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>

                <Row>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ADate</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">AString</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ANumber1</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ANumber2</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">Sum</Data>
                    </Cell>
                </Row>

                <xsl:apply-templates select="Table"/>

            </Table>
        </Worksheet>
    </Workbook>
</xsl:template>
    
<xsl:template match="Table">
    <Row>
        <Cell ss:StyleID="s24">
            <Data ss:Type="DateTime">
                <xsl:value-of select="custom:FormatDateTime(ADate)"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s22">
            <Data ss:Type="String">
                <xsl:value-of select="AString"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23">
            <Data ss:Type="Number">
                <xsl:value-of select="ANumber1"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23">
            <Data ss:Type="Number">
                <xsl:value-of select="ANumber2"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23" ss:Formula="=SUM(RC[-2]:RC[-1])">
            <Data ss:Type="Number" />
        </Cell>
    </Row>
</xsl:template>

</xsl:stylesheet>

Note the use of the "urn:my-scripts" namespace which invokes the FormatDateTime() method of the DateConverter class in the transform ...

If you've been redirected to this blog from its old location at http://georgew.typepad.com/dotnet/ - Welcome!

I will continue to keep the old site running for a while longer, but will make all future posts here.  I've also migrated all the posts from the old location to the new.  I hope you enjoy the "portal" look and feel!

We currently have the need to export the contents of a .NET DataSet (containing several DataTables) to an Excel workbook containing several worksheets.  There are a couple of ways to do this:

1.   Use the Excel object model to programmatically create an instance of Excel, create an Excel Workbook with several Worksheets, iterate over the DataRows in each DataTable, and insert the results in each Cell.  Along the way, you can also apply the appropriate cell and number formatting to the cells.  This technique is very serviceable, but also very slow.  If you have a DataTable of any size at all (even 100 rows), the performance may be unacceptable ...

2.  The second techique involves serializing the DataSet using an XmlTextWriter, and apply an XSL Transform to the result to transform the XML to the Excel format.  This technique is at least an order of magnitude faster, while providing the ability to create multiple datasheets within a single workbook and handle cell formatting ...

I'll post a specific example here within the next few days ...

I mentioned in a previous post (which I posted from the PDC in September) that RSS support will become ubiquitous in future Microsoft releases: Office 12, IE, Vista, etc.  Here are some interesting things you can do with RSS that you may have never considered ...

I installed both Visual Studio 2005 Team Edition, and SQL Server 2005 Developer edition on my production laptop running Windows XP SP2.  Both installed without a hitch!  I installed Visual Studio 2005 first, choosing to install everything except the Express Edition of SQL Server 2005.  Then, I installed SQL Server 2005, again, choosing to do a full install ...

Now I have a production environment that includes VS 2005, VS 2003, and VS6, all peacefully coexisting.  On the database side, SQL Server 2000 and SQL Server 2005 are installed side-by-side as well ...

There's a know installation issue with installing Visual Studio 2005 from DVD (or any other software, for that matter) in a Virtual PC environment.  The CD mounting software that's provided with Virtual PC only supports 2.2 GB of data ...

The workaround is to either install from CDs, or copy the contents of the DVD to the local hard drive and share it with the Virtual PC ...

The long-awaited production versions of SQL Server 2005 and Visual Studio 2005 have been posted on the MSDN Subscription site and are available for download ...

I have a machine in my personal lab that consistently "blue screens" at some time after booting into Windows Server 2003.  It appears to be either disk or memory related.  I found a great tip on Ed Bott's blog about a Windows Memory Diagostic tool that is downloadable from Microsoft.  It is very easy to use; you download the executable and use it to build a bootable floppy or CD.  When you boot the machine, the diagnostic program runs several tests (there are "simple test" and "advanced test" options) on the installed memory chips ...

Unfortunately, it did not find any problems with the installed memory, so I guess it is "back to the drawing board" ...