# Wednesday, October 12, 2005

I have a wonderful VPS of Yukon and Whidbey with all of my conference demos. I my first TSQL session and it went well but the VPC was slow. Then I had my next session “Ranking and Windowing” functions, 15 minutes later in another room. I suspended my PC and went on. VPC was dead in the water.

I had to do a song and a dance. A delegate named Harold lent me his computer so I can demo code. I decided to reboot my machine while I was working on Harold's PC. Once back, life was good.

So lesson learned, do not suspend your computer with a VPC. However another speaker, Walt Ritscher, let me know that there is a hotfix for this craziness. Installed and life is good. 



posted on Wednesday, October 12, 2005 2:24:41 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [6] Trackback
# Tuesday, October 11, 2005

When I installed the latest beta of SQL Server 2005 and ran the Merge Replication wizards to create a Publication, I got a strange new error not present in previous betas. The error said that the “XP Agents are not enabled and you must run sp_configure to enable them.”

I figured that MS did the right thing and turned this feature off by default. I ran the new SQL Server Surface Area Configuration Tool and could not find anything for enabling XP Agent stored procedures, etc. I did find a lot of good stuff disabled by default that will make SQL Server more secure. This is good since by default out of the box an installation of SQL Server will leave some of the advanced, yet powerful tools disabled to deny a hacker the ability to guarantee that it will be turned on.

So in my case, MS turned it (XP Agents) off but do not give a way in the tool to enable it. After some trial and error and help from help I got this to make it all work and my publication agent ran smoothly:

You can't just run sp_configure, you first have to turn on advanced options so SQL Server knows it exits:

use master
sp_configure 'show advanced options', 1;
sp_configure 'Agent XPs', 1;

It is good that this stuff is turned off by default, it will just mean a little more time for us developers to setup and deploy. Worth the tradeoff.

posted on Tuesday, October 11, 2005 8:15:40 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, October 10, 2005

Speaking at VSLive again down in Orlando, Florida. Should be a great show. I am speaking on SQL Server 2005 topics, hope to see you there.

TSQL Enhancements
Stephen Forte, Corzen, Inc.
10:30 a.m.

By now you have heard that you can write stored procedures in C# and VB . NET for SQL Server 2005. Does that mean TSQL is dead? NO! A lot has changed in the world of TSQL with the next version of SQL Server. See how efficient you can become with the new TSQL enhancements. You can crosstab in seconds with the PIVOT statement, perform recursive queries much easier with Common Table Expressions, manipulate XML data easier with the XML datatype and aggregate with ease using the new TOP functions. Report with ease with ranking functions. We’ll take a look at the new TSQL enhancements, data types, and the like. We’ll also look at how to choose between CLR Stored Procedures and TSQL.

Data Analysis Using Ranking and Windowing Functions
Stephen Forte, Corzen, Inc.
11:45 a.m.

SQL Server 2005 adds the functionality of a ranking expression that can be added to your resultset that is based on a ranking algorithm being applied to a column that you specify. This will come in handy for reporting and in .NET applications for paging and sorting in a grid as well as many other scenarios. We’ll explore the ROW_NUMBER() function, which assigns a running count to each row and compare it to RANK() and DENSE_RANK(). Use these functions to solve complex SQL Problems. Then we’ll look at custom percentile ranking using NTILE() and look at business and academic applications of NTILE. Lastly, we’ll apply windowing functions to all of the four ranking functions with PARTITION BY to perform hard code aggregate functions. 


 Mobile Enterprise Applications with SQL Server 2005 Mobile Edition
Stephen Forte, Corzen, Inc.
4:30 p.m.

Using SQL Server 2005 Mobile Edition and Visual Studio 2005, Microsoft has made developing applications for the Windows SmartPhone and Pocket PC devices as easy as developing conventional Windows and Web applications. The .NET Compact Framework and SQL Server Mobile give you full functionality on the device with the ability to perform Merge Replication with a back-end SQL Server. This session will help you leverage your existing .NET and SQL Server to build mobile enterprise applications from scratch that can dynamically update a backend SQL Server or work offline and synchronize later.

posted on Monday, October 10, 2005 10:40:26 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Sunday, October 9, 2005

I know that all my firends in Pakistan read my blog, and let me say my heart goes out to you. You and your country really touched me on my most recent visit.

While most of my firends are safe in Karachi, I had to search for Saqib Ilyas in Lahore, much closer to the epicenter. Thankfully he is ok.

My buddy Fahad Majeed reports about the earthquake here.

You can donate here.

posted on Sunday, October 9, 2005 10:38:32 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [1] Trackback
# Saturday, October 8, 2005

SQL Server 2005 allows you to store XML in a native data type. That is cool, however, having XML in the database is almost useless unless you can query the elements and attributes of the XML data natively. XQuery becomes very useful when you can use it to search based on the values of a particular element or attribute. The xml.exist() function accepts an XQuery as input and returns 0, 1, or NULL, depending on the result of the query; 0 is returned if no elements match, 1 is returned if there is a match, and NULL is returned if there is no data to query on. For example we will verify if a node exists in this particular XML string of classes.


Set @XML='


   <class name="SQL Server Index"/>

   <class name="SQL Precon"/>



Select @XML.exist('/classes')


The code above returns a 1 since the “classes” element exists in the XML variable. If you change the XQuery expression to search for an XML node that does not exist like,  Select @XML.exist('/dogs), then it will return 0.

You can see this in action as part of a CHECK CONSTRAINT. SQL Server will not allow you to use an xml.exist as part of a CHECK CONSTRAINT. So you have to first create a user defined function (UDF) to perform the action. This UDF accepts an XML field and retunes the value of an xml.exist() method looking for an instance of < Orders'>:

USE AdventureWorks


CREATE FUNCTION dbo.DoesOrderXMLDataExist        

(@XML XML)                    

RETURNS bit                       



RETURN @XML.exist('/Orders')   




            To use this UDF as a CHECK CONSTRAINT, just create a table and pass the column you want to apply the constraint to the UDF you just created.

--create the table using the function



   xOrders XML NOT NULL Default '<Orders/>'

 CONSTRAINT xml_orderconstraint


Its that simple, now you have a rule enforced on that column making sure that an <Order> element is added to this table.
posted on Saturday, October 8, 2005 3:56:06 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [2] Trackback
# Wednesday, September 28, 2005
That was my time for a mile in the race this weekend down 5th Avenue. In some respects a mile that fast was harder than the marathon. Headed out to Redmond, Washington to visit the boys at Microsoft at the MVP Summit. Should have some good stories to tell in the old blog while I am out there.
posted on Wednesday, September 28, 2005 4:30:10 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Tuesday, September 20, 2005

This Sunday I am going to see the Dalai Lama speak at Rutgers University. The topic is “Peace, War, and Reconciliation.” I am curious to see how he approaches the topic of China and Tibet.


Not being a fan of Richard Gere I do find the Chinese occupation of Tibet wrong. I can say this having visited the region several times and spending a lot of time there making friends with the locals in Tibet, Bhutan and Nepal. I hope the Dali Lama speaks to this.

posted on Tuesday, September 20, 2005 8:23:07 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, September 19, 2005

Three and a half years ago I scaled my first peak in the Catskills and was hooked to get into the exclusive Catskill 3500 club. The 3500 club is someone who has climbed all 35 peaks of 3500 feet elevation in the Catskills, including 4 of them twice in the winter. You may say, that is easy, but considering that 18 or so of them are trail less and the Catskills can have a foot of snow as early as October 1 and as late as May 1.


Well I did it! It did take me long enough since I got diverted by the mountains in Bhutan, Machu Picchu, Everest, Denali, Mt. Rainer (twice), Kilimanjaro and a marathon in some strange land of Antarctica.


So in all of those crazy treks, Nepal is my favorite country (people, scenery, Buddhist culture), Rainer is my favorite, and Kilimanjaro is the one most likely to do more than once.


In the Catskills, I would do almost any of the mountains over again (and will do a bunch this winter) and most likely will start climbing some peaks in the White Mountain Range in NH.


So here are the peaks:



Slide (2/9)

Balsam (2/24)

Hunter (6/2)

Southwest Hunter (6/2)

Black Dome (9/8)

Thomas Cole (9/8)

Blackhead (9/8)

Whindham High Peak (9/28)

Graham (9/29)

Balsam Lake (9/29)

Panther (11/10)

Panther (12/29)



Blackhead (1/12)

Big Indian (3/13)

Eagle (3/13)

Westkill (3/23)

Table (4/19)

Peekamoose (4/19)

Indian Head (10/4)

Twin (10/4)

Sugarloaf (10/4)

Pleatu (10/4)

Katterskill High Peak (10/25)

Bearpen (10/26)

Vly (10/26)

Slide (11/16)

Wittenberg (11/16)

Cornell (11/16)

North Dome (12/28)

Sherill (12/28)



Balasm (1/4)

Lone (9/22)

Rocky (9/22)

Doubletop (9/25)

Fir (11/27)



Balsam Cap (5/15/05)

Friday (5/15/05)

Rusk (7/16/05)

Halcott (09/17/05)


posted on Monday, September 19, 2005 3:05:31 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [1] Trackback