2008-05-20

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;

2008-05-15

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).

2008-05-07

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