THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

  • SQL Bracketed Identifiers and Community Server

    Bloggers at SQLblog.com were occasionally having issues posting. We had narrowed down the cause to the use of bracketed identifiers with certain text, such as [name] or [date], so that when a post contained a query such as this:

    SELECT Description as [name],
        , OrderDate as [date]
    FROM SomeTable

    Attempting to publish it to the blog would simply cause an unhandled exception. The problem has to do with the Community Server TextPart component, which uses bracketed identifiers to replace snippets and for certain post settings when posting. I found a work around, however, which involves making a change to the ScottWater.CS.Modules source code and redeploying. Here is the code change, found in ScottWater.CS.Modules.MetaBlogExtender class, csa_PreProcessPost method:

    ...
    foreach
    (TextPart tp in e.TextParts)
    {
        string name = tp.Name.ToLower();
        /* Start of added code */
        try
        {
            String testValue = tp.Value;
        }
        catch (ArgumentOutOfRangeException)
        {
            break;
        }
        /* End of added code */
        switch
    (name)
            {
    ...

     

  • Virus Alert: Windows Live Messenger

    I received a IM message in Windows Live Messenger from a techie friend earlier today. The message reads (myname replaces my actual email name)

    "Hey isnt this YOU?? http://msngallery.ms.funpic.de/viewimage.php?=myname@hotmail.com"

    I thought it strange that my friend was offline when this message was sent, but I clicked the link anyways to see what he could be talking about (my ego/curiosity made a bad choice). Next thing I know, I am being prompted to Run or Save a MS-DOS application. Fortunately, I read the dialog and canceled the request.

    According to news on the net, some AV programs didn't catch it and many people now have a trojan running on their system.

  • OT: What will Bill Gates do after he leaves Microsoft?

    I laughed, I cried...

    http://www.youtube.com/watch?v=v5uw07iEkjU

    Bill Gates presents a farewell video at the keynote of the Consumer Electronics Show. (video URL has been changed to YouTube since the MSN video and image are no longer "available".)

  • TechEd 2008 Abstract Deadline Extension

    Just over a month ago, Simon Guest had posted a blog entry about the call for sessions for TechEd 2008. The original deadline was 31 December 2007, but I've just gotten confirmation that this deadline has been extended to 31 January 2008, so if you were thinking of submitting any abstracts, you still have some time to do it.

    This year, the format for TechEd is different: It occurs over two weeks, the first being a developer-centric conference (June 3 - 6), and the latter being IT-centric (June 10 - 13). SQL Server topics can obviously be in both, so be sure to choose the correct conference before submitting your abstracts.

    To get started, you need to first go to https://2008.msteched.com/cft/. Use TEUS-Speakers as the access code, create a profile, and begin submitting topics.

    And good luck!

  • 2008: Converting XML to HIERARCHYID

    While working on a new book, I have been experimenting with the new HIERARCHYID data type. And since I've seen comparisons drawn between using XML versus using HIERARCHYID to manage a hierarchy of data, I decided that it would likely be very useful if I could convert an XML structure into an equivalent relational structure using HIERARCHYID. Here is what I came up with:

    DECLARE @x XML =
    '<A id="1">
        <B id="2">
          <C id="3"/>
          <D id="4"/>
        </B> 
        <E id="5"/>
        <F id="6"/>
    </A>';

    WITH Folders (ID, ParentID, Description, RowNum) AS
    (
        SELECT
            t.c.value('@id', 'int')
            , NULLIF(t.c.value('../@id', 'nvarchar(50)'), '')
            , t.c.value('local-name(.)', 'nvarchar(50)')
            , t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int')
        FROM @x.nodes('//*') AS t(c)
    )
    , FolderTree AS
    (
        SELECT ID, ParentID, Description, RowNum,
            HIERARCHYID::GetRoot() AS FolderNode
        FROM Folders
        WHERE ParentID IS NULL

        UNION ALL

        SELECT
    F.ID, F.ParentID, F.Description, F.RowNum,
            CAST(FT.FolderNode.ToString() + CAST(F.RowNum AS varchar(50)) + '/'
                AS HIERARCHYID)
        FROM Folders AS F
            INNER JOIN FolderTree AS FT ON F.ParentID = FT.ID 
    )
    SELECT
        ID,
        ParentID,
        Description,
        FolderNode,
        FolderNode.ToString() AS Path
    FROM FolderTree
    ORDER BY FolderNode;

    Which results in:

    ID 

    ParentID 

    Description 

    FolderNode 

    Path 

    NULL 

    0x 

    0x58 

    /1/ 

    0x5AC0

    /1/1/ 

    0x5B40 

    /1/2/ 

    0x68 

    /2/ 

    0x78 

    /3/ 

    The basis of this is the same as if converting an existing adjacency model to use HIERARCHYID – use a combination of a recursive CTE and row numbers partitioned by parent to generate the new path of the node and then cast that path to the HIERARCHYID data type. The parent path is retrieved using the ToString method of the parent node, and this is concatenated with the RowNum value and a forward slash (/) to result in the path for the current node. And finally, the newly generated path is then cast to HIERARCHYID, which uses its static Parse method behind the scenes to create a new instance of HIERARCHYID. This table shows the additional detail and should reveal how the new path is created.

    ID 

    ParentID 

    Description 

    FolderNode 

    Path 

    Parent Path 

    RowNum

    NULL 

    0x 

    N/A 

    1

    0x58 

    /1/ 

    /

    1

    0x5AC0

    /1/1/ 

    /1/

    1

    0x5B40 

    /1/2/ 

    /1/

    2

    0x68 

    /2/ 

    /

    2

    0x78 

    /3/ 

    /

    3

    On the XML side of things, I generated a partitioned row number by using the nodes method to return all nodes in the XML structure, as shown here: FROM @x.nodes('//*') AS t(c). The double forward slash (//) translates to "relative path" and the asterisk means any node, so this returns all nodes at all levels. Then, to return the row number, I use one of my favorite XQuery examples:

    t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS RowNum

    This translates to "count the number of immediate child nodes of my parent node that are positioned before me." Now that I have all the nodes, and a partitioned row number for these nodes, I can construct the new path of the node.

    I will post more as I continue writing about and exploring SQL Server 2008.

    Enjoy!

  • 2008: Row Constructor or Table-Valued Parameter

    Denis posted a follow-up to the posts by both Louis and me. However, I have to say that there is a better way to pass a bunch of data to a procedure. The following code is amended to include the table-valued parameter feature.

    --The Movie Table
    CREATE TABLE Movie
    (
    MovieID INT IDENTITY(1, 1),
    MovieRatingId INT,
    Title VARCHAR(200) NOT NULL
    );
    GO
    INSERT INTO Movie (MovieRatingId, Title)
    VALUES
    (3, 'SQL the Movie'),
    (4, 'SQL Massacre'),
    (1, 'SQL for Everyone'),
    (4, 'SQL Massacre 2 - The Oracle Returns');

    --Create a new table type in the database
    CREATE TYPE MovieRating AS
    TABLE
    (
    MovieRatingId int NOT NULL,
    Code varchar(20) NOT NULL,
    Description varchar(200) NULL,
    AllowYouthRentalFlag bit NOT NULL
    )
    GO

    --Create a new proc that accepts a table as a parameter
    CREATE PROC prMoviesByRating
    (
        @MovieRatings MovieRating READONLY
    )
    AS
    BEGIN
        
    --and join to that table-valued parameter
        SELECT
            M.Title,
            MR.Code AS RatingCode,
            MR.Description AS RatingDescription
        FROM Movie AS M
            INNER JOIN @MovieRatings AS MR
                ON M.MovieRatingId = MR.MovieRatingId;
    END
    GO

    --Declare a variable using our table type
    DECLARE @MovieRatings MovieRating
    --and insert data into it (again, using a row constructor)
    INSERT INTO @MovieRatings
    VALUES
    (0, 'UR','Unrated',1),
    (1, 'G','General Audiences',1),
    (2, 'PG','Parental Guidance',1),
    (3, 'PG-13','Parental Guidance for Children Under 13',1),
    (4, 'R','Restricted, No Children Under 17 without Parent',0)

    --and pass the table as parameter to the proc...
    EXEC
    prMoviesByRating @MovieRatings

    So when I want to pass the data in, no dynamic SQL is necessary, since I can instead simply pass the table itself (with all of its data)…

  • 2008: More Row Constructor Fun

    My esteemed colleague Louis Davidson just wrote about a new feature in SQL Server 2008 known as row constructors. I thought I'd follow up with some more fun usage of this cool feature. In addition to being used with an INSERT statement, you can also use a row constructor in a common table expression (CTE). This example uses the row constructor to insert data into one table (@Movie) and then again to join a table to another sets of constructed row:

    DECLARE @Movie TABLE
    (
        MovieID INT IDENTITY(1, 1),
        MovieRatingId INT,
        Title VARCHAR(200) NOT NULL
    );

    INSERT INTO @Movie (MovieRatingId, Title)
    VALUES
        (3, 'SQL the Movie'),
        (4, 'SQL Massacre'),
        (1, 'SQL for Everyone'),
        (4, 'SQL Massacre 2 - The Oracle Returns');

    WITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)
    AS
    (    SELECT *
        FROM (VALUES
            (0, 'UR','Unrated',1),
            (1, 'G','General Audiences',1),
            (2, 'PG','Parental Guidance',1),
            (3, 'PG-13','Parental Guidance for Children Under 13',1),
            (4, 'R','Restricted, No Children Under 17 without Parent',0))
                AS MR(MovieRatingId, Code, Description, AllowYouthRentalFlag)
    )
    SELECT
        M.Title,
        MR.Code AS RatingCode,
        MR.Description AS RatingDescription
    FROM @Movie AS M
        INNER JOIN MovieRatings AS MR
            ON M.MovieRatingId = MR.MovieRatingId;

    Notice that when used in a CTE, the syntax is slightly different

    SELECT column_list
    FROM
    (VALUES
        (column_1_value [, column_2_value [, ...]])
        (column_1_value [, column_2_value [, ...]])
    ) AS TableAlias (column_1_name [, column_2_name [, ...]])

    When used in a CTE, you must supply a table alias, as well as column names for all columns supplied by the row constructor.

    Enjoy!

  • Non-Technical: SQLblog has moved!

    If you are reading this post then you are being served up by SQLblog from its new home at MaximumASP hosting provider. We are very excited to be working with the people at MaximumASP in order to better serve the community and help with the continuing growth of SQLblog.
  • SQL Server Connection Level Parameters

    Have you ever needed to pass in a value that would be used throughout the lifetime of a connection - perhaps a connection session level value that would be used by multiple procedures, views, functions, etc...?

    Well, a colleague of mine, Jay Frysinger, had a great idea - use the connection string itself to pass additional parameters. You can use both "Application Name" and "Workstation ID" to pass in 2 additional 128 character length strings. You can then use the APP_NAME() system function to retrieve the value of the "Application Name" or the HOST_NAME() system function to retrieve the value of "Workstation ID".

    In this example, a GUID is created in the data client, then using ToString() of the GUID, it is added to the connection string. Then,  HOST_NAME() is used to extract that value. Since I wanted a UniqueIdentifier in SQL, I used CAST to convert the string GUID to a UniqueIdentifier. Finally, the UniqueIdentifier is returned and displayed in the console.

    using System;
    using System.Data;
    using System.Data.SqlClient;

    namespace ConnectionStringTest
    {
        
    class Program
        {
            
    static void Main(string[] args)
            
    {
                Guid guid 
    = new Guid("BCAEF7EA-7BD3-4CC8-8288-9B2C8F6BCF28");

                
    using (SqlConnection c = new SqlConnection(string.Format
                   
    (@"Data Source=YourServer;Initial Catalog=YourDatabase;Persist Security Info=True;Trusted_Connection=True;Workstation ID={0}"
                   
    guid.ToString())))
                
    {
                    
    using (SqlCommand cmd = new SqlCommand("SELECT CAST(HOST_NAME() as uniqueidentifier)"c))
                    
    {
                        cmd.CommandType 
    CommandType.Text;
                        
    c.Open();
                        
    SqlDataReader dr cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        
    while (dr.Read())
                        
    {
                            Console.WriteLine
    (dr.GetSqlGuid(0).ToString());
                        
    }
                        dr.Close
    ();
                    
    }
                    c.Close
    ();
                
    }
                Console.ReadLine
    ();
            
    }
        }
    }

  • Manage Visual Studio Auto Connect to Team Foundation Server

    I've been on a project where we are using Team Foundation Server (TFS). As you may already be aware, Visual Studio (VS) automatically tries to connect to TFS. When I am onsite, this is great, but when I am offsite and not VPN-ed into the client site, VS takes a long time to time out on its attempt to connect to their TFS. When going in and out of VS throughout the day, I felt as if I was wasting a lot of time waiting for VS to time out. So I found this little nugget that showed how to modify the registry to turn off the auto connect feature:

    http://blogs.conchango.com/merrickchaffer/archive/2007/01/09/Prevent-Visual-Studio-Team-Explorer-from-connecting-to-Team-Foundation-Server-at-startup.aspx

    But I decided that I wanted to quickly be able to change the setting without having to plow through the registry each time, and so I wrote a quickie VBScript for WSH that would allow you to easily change the registry setting. Here is that script: 

    ' VBScript
    ' The script creates and/or updates the TFS AutoLoadServer feature for Visual Studio
    ' By default, Visual Studio will attempt to reconnect to all Team Foundation Servers

    ' Creating a REG_DWORD value named AutoLoadServer at 
    ' HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\TeamFoundation\
    ' you can manually control whether or not VS will automatically connect to TFS server(s) 
    ' 0 means do not automatically connect 
    ' 1 means automatically connect (default behavior when value is not present)
    '
    ' Peter DeBetta, 2007

    ' Some variables
    Dim objShellRegLocation

    ' Instantiate the WSH Shell
    Set objShell WScript.CreateObject("WScript.Shell")

    ' This is the value that affects the auto connect feature
    RegLocation"HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\TeamFoundation\AutoLoadServer"

    ' Default to existing value (blank if not present)
    id = objShell.RegRead(RegLocation)

    ' Ask for the new value (default to "1")
    id inputbox("Type 1 to turn on, 0 to turn off","VS TFS AutoLoadServer"id)

    If id <> "0" And id <> "1" Then
       
    ' Did we get a valid value? No? Then let the user know
       
    objshell.popup "Invalid input: Only 0 or 1 are allowed" ,,"INPUT ERROR!",48
    Else
       
    ' Otherwise, update the Registry Value
       
    objShell.RegWrite RegLocationid"REG_DWORD"
    End If

    ' Exit Gracefully
    WScript.Quit 

    Quick Update: Changed default to existing value in Registry (and blank if it doesn't exist)

  • DevTeach Code Samples

    A little overdue, here are my code samples from my sessions at DevTeach Montreal 2007.

  • News: DevTeach, TechEd, and SQLblog Sundries...

    I know I have been a slacker when it comes to posting lately, but I have been on the road quite a bit of late and will finally have some time at home over the next 2 weeks and have some new topics that I should be posting about during that time.

    As for DevTeach, SQLblog had a great presence at the SQLTeach part of the conference here in Montreal, including (in no particular order): Adam Machanic, Kevin Kline, Rick Heiges, Paul Nielsen, Roman Rehak (who was the SQLTeach Tech Chair), and yours truly, and soon to be blogging at SQLblog, James Luetkehoelter. I heard great things about my fellow SQLbloggers and was glad to get some time to catch up with them and a few other folks at the conference.

    As for TechEd. I will be off to TechEd this year (June 4 - 8), and even doing a breakout session on XML in SQL Server 2005. I'll also be co-hosting an SQL Server 2005 XML Q&A session with Michael Rys from Microsoft. Very exciting!

    I will be looking into migrating SQLblog to the latest version of CommunityServer (CS2007) in the coming months. With the change, we are also hoping to add some new features to the site. Some of the features we have been contemplating are articles, book reviews, and voting forums. No promises; but when my current project ends, I hope to have time to implement some of these features (assuming CS2007 makes it easy to do so).

    Finally, if you are using the Windows Live Toolbar in IE, be sure to add the SQLblog custom button (found here) for quick access to the site and the latest posts!

  • T-SQL Convert Hex String to Varbinary

    [EDIT] I made a few changes to account for the issues Chris and Cooter noted in the comments. 

    One last post before I go on vacation next week...

    SQL Server 2005 includes an undocumented function, sys.fn_varbintohexstr, that converts a hex value to a string representation of that hex value (0x3a becomes '0x3a'), but there is no function to go back from a hex string to a hex value. Now there is J:

    CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(8000)) 
    RETURNS varbinary(8000
    AS 
    BEGIN 
       DECLARE 
    @hex CHAR(2), @i INT@count INT@b varbinary(8000), @odd BIT@start bit
       
    SET @count LEN(@hexstr
       
    SET @start 1
       
    SET @b CAST('' AS varbinary(1)) 
       
    IF SUBSTRING(@hexstr12'0x' 
           
    SET @i 
       
    ELSE 
           SET 
    @i 
       
    SET @odd CAST(LEN(SUBSTRING(@hexstr@iLEN(@hexstr))) % AS BIT)
       
    WHILE (@i <= @count
        
    BEGIN 
           IF 
    @start AND @odd 1
           
    BEGIN
               SET 
    @hex '0' SUBSTRING(@hexstr@i1)
           
    END
           ELSE
           BEGIN
               SET 
    @hex SUBSTRING(@hexstr@i2)
           
    END
           SET 
    @b @b 
                   
    CAST(CASE WHEN SUBSTRING(@hex11) LIKE '[0-9]' 
                       
    THEN CAST(SUBSTRING(@hex11AS INT
                       
    ELSE CAST(ASCII(UPPER(SUBSTRING(@hex11)))-55 AS INT
                   
    END 16 
                   
    CASE WHEN SUBSTRING(@hex21) LIKE '[0-9]' 
                       
    THEN CAST(SUBSTRING(@hex21AS INT
                       
    ELSE CAST(ASCII(UPPER(SUBSTRING(@hex21)))-55 AS INT
                   
    END AS binary(1)) 
           
    SET @i @i + (- (CAST(@start AS INT) * CAST(@odd AS INT)))
           
    IF @start 1
           
    BEGIN
               SET 
    @start 0
           
    END
        END 
        RETURN 
    @b 
    END 
    GO 

  • T-SQL UrlDecode

    While I'm at it, here is the counterpart to the UrlEncode function – UrlDecode:

    CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
    RETURNS varchar(3072)
    AS
    BEGIN 
        DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072
        SET @count = Len(@url
        SET @i =
        SET @urlReturn = '' 
        WHILE (@i <= @count
         BEGIN 
            SET @c = substring(@url, @i, 1
            IF @c LIKE '[!%]' ESCAPE '!' 
             BEGIN 
                SET @cenc = substring(@url, @i + 1, 2
                SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' 
                                    THEN CAST(SUBSTRING(@cenc, 1, 1) as int) 
                                    ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int
                                END * 16
                                CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' 
                                    THEN CAST(SUBSTRING(@cenc, 2, 1) as int) 
                                    ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int) 
                                END
                SET @urlReturn = @urlReturn + @c 
                SET @i = @i +
             END 
            ELSE 
             BEGIN 
                SET @urlReturn = @urlReturn + @c 
             END 
            SET @i = @i +
         END 
        RETURN @urlReturn
    END
    GO

  • T-SQL UrlEncode

    I was perusing the newsgroups when I came across a request in the microsoft.public.sqlserver.clr newsgroup to see if there was a way to use System.Web.HttpUtility.UrlEncode in T-SQL. I know using the CLR is an option, but I decided to write a T-SQL equivalent. I reflected the code to figure out the exact rules for UrlEncoding. Then, I discovered much to my delight, a new system function in SQL Server 2005 that converts a string to its hexadecimal equivalent as a string– sys.fn_varbintohexstr. I knew there was an extended stored procedure in prior versions of SQL Server to do this conversion, but this function made writing this UrlEncode function even easier.

    CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))
    RETURNS NVARCHAR(3072)
    AS
    BEGIN
        DECLARE
    @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
        
    SET @count = LEN(@url)
        
    SET @i = 1
        
    SET @urlReturn = ''    
        
    WHILE (@i <= @count)
        
    BEGIN
            SET
    @c = SUBSTRING(@url, @i, 1)
            
    IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
            
    BEGIN
                SET
    @urlReturn = @urlReturn + @c
            
    END
            ELSE
             BEGIN
                SET
    @urlReturn =
                      
    @urlReturn + '%'
                      
    + SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
                       +
    ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
            
    END
            SET
    @i = @i +1
        
    END
        RETURN
    @urlReturn
    END
    GO

    This function works in a very similar fashion to the UrlEncodeBytesToBytesInternal found in the .NET Framework (in System.Web.HttpUtility) except that the function I wrote does not convert the URL to a byte array. If you have the latest version of Reflector, you can view the reflected code for UrlEncodeBytesToBytesInternal by going to this URL:

    code://System.Web:2.0.0.0:b03f5f7f11d50a3a/System.Web.HttpUtility/UrlEncodeBytesToBytesInternal(Byte%5b%5d,Int32,Int32,Boolean):Byte%5b%5d

    And speaking of Reflector – if you have the latest version, you can copy a URL link to a method, class, property, and so on by select the item and pressing Ctrl-Alt-C (which is how I got the URL to lookup the UrlEncodeBytesToBytesInternal method in Reflector).

    [Edit - I've made some modifications which should address everyone's very valid points!]

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement