Update 'GDR 3068 for SQL Server Database Services 2005 ENU (KB948109)' could not be installed. Error code 1603.

I noticed that my local SQL Server (2005, Standard Edition) was not started every day when I came to work, so I checked the event log, and found the error message from the title of this posting. Seems that there is a bug somewhere that prevents an update from being installed. There is a workaround at this address: http://support.microsoft.com/kb/925976 .

SandcastleGUI

I started using Sandcastle just a few days ago, and it was not very userfriendly, since it is a collection of command line utilities. Hence, I started looking for a GUI for Sandcastle, and found one at: http://www.inchl.nl/SandcastleGUI .

It's quite easy to use, and I was quite happy with it. I then started to create a web with documentation for one of my projects, and ran into some problems. It seems Sandcastle has problems with some long names, resulting in linebreaks in a file called "filetitles.js". This results in a javascript error saying something about unterminated string constants.

Thats why I created a small utility for fixing that file: FixFiletitlesJs.exe. Here's the code for my little utility (Console application):

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;

namespace FixFileTitlesJs
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length == 0 || (args.Length > 0 && args[0] == "?"))
            {
                Console.WriteLine("USAGE: fixfiletitlesjs filename [Y | N]");
                return;
            }

            string filename = args[0];

            //creates a new filname by replacing ".js" at the end of the filename with ".bak"
            string backupFilename = Regex.Replace(filename, @"\.js$", ".bak");
            try
            {
                //create a backup file
                FileInfo fi = new FileInfo(filename);
                fi.MoveTo(backupFilename);
            }
            catch (FileNotFoundException)
            {
                Console.WriteLine("File not found.");
                Console.ReadLine();
                return;
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("An exception was thrown while accessing the file: {0}", ex.Message));
                Console.ReadLine();
                return;
            }

            StreamWriter sw = null;
            try
            {
                sw = File.CreateText(filename);
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("An exception was thrown while accessing the file: {0}", ex.Message));
                Console.ReadLine();
                return;
            }

            StreamReader sr = null;
            try
            {
                sr = File.OpenText(backupFilename);
            }
            catch (Exception ex)
            {
                Console.WriteLine(string.Format("An exception was thrown while accessing the file: {0}", ex.Message));
                Console.ReadLine();
                return;
            }

            int i = 0,j = 0, k = 0;
            while (!sr.EndOfStream)
            {
                string line = sr.ReadLine();
                j++;
                Regex rx = new Regex("\",$");
                while (!sr.EndOfStream && !rx.IsMatch(line) && i > 0)
                {
                    line += sr.ReadLine();
                    j++;
                    k++;
                }
                sw.WriteLine(line);
                i++;
            }
            sw.Close();
            sr.Close();

            //write result
            Console.WriteLine(string.Format("Original file renamed to: {0}", backupFilename));
            Console.WriteLine(string.Format("New file created with original name: {0}", filename));
            Console.WriteLine(string.Format("Number of lines read from source: {0}", j));
            Console.WriteLine(string.Format("Number of lines written to destination: {0}", i));
            Console.WriteLine(string.Format("Number of concatenations of lines done: {0}", k));
            Console.WriteLine();

            string reply = string.Empty;

            if (args.Length >= 2 && !string.IsNullOrEmpty(args[1]))
            {
                reply = args[1];
            }

            while (reply.ToLower() != "y" && reply.ToLower() != "n")
            {
                if (reply != string.Empty) Console.WriteLine("You must answer Y or N.");
                Console.Write("Would you like to delete the backup of the original file (y/n)?");
                reply = Console.ReadLine();
            }

            if (reply.ToLower() == "y") File.Delete(backupFilename);
        }
    }
}

Using MARS with SQL Native Client

I tried using the example code from this article http://blogs.msdn.com/sqlnativeclient/archive/2006/09/27/774290.aspx  but I hade some trouble. Seems that you cannot use the System.Data.SqlClient to access MARS because it does not support using a Provider or the keyword "MARS Connection" in the connection string. This means you have to use an ADODB Connection.

Javascript error in EPiServer admin mode ('Invalid argument')

In this posting, Mark Bagnall describes a problem with javascript in EPiServer Edit Mode. I had the same problem. It was not possible to expand any branches in the EditTree, and the web browser reported a javascript error. Turned out I had set up my website in IIS with Windows Authentication only, to enable debugging/stepping in Visual Studio. The problem was fixed by allowing "Anonymous" access.

T-SQL CONVERT datetime to varchar

This script gets current date, and converts it using all existing predefined formats between 1 and 255:

DECLARE @format int;
CREATE TABLE #temp (format int NULL, string varchar(20) NULL, date datetime NULL)
SET @format = 1;
WHILE @format < 256
BEGIN
    BEGIN TRY
        INSERT INTO #temp (format, string, date) VALUES(@format, CONVERT(varchar(20),getdate(),@format), getdate());
    END TRY
    BEGIN CATCH
    END CATCH
    SET @format = @format + 1;
END
SELECT * FROM #temp;
DROP TABLE #temp;

SQL Server 2005 XML - Msg 9402 (unable to switch the encoding)

Had a problem converting XML from a column of type text to type xml. Someone else luckily has had the same problem:
http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/.

The solution was to convert the column from type TEXT to NVARCHAR(max), and get rid of the "encoding" part of the root tag, something like this (slightly altered from the example mentioned above):

SELECT ID, CAST(
REPLACE(CAST(XmlTextColumn AS NVARCHAR(MAX)), 'encoding="UTF-16"', '')
AS XML).query('xpath to nodes') AS Node
FROM TableName

Another way to solve this problem is by adding a computed XML column:

ALTER TABLE [MyTable]
    ADD MyXMLColumn AS CAST(REPLACE(CAST(MyXMLStoredAsText AS NVARCHAR(MAX)), 'encoding="UTF-16"', '') AS XML)
    PERSISTED

If you add the persisted option, the value will be persisted with the table data, which will give better performance for read operations (slightly worse performance for insert/update operations).

TFS Workspace Mapping

I was getting this error: The Path <local path> is already mapped in workspace <machine name [old tfs server]>

Turns out workspaces are cached locally and settings are found in: <DRIVE>:\Documents and Settings\<USER ID>\Local Settings\Application Data\Microsoft\Team Foundation\1.0\Cache.

To fix my problem I opened the VS 2005 Command Prompt and entered: "tf workspaces /remove:*" which removed all my cached workspaces (refer to http://msdn.microsoft.com/en-us/library/54dkh0y3.aspx for full description of the Workspaces Command). This also cleared most of the content from the earlier mentioned file in the users Documents and Settings.

Then, of course, I had to create a new workspace using VS 2005.

Source: http://geekswithblogs.net/aaronsblog/archive/2006/09/11/90878.aspx

.NET 2.0 transaction model

How to handle transactions using ASP.NET 2.0 and SqlClient. Nice article here.

Serialization of IDictionary objects

By design, objects that implement IDictionary (Hashtable, SortedList, ListDictionary, or HybridDictionary) cannot be serialized. This Q&A describes a way of making these objects serializable by using (a "hidden hook"), and implementing IXmlSerializable: http://msdn2.microsoft.com/en-us/magazine/cc164135.aspx

Using SQL Server 2005 XML and CROSS APPLY

In my last blog entry, I used the UNPIVOT operator to get a table with products from an XML type column which were displayed as columns, to display the columns as rows. As fun as that was, it was not really a practical approach, more a way of showing how the UNPIVOT operator works.

To get a similar result, without using UNPIVOT, using in stead the values() function with the CROSS APPLY operator, we could use this query:

SELECT
ContractNumber,
col.value('Name[1]', 'nvarchar(50)') AS ProductName
FROM Contract
CROSS APPLY contractXML.nodes('Contract/Order/OrderItem') AS x(col)

This could give a table like this if there were only one contract in the table with ContractNumber = 1:

ContractNumber	ProductName
1	Product 1
1	Product 1
1	Product 2

The nodes() function returns a table "x" with one column "col".

The CROSS APPLY operator joins the result from a table-valued function with the result of an "ordinary" query. This is like a LEFT JOIN, only against a table returned by a function in stead of another table.

Using SQL Server 2005 XML And Unpivot

I have a table with contracts stored as XML. Using the XML query possibilities in SQL Server 2005 together with the UNPIVOT keyword I can get statistics on different types of contracts.

My table has these columns:
ContractNumber int (Primary Key)
contractXML xml

My XML looks something like like this, and is stored in a column of type XML:

<Contract>
  <Customer>
  ...
  </Customer>
  <Order>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product3</Name>
      <Price>13.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product2</Name>
      <Price>23.00</Price>
    </OrderItem>
    <OrderItem>
      <Name>Product1</Name>
      <Price>123.00</Price>
    </OrderItem>
  </Order>
</Contract>

To get a table with a max of 5 OrderItems as columns, I can use this query:

    SELECT
      ContractNumber,
      contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)') AS Product1,
      contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)') AS Product2,
      contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)') AS Product3,
      contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)') AS Product4,
      contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)') AS Product5
    FROM Contract

 

This is nice, but what if I want to get the number of each product sold? The answer is that I can use the UNPIVOT operator!
Something like this will do it:

SELECT Name, COUNT(*) AS [Count] FROM
(
    SELECT
        ContractNumber, col, Name
    FROM
    (SELECT
    ContractNumber,
    [col1] = contractXML.value('(/Contract/Order/OrderItem/Name)[1]','varchar(50)'),
    [col2] = contractXML.value('(/Contract/Order/OrderItem/Name)[2]','varchar(50)'),
    [col3] = contractXML.value('(/Contract/Order/OrderItem/Name)[3]','varchar(50)'),
    [col4] = contractXML.value('(/Contract/Order/OrderItem/Name)[4]','varchar(50)'),
    [col5] = contractXML.value('(/Contract/Order/OrderItem/Name)[5]','varchar(50)')
    FROM Contract) col
    UNPIVOT(
        Name
        FOR col
        IN ([col1],[col2],[col3],[col4],[col5])
    ) AS unpvt
) AS T
GROUP BY PackageName

The UNPIVOT operator gives the values in the 5 columns as 1 column.

If I had only the 1 row in my Contract table from the example above, the result would be:

Name	Count
Product1	3
Product2	1
Product3	1

This is a simple example, and it has a max number of ordered products per contract of 5. Could maybe be extended.

If you want a table with the counts for the different products as columns, then something like this would do the job:

SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract

This would give this result:

Product1	Product2	Product3
3	1	1

And we could of course UNPIVOT this result too:

SELECT
   ProcuctCount
FROM
(SELECT
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product1"])','int')) AS Product1,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product2"])','int')) AS Product2,
   SUM(contractXML.value('count(/Contract/Order/OrderItem[Name="Product3"])','int')) AS Product3
FROM Contract) cols
UNPIVOT(
   ProductCount
   FOR cols IN (Product1, Product2, Product3)
) AS unpvt

This should give a table like this:

ProductCount

3

1

1

Apache Leap Year Bug

Seems Apache (Web Server) has some rather embarrassing problems with leap years: http://blogs.lodgon.com/johan/Leap_year_issues_in_apache_commonsnet
https://issues.apache.org/jira/browse/NET-188

Gøran's blog

I see that this guy, who was a presenter at MSDN Live in Oslo yesterday, has some good links and stuff relating to WPF and hopefully soon something on MVC (Model-View-Controller): http://blog.goeran.no/CategoryView,category,Presentation.aspx

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

This blog entry explains how to read the "login failed" error message for SQL Server 2005. The messages can be very cryptic, like for instance "Error: 18456, Severity: 14, State: 8. It is the "State" part that tells you the reason the login failed.