# Tuesday, June 7, 2005

For some reason they trusted me to do the first one. It went very well: Ranking and Windowing Functions in SQL Server 2005. After editing, you can view it here: http://www.groktalk.net/blog/

posted on Tuesday, June 7, 2005 11:48:13 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback

SQL Sever 2005 will ship on NOvember 7th. It has been a long time, worth the wait. Go get the current beta, released today: http://msdn.microsoft.com/

posted on Tuesday, June 7, 2005 10:36:53 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, June 6, 2005

Tuesday 11:25 AM: Using Ranking and Windowing Functions in SQL Server 2005

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 in .NET applications for paging and sorting in a grid as well as many other scenarios. We will 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 will look at custom percentile ranking using NTILE() and look at business and adacemic applications of NTILE. Lastly we will apply windowing functions to all of the 4 ranking functions with PARTITION BY to perform hard code aggregate functions

posted on Monday, June 6, 2005 11:55:00 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
posted on Monday, June 6, 2005 12:22:11 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback

Come to mine today:

DBA304  Advanced Querying Techniques, Tips & Tricks Using Transact-SQL
Speaker(s): Richard Campbell, Stephen Forte
Session Type(s): Breakout
Track(s): Database Administration
Day/Time: Monday, June 6 3:15 PM - 4:30 PM Room: S 310 A
Take your querying to the next level! This session gets away from the fundamentals of SQL queries and into the hard stuff. See two experts in SQL Server compare and contrast querying techniques between SQL Server 2000 and SQL Server 2005. This session has a series of real world examples to show how creative SQL queries can generate solutions in record time. Some techniques you'll learn include how to do crosstab queries that take seconds to execute instead of hours, exploiting sub-queries and taking advantage of self-joining. Along the way, get some insight into how SQL servers work, as well as how SQL Server 2005 is going to make advanced querying even easier.
posted on Monday, June 6, 2005 12:44:10 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Saturday, June 4, 2005
posted on Saturday, June 4, 2005 1:43:11 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, June 3, 2005

With an auction starting on Monday, 22 of the top Tech*Ed speakers, including Microsoft employees (don’t worry we cleared it with Legal <g>), will together to help raise money for an organization that is doing amazing and heroic disaster relief and recovery in Aceh Province, Sumatra, the hardest hit area of the Dec 26th Tsunamis. Just like last time we will auction the time off on eBay, the link will be here in about 24 hours.


Although news of the tsunami has largely disappeared from the international press, five months later, the situation on the ground in Aceh remains acute. Many isolated communities have not yet been helped. Aceh Recovery at IDEP (www.acehaid.org) is continuing their efforts to get assistance to the people that are most in need through a dynamic network of local NGOs and partners on the ground in Aceh.


Here is the current list of speakers donating their time:


Don Box

Jesper Johansson

Richard Campbell

Scott Hanselman

Kimberly Tripp

Michele Leroux Bustamante

Kate Gregory

Juval Lowy

Stephen Forte

Clemens Vasters

Andrew Brust

Carl Franklin

Ingo Rammer

Christian Weyer

Joel Semeniuk

Rockford Lhotka

Patrick Hynds

Tim Landgrave

Tim Huckaby

Jackie Goldstein

John Goodyear

Richard Hundhausen


IDEP is a small, Indonesian NGO, based in Ubud, Bali. Completed projects over the years have included community based development, sustainable living initiatives, permaculture training, waste management, organic gardens, recycling, etc. The focus is on helping people to help themselves. IDEP's founding director, Petra Schneider is a US-born, Indonesian citizen. The demonstrated and reproducible success of IDEP's small projects in local communities has earned the team an excellent reputation.

At the time of the Bali bomb, about two years ago, IDEP was an important element of the network of local NGOs and other supporters that quickly responded to the tragedy, in various ways, not only immediately after the bomb, but during the recovery process for the various communities involved. Following shortly thereafter, IDEP received funding from USAid to create a comprehensive set of disaster management materials for Indonesian communities, aimed at children, families, and local leaders (official and unofficial). The materials are in the Indonesian language and suitable for use in rural and urban settings. These materials, including a booklet for children about Tsunami preparedness, were finished just weeks ago, but had not yet been disseminated to communities. Then the tsunami struck.

Only hours after the news of the tsunami reached Bali, the same network of NGOs and individuals in Bali who had been involved in the relief efforts for the Bali bomb, reanimated and went into action. We started something called the "Aceh Aid Bucket Brigade" (see website), creating and deploying one-family-one-bucket multi-material aid packages from the hands of donors in Bali to the field in Sumatra. We began sending highly skilled volunteers, well-matched to the task within two days of the tsunami (Sam Schultz, Lee Downey, Oded Carmi and others). Our relief, and later, recovery programs in response to the Tsunami are now focused on two fronts. One is direct aid from Medan by road to areas around Banda Aceh. The other is this remarkable joint effort (nothing short of heroic), to the islands off the west coast of Sumatra, which as of yet, have not been receiving aid from any other channels that we know of. Read more at www.acehaid.org.


posted on Friday, June 3, 2005 5:58:55 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Wednesday, June 1, 2005

The Palestine Information Center (http://www.pitcenter.org) is a passion of mine. A very courageous friend of mine in the West Bank, Jihad Hammad, has created the PIT and put in most of the hard work so far. I am the Development Director and also sit on the Advisory Board (along with a few others you may recognize from this blog like Richard, Clemens and Goksin). I have donated a lot of my time and money to this effort in a bid to get it off the ground and off the ground it is!

The vision is simple. Create an IT Community/Training Center in a place that desperately needs one. Hold classes to get people certified in MS technologies (as well as Java and Linux). Provide a library and lifetime learning resource. A bridge to industry. Then mid to long term, have the center partner with local government, business and university to have the newly certified people work on projects under the direction of project managers at the center. The center can be a software and networking resource for the local industry and government. We would like to produce professional “white collar” workers in an economy that needs them, but also partner with the local industry to more easily place these newly trained people on projects.

The Al-Quds Open University has offered PIT the opportunity to use their lab free of charge for this beginning phase. The PIT curriculum would differ from the typical University Computer Science department courses as it would seek industry leaders to teach the courses with the goal of the courses to be industry certification and lifelong learning. We have four workshops under way. The first one is an Excel class to get started. It is in progress this week!

The Execl class in progress is for the Bethlehem Arab Society for Rehabilitation (http://www.basr.org/) Ultimately we plan to target the MSCD and MSCE curriculum. For now we are just getting interest in the center amongst the population and the university students.

posted on Wednesday, June 1, 2005 7:07:27 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [2] Trackback

In the Netherlands today for the final day of the SDC and the NO vote to the EU charter was not a surprise after several long conversations with locals. after the vote they took us to play some paintball. Somehow I managed to get shot a lot. :)

posted on Wednesday, June 1, 2005 7:02:42 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [1] Trackback
# Tuesday, May 31, 2005


So last night during the geek night session at the SDC, the Dutch, inspired by Richard Campbell called me on my SMO Backup and Restore GUI that had a progress meter. They thought I was hacking it, not that I was actually providing a true representation of the progress made by the status of the backup. Here is the progress meter in action, as the database backup makes progress we update the progress meter:




To do a backup programmatically you can to use SMO (see yesterday). Begin by setting the variables to get started.


Server svr = new Server();//assuming local server

Backup bkp = new Backup();


Cursor = Cursors.WaitCursor;


Then you have to set the device to backup to and what database to backup. Notice in the comments the code to the progress meter




      string strFileName = txtFileName.Text.ToString();

      string strDatabaseName = txtDatabase.Text.ToString();


      bkp.Action = BackupActionType.Database;

      bkp.Database = strDatabaseName;


      //set the device: File, Tape, etc

      bkp.Devices.AddDevice(strFileName, DeviceType.File);

      //set this when you want to do Incremental

      bkp.Incremental = chkIncremental.Checked;


      //progress meter stuff

      progressBar1.Value = 0;

      progressBar1.Maximum = 100;

progressBar1.Value = 10;


      //this gives us the % complete by handling the event

      //provided by SMO on the percent complete, we will

      //update the progress meter in the event handler


      //set the progress meter to 10% by default

bkp.PercentCompleteNotification = 10;

//call to the event handler to incriment the progress meter

bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);


//this does the backup


      //alert the user when it is all done

      MessageBox.Show("Database Backed Up To: " + strFileName, "SMO Demos");





catch (SmoException exSMO)





catch (Exception ex)







      Cursor = Cursors.Default;

      progressBar1.Value = 0;




Here is the ProgressEventHandler, notice that I made it generic enough that I can call it from both the backup and restore methods!


public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)


            //increase the progress bar up by the percent

            progressBar1.Value = e.Percent;



posted on Tuesday, May 31, 2005 6:28:53 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [1] Trackback
# Monday, May 30, 2005

The Software Developers Conference in the Netherlands has begun. Today I show how to prevent SQL Injection Attacks in ASP .NET as well as other cool tricks. Lots of RDs here and lots of happy attendees. As usual at SDC I will show something very new and cool. I will show off some of the new SQL Server Management Objects (SMO) in the keynote tonight. Ill give you a preview here.

The SMO object model is a logical continuation of the work done in SQL-DMO. SMO is feature-compatible with SQL-DMO, containing many of the same objects. . To achieve maximum data definition language (DDL) and administrative coverage for SQL Server 2005, SMO adds more than 150 new classes. The primary advantages of SMO are in its performance and scalability. SMO has a cached object model, which allows you to change several properties of an object before effecting the changes to SQL Server. As a result, SMO makes fewer round trips to the server, and makes its objects more flexible. SMO also has optimized instantiation, meaning that you can partially or fully instantiate objects. You can load many objects quickly by not instantiating all the properties of the objects. To get started you have to set a reference to it and pull in the namespace:

using Microsoft.SqlServer.Management.Smo;

Now I will show you how to programitically do a database restore. You start with getting the SMO objects: Server and Restore.

Server svr = new Server();

Restore res = new Restore();

Now take a look at how easy you can do a restore, just a few lines of code:

res.Database = “AdventureWorks“;

res.Action = RestoreActionType.Database;

res.Devices.AddDevice(“c:\mybackup.bak“, DeviceType.File);

res.ReplaceDatabase = true;


There is a lot more that you can do with SMO, but this shows you how easy it is to manage your server from code. A very cool thing to do it put some of the server monitor stuff into an ASP .NET page for viewing your server stats from a remote location.

More on SMO to come...


posted on Monday, May 30, 2005 7:32:15 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Saturday, May 28, 2005

That is the title of Richard and my Monday night keynote at the Software Developers Confrence in the Netherlands. Well I think it is now: “Estaban, splain dis Jukon to me!“ where we talk all about SQL Server 2005 in bad Spanish accents. To tell you the truth we have no idea what we are going to talk about, we do know that it will  be fun, contain beer and technical content. Confrences in the Netherlands rock.

Off to the Red Lights...

posted on Saturday, May 28, 2005 7:33:16 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [1] Trackback