Jacob's Blog on Beyond Relational

Getting ready for PASS Summit, See you in Seattle

Jacob Sebastian — Sat, 31 Oct 2009 00:25:28 GMT

I am wrapping up my work in NY and getting ready to go to Seattle for PASS Summit. I will be in Seattle from Sunday afternoon (1 Nov 2009) to Saturday morning (7 Nov 2009). I look forward to see many of you during PASS Summit.

Contacting Me

Email is the best way to setup a meeting time if you would like to meet me. However, it is not mandatory; If you see me around, just feel free to jump in and ‘interrupt’.

My talk on Creating XML Schema Collections

I will be speaking on Creating XML Schema Collections and the session is on Tuesday (3 Nov 2009) 4.30 PM to 5.45 PM in Room Number 612. We will go over the basics of XML Schema collections and see a number of demos that perform a variety of validations on XML Documents. A few of you will be able to grab copies of my book The Art Of XSD – SQL Server XML Schema Collections during my session.

Join me at Birds of a feather (XML) Lunch on Tuesday

Before you read further, I would suggest you read Mike Walsh’s post on what is Birds of a feather lunch. I will be hosting the XML table and nine of you can join me for lunch on Tuesday. We will discuss SQL Server and XML. I will not be the only one talking at the table, in fact, all of us will be discussing, asking and answering questions, praising and cursing XML. I look forward to meet many of you at the Lunch Table on Tuesday.

Join Pinal Dave at Birds of a feather (CDC) Lunch on Tuesday

Pinal Dave will be hosting the CDC lunch table. Those of you wants to take part in the Change Data Capture (CDC) discussion, please be at his table (which will be close to my table) ‘on time’ (honestly, he is a SQL Server Star and if you are late by a minute, you might miss the opportunity to lunch with him).

See you at PASS Summit.

SQL Server Saturday at Microsoft Iselin

Jacob Sebastian — Sun, 25 Oct 2009 19:54:09 GMT

I am just back from the the SQL Server Saturday event (Microsoft Iselin) and had the honor of presenting with Michael Coles and Robert Pearl, two great SQL Server Experts of New York. It was a fantastic event with close to 100 SQL Server developers and DBAs, which triggered a lot of interesting discussions around various aspects of SQL Server.

Getting to the Venue

Robert was kind enough to pick me up in the morning from Brooklyn, along with my colleague Joe. None of us were familiar with the location and neither Google Map nor Map Quest helped us to to get to the venue on time. After driving around New Jersey for close to an hour, we finally located the Microsoft Building.

Michael Coles and SQL Server Spatial Support

I missed the first part of Michael’s interesting presentation. Michael explained SQL Server 2008’s spatial data support in great detail along with a number of very interesting demos. Instead of explaining the spatial features by showing a world map and places that does not make sense to most of us (that is what I always saw elsewhere), he really demonstrated the spatial features on New York’s own Road Maps and Subway Maps which made the presentation very interesting and valuable.

I had been exploring the spatial support recently to add some of the spatial features to our home health care solution HhaeXchange (http://www.hhaexchange.com). Many of the examples that Michael demonstrated were very close to what I was looking for, and it reduced my work tremendously.

Thank you Michael for putting up a great presentation, giving me a copy of all your demos and the spatial database you created with a lot of hard work. Most importantly, thank you for driving me back to New York (Michael lives in New Jersey), I know it should have taken you several hours to get back to home after dropping me.

Michael authored close to a dozen great books on SQL Server. I would recommend reading them, especially the following books.

Robert Pearl and SQL Server Auditing

Robert Pearl presented a very interesting session on SQL Server Auditing. He explained how to configure auditing in SQL Server, how to configure Change Data Capture and walked through tracing and management of transaction logs.

Robert had been doing a lot of work in SQL Server internals. He has a web based SQL Server Monitoring Product, SQL Centric, which is capable of monitoring one or more SQL Server databases and can send you notification/alert emails in case something goes wrong.

Thank you Robert for the insights you provided into SQL Server storage engine and Transaction Log Processing.

Jacob Sebastian and “Best Practices for Error Handling and Defensive Programming in SQL Server”.

I am very glad to know that every one found the session very interesting and helpful. Thank you Melissa for your complement “SQL Server Fire Cracker”.

Jacob and Melissa

Thank you Michael for walking around and clicking all the photographs.

Gifts and Prizes

A lot of gifts and prizes were distributed among the attendees which included several autographed copies of my book The Art of XSD – SQL Server XML Schema Collections, Michael’s book Pro T-SQL 2008 Programmer’s Guide, several copies of Windows 7, including a Signature Edition copy, T-shirts from Red-Gate and many more.

Thank you and don’t forget to rate my talk

I would like thank everyone for attending the session. I look forward to hear your feedback (though many of you talked to me after the session), please go to http://speakerrate.com/talks/1556-best-practices-for-exception-handling-and-defensive-programming-in-microsoft-sql-server4 and rate my talk there.

See you in New Jersey SQL Server UG Meeting on 24th October 2009

Jacob Sebastian — Tue, 20 Oct 2009 03:50:51 GMT

I have not been able to write on my blog for quite some time. I am currently in NY for an onsite assignment and have been quite busy lately. I hope my life will get back to normal soon :-)

I will be presenting at the New Jersey SQL Server User Group on 24th October 2009 on “Best practices for exception handling and defensive programming in SQL Server”. I am glad to have the honor of presenting with two great SQL guys, Michael Coles and Robert Perl.

Event Details

Date: Saturday, October 24^th
Time: 9:00am - 3:00pm (Sign-in begins at 8:15am)
Location: Microsoft Office (Iselin, NJ)
Registration Link: https://www.clicktoattend.com/invitation.aspx?code=141878

Speakers and Sessions

Michael Coles ["Where in the World?" -- Spatial Data in SQL Server 2008 ]

SQL Server 2008 introduces two new core data types for storage and manipulation of geospatial data. You can use SQL Server's built-in library of spatial operations to perform complex geographic calculations quickly and easily, and interface the spatial data types with front end mapping tools. The R2 release of SQL Server 2008 adds even more spatial data functionality to the Microsoft BI stack via Reporting Services improvements. In this session attendees will learn how to store, manipulate, and perform calculations with geospatial data in SQL Server to enrich their applications.

Robert Pearl [Who Did it and Ran?']

Robert Pearl brings us a SQL caper that takes us on a journey through the depths of the sql server internals world, putting us on the trail of who did what and when to your database. This presentation will be an exercise in database forensics that will apply computer investigation and analysis techniques to set up auditing and evidence collection, by leveraging the native SQL Server features out-of-the-box. We'll explore the Transaction Log, SQL 2008's Change Data Capture (CDC), and other methods to secure your database (to put us in compliance (ie: SOX, HIPAA, etc.)

Jacob Sebastian [Best practices for exception handling and defensive programming in SQL Server ]

This session focuses on understanding errors and error handling in SQL Server. We will see a number of demos that show how to detect errors, control errors and handle errors. It also focuses on exposing some of the common programming mistakes and pitfalls.

I hope to see many of you at the event.

Community Tech-Days Ahmedabad – Thank you for attending!

Jacob Sebastian — Sun, 11 Oct 2009 23:53:29 GMT

Though I wanted to write a blog post with a quick summary of Community Tech-days Ahmedabad event, I could not really do that for the last several days because I was travelling. I had to leave right after the event for New York for a 4 week on-site assignment.

A few of my friends have already written great blog posts covering the event in great details.

Vinod Kumar’s Blog - A’bad UG CTD and Gandhi Ashram
Pinal Dave’s Blog - SQLAuthority News – Community TechDays in Ahmedabad – A Successful Event
Ritesh Shah’s Blog - Microsoft Community Tech Day in Ahmedabad on 3rd OCT 2009
Tejas Shah’s Blog - Community TechDays at Ahmedabad - Great Event
Imran’s Blog - Overview of new features in windows 7 - MTD Session

I had a great time meeting and having a quick chat with many of you. It was a pleasure meeting some of you after so many years. Thank you for attending Community Tech-days.

Thank you Vinod Kumar, Pinal Dave and Prabhjoth Bakshi for making it a great event. Special thanks goes to the guys at Microsoft who agreed to bring the event to ahmedabad and to my friend Ashwin Kini who coordinated the event.

Vinod Kumar Presenting on Windows7 and Office 2010

Pinal Dave presenting on the ‘Other side of Indexes’

Jacob Sebastian presenting on ‘Best practices for exception handling in SQL Server’

Prabhjotsingh Bakshi presenting on .NET 4.0

I got several emails asking for the the PPTS and the sample scripts that I used in the presentation. I will make them available for download soon. I request those of you who attended my session, to go to http://speakerrate.com/talks/1473-best-practices-for-exception-handling-and-defensive-programming-in-microsoft-sql-server and rate the talk there.

Community Tech-Days Ahmedabad on 3rd October

Jacob Sebastian — Wed, 30 Sep 2009 04:06:52 GMT

Community Tech-Days is coming to Ahmedabad again. The event is scheduled to be on 3rd October 2009. I look forward to see many of you in the venue on 3rd October.

What is Community Tech-Days?

Community Tech-Days is a technology event organized by Microsoft to help IT Professionals and developers catch up with the latest technologies and development methodologies. This event is pretty much close to the ‘Tech-ED On the Road’ we had recently.

The Community Tech-Days website says “Dive deep into the world of Microsoft technologies at the Community TechDays and get trained on the latest from Microsoft. Build real connections with Microsoft experts and community members, and gain the inspiration and skills needed to maximize your impact on your organization while enhancing your career.”

I agree with the description 100%.

Click here to register for the event.

Who are the speakers and what are the topics?

I am very excited about the Community Tech-days, some of the top speakers in the industry will be speaking on a number of very interesting subjects.

Vinod Kumar, Microsoft India (Tips and Tricks with Office 2010, Windows 7 – A Dream Operating System for IT Profession)

Vinod Kumar works as a Technology Evangelist (Databases and BI) with Microsoft India specializing primarily on SQL Server. With the love and passion for SQL Server he would want to help the community to maximize their investments on SQL Server by utilizing the product in the right way in their design, development, architecture and deployments. He has been working in the industry on various Microsoft technologies for the past 8+ years and been MVP - SQL Server for more than 3+ years before joining Microsoft. He has been a regular speaker at many of the MS Events like TechEd, MSDN, TechNet and many more. Also loves the local Microsoft Communities and has helped them in every single opportunity available. Apart from SQL, his part-time love is around Office suite of products which he plays a lot with.

Pinal Dave, SQL Server MVP (SQL Server 2008 - The other side of Index)

Pinal Kumar Dave is a Microsoft SQL Server MVP and a prominent expert on SQL Server technologies. He has written over 1000 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia and is also Mentor for Solid Quality India.

Jacob Sebastian, SQL Server MVP (Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server)

Who is this guy??? I think I have seen him somewhere ;-)

Prabhjot Singh Bakshi, Professional Trainer (.NET Framework 4.0)

A Microsoft Certified Trainer, Co-Founder and Executive Director of Dubai(UAE) based, E- Consultancy FZC, a Trainers outfit which pioneering to teach over internet across the globe. Also imparting Corporate training to Professionals of leading IT Companies, including IBM, Microsoft India, Accenture, Cap Gemini, Intel, Scope(Standard Chartered), Mcafee , Royal Bank of Scotland, KPIT Cumins, Wipro, HCL, CSC, Computer Society of India, Patni, Ness, Mphasis, Polaris, ICICI, 3iInfotech, L&T Infotech, Kambay, Virtusa, Aces Goa, Logica, Solid Exact Solution(Holland), SMTC(Dubai), Ceridian (Mauritius) and many more. He interacts with the IT professionals and assists them to adapt and horn their skills on what’s new.

Date, time and location

Community Tech-Days Ahmedabad starts on 3rd October 2009 from 9.15 AM onwards. It is a full day event with 5 sessions on core ITPro/Dev topics.

Location: Hotel Anmol, H l College Lane, Opp Bank of Baroda, Navrangpura. Ahmedabad – 380009

SQL Quiz: My Journey Towards the SQL Server World

Jacob Sebastian — Wed, 23 Sep 2009 12:06:12 GMT

My friend and SQL Server MVP, Jonathan Kehayias (@SQLSarg) tagged me and it is now my turn to disclose the path that I travelled to reach the SQL Server World.

After I read Jonathan’s post, I tried to travel backward through my career in the IT industry. It was fun, very interesting and emotional.

I never got a chance to touch a computer in the school. My first exposure to computers took place in 1990 at Rogate Ashram, India, a religious house run by Rogationist Fathers from Italy. Head of the Ashram, Fr. Vito Antonio Lipari gave me the first lessons of computers in 1990 and I picked them up pretty well.

Fr. Vito Antonio Lipari

Fr. Vito had a Toshiba laptop and that was what we used for the initial training. I don’t remember the model or the ram, hard-disk size etc, but it was a cute little laptop that I liked very much. (I am glad that I could find an old photograph of the laptop from my photo album)

The Toshiba Laptop that I first worked with

The computer was equipped with 5 software programs.

Wordstar - A word processing software like MS Word.
Lotus 123 - A spread sheet application, like MS Excel,
Professional File - A software that provides an interface to create data entry screens and view reports.
Q&A – An italian softare which is used to generate quizzes, where you can enter objective type questions and their answers. The software randomly generates test papers that can be printed and given to the students.
Banner – A software that allows to format/shape text and print in a variety of attractive fonts and layouts.
DBase – A relational database management software

That was a very humble beginning and then I got a number of opportunities to work with DBase and that is when I developed an interest in database management systems. The journey continued to FoxBase and FoxPro in the next few years.

FoxPro was my favorite database management system those days. I wanted to explore the capabilities of FoxPro to the maximum. However, very few learning resources were available (and accessible to me) at that time. I could find no good FoxPro book in the local book store. Internet access was not easy.

Finally I came up with my own learning resource. I created a Foxpro report from the internal help file of foxpro (foxhelp.dbf) and then generated a text file output from the report. It generated a large text file and I managed to print them all. There were close to 700 pages and I took them to a local book binding store and created a reference book out of it.

Honestly, that was the first and last book I studied from the first page to the last page. I was not been that sincere even when I was in the school. None of the books I purchased after that were read/studied completely.

I moved to a new company in those days and soon had to switch to Clipper. It was not hard to pick up because a major portion of FoxPro commands were supported in clipper too. After working with Clipper for close to two years, I switched to Visual Foxpro for a short while.

From Visual Foxpro, I moved to SQL Server 6.5 in 1997. That was my first meeting with SQL Server. We then moved to SQL Server 7.0 and then to 2000. I got the opportunity to work on a number of large SQL Server 2000 projects having large databases and complex business requirements.

The experience I gained from designing and building applications in Foxpro/Clipper for verticals such as manufacturing, banking, insurance etc, has helped me tremendously in the later part of my career. I am deeply indebted to those colleagues and seniors who have contributed directly or indirectly towards making me a better Relational Database Citizen.

Let the game continue and let me Tag:

My Presentation at “24 Hours of PASS” event - “Tips and Tricks for writing SET based queries”

Jacob Sebastian — Fri, 04 Sep 2009 11:51:21 GMT

It was a pleasure presenting at ‘24 Hours PASS’ event this morning and I would like to thank all of you who attended the session. For those who missed the session, the recorded version will be available at PASS website soon.

What is “24 Hour PASS” event?

“24 Hour PASS” event (24HOP) is a 24 hour FREE SQL Server Training Webcast organized by the Professional Association for SQL Server (PASS), an independent, not-for-profit association dedicated to supporting, educating, and promoting the Microsoft SQL Server Community.

Twenty Four speakers from all over the world presented 24 sessions – one session every hour – starting from 00:00 hours GMT on 2nd September 2009. Eminent speakers like Allen White, Gail Shaw, Greg Low, Kalen Delaney, Kevin Kline, Louis Davidson, Paul Nielsen, Steve Jones etc made the 24HOP a must-attend session for every SQL Server professional.

Community Rocks!

What is so lovely about this event was the amount of effort volunteers from PASS HQ, PASS Chapter and SQL Server community have put into making it a big success. Each session was hosted and moderated by a PASS chapter. People at like Rick, Blythe, Chuck at PASS HQ have worked round the clock to make sure that everything is well organized. A large number of volunteers from PASS Chapters across the world showed their commitment to the SQL Server community by being the host and moderators of each session. These moderators assisted the attendees and presenter by managing the live meeting, helping people having trouble with using the live meeting interface, assisting them with Q&A etc.

You may be surprised when I say that each session was supported by a ‘backup-moderator’ and a number of ‘backup-bakup-moderators’. That shows the spirit :-)

My Session: Tips and Tricks for writing SET based queries

My session - “Tips and Tricks for writing SET based queries” - focused on exposing some of the tips and tricks for writing efficient TSQL queries that solves common business problems using a SET based approach. In most cases, a SET based query may be found to be a better choice over a piece of PROCEDURAL code.

My session included dozens of TSQL demos that presented a number of ways of writing TSQL queries to solve common business problems. We examined the different versions of the queries, starting with a simple version and incrementally presented better options resulting in the best practice solution to the problems

Download the demo scripts

I have uploaded all the demo scripts we examined during the presentation and you can download it from here.

Feedback and rating

I will be very happy to hear your feedback and please do send me emails with your comments. If you have attended the session, please rate my talk at http://speakerrate.com/talks/1434-tips-and-tricks-for-writing-set-based-queries

XQuery Lab 49 – Deleting rows from a table based on the data in an XML document

Jacob Sebastian — Wed, 26 Aug 2009 07:24:00 GMT

We have seen a few examples that demonstrated how to delete elements and attributes from XML documents. In this lab, let us see how to delete rows from a table, based on the data in an XML document.

DECLARE @t TABLE (id INT)
INSERT INTO @t(id) 
SELECT 1 UNION ALL 
SELECT 2 UNION ALL 
SELECT 3

SELECT * FROM @t 
/*
id
-----------
1
2
3
*/

declare @XmlData xml 
set  @XmlData = '
<PersonalInformationObject>
   <Skills>
    <SkillObject>
        <SkillId>1</SkillId>
    </SkillObject>
    <SkillObject>
        <SkillId>2</SkillId>
    </SkillObject>
    </Skills>
</PersonalInformationObject>'

DELETE t
FROM @t t
CROSS APPLY @XmlData.nodes('
    /PersonalInformationObject/Skills/SkillObject/SkillId
    [. = sql:column("id")]')
 a(x)

SELECT * FROM @t 
/*
id
-----------
3
*/

Note the usage of CROSS APPLY and the way the join is established between the table and the XML document using the sql:column() function within the XQuery expression.

Previous Lab: XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Finally, received my SQL Azure Invitation Code!

Jacob Sebastian — Tue, 25 Aug 2009 02:22:57 GMT

After waiting for much longer than I anticipated, I received my SQL Azure Invitation Code! One of the questions most frequently asked in the Azure forums is from anxious users like me who wanted to know when the invitations codes will be sent. Zach wrote a blog post that answers the questions of those of you who are still waiting for the invitation code. You can find his blog post here: http://english.zachskylesowens.net/2009/08/19/sql-azure-invitation-codes/

XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project

Jacob Sebastian — Sat, 22 Aug 2009 10:36:00 GMT

I was working on my presentation for 24-hour-pass event on 2nd September and came across the sorting issue with the solution explorer. I have a number of script demos and I wanted to organize them by name. No matter, what I did, the solution explorer continued to display the script files in its own order, and not in the way I wanted them.

My Frustration increased when I opened the project XML file and found that the XML element is set to be “sorted”, but the sorting flag has no effect in the way SSMS displayed the items.

<LogicalFolder Name="Queries" Type="0" Sorted="true">

Though the “Sorted” attribute is set to “true”, no sorting took effect in the SSMS. As a quick work around, I opened the “.ssmssqlproj” XML file in an XML editor and modified the physical order of the elements in the project file. I saved the file and after I reopened the project, the query files were displayed in the correct order.

I felt so bad about this behavior and thought of writing a script that can automate this process the next time I need this. So I wrote a stored procedure that loads the content of the project file and order it and outputs a file with the sorted items.

Here is the content of the stored procedure that performs this.

CREATE PROCEDURE SortSSMSProjectFiles
(
    @ProjectFileName VARCHAR(512)
)
AS

DECLARE @x XML, @qry NVARCHAR(500), @param NVARCHAR(100)
SELECT @qry = N'
    SELECT 
        @x = CAST(bulkcolumn AS XML) 
    FROM OPENROWSET(BULK ''' + @ProjectFileName + ''', 
        SINGLE_BLOB) AS x'
PRINT @qry
SELECT @param = '@x XML OUTPUT'
EXECUTE sp_executesql @qry, @param, @x OUTPUT 

SELECT @x.query('
<SqlWorkbenchSqlProject 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    Name="{SqlWorkbenchSqlProject/@Name}">
  <Items>
    {
        for $lf in SqlWorkbenchSqlProject/Items/LogicalFolder
        return 
            if ($lf/@Name = "Queries") 
            then 
                <LogicalFolder Name="{$lf/@Name}" 
                    Type="{$lf/@Type}" Sorted="{$lf/@Sorted}">
                    <Items>
                        {
                            for $i in $lf/Items/*
                            order by $i/@Name
                            return $i
                        }
                    </Items>
                </LogicalFolder>
            else $lf
    }
  </Items>
</SqlWorkbenchSqlProject>    
')

This is how you can execute this stored procedure

EXECUTE SortSSMSProjectFiles
    @ProjectFileName = 'C:\temp\demo\demo\demo.ssmssqlproj'

The @ProjectFileName should point to the “.ssmssqlproj” file of your project. This stored procedure will produce an XML document. You can open it in SSMS and use “File->save as” menu to overwrite your existing project file.

If you want to completely automate this, you can use osql.exe or sqlcmd.exe to execute this stored procedure and generate an output file in the desired location.

To demonstrate this problem, I created a demo project and added some files in random order. Here is how SSMS displays my files now.

I wanted to get them organized and I ran the project file through my stored procedure. The stored procedure updated the project file and here is how the files are displayed in SSMS after the change.

I tested it with my projects and it works. I would like to hear your comments and suggestions on this script.

Next Lab: XQuery Lab 49 – Deleting rows from a table based on the data in an XML document

Previous Lab: XQuery Lab 47 – Generating HTML table from XML Data

View all labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SSRS 2008 R2 and ATOM Data Feeds

Jacob Sebastian — Fri, 21 Aug 2009 15:25:31 GMT

While going over the ‘What is new’ part of SQL Server 2008 R2 Books Online, I found a reference to generating ATOM Data Feeds. It appeared that I could crate a report and then generate the data as an ATOM Data Feed.

I found it very interesting. I was very excited with the idea of generating and publishing ATOM feeds using reporting services.

I have had a very close interaction with the ATOM format while working on the ATOM.NET project, a .NET library for generating ATOM feeds. After that I a couple of articles at SQLServerCentral that explained how to generate an ATOM Feed from TSQL.

I was very curious to know how the atom feed generation and publishing works. I had a tough time trying to configure a few SSRS 2008 installations to have reports accessible over internet. It was quite easy to configure SSRS 2005 reports to be accessible over internet. But is not anymore with SSRS 2008. So I was really curious how the ATOM feeds will be consumed by feed readers.

Reading further on Books online did not give me any clear information about this and finally I did some googling and binging and came across this article that indicates that the SSRS ATOM Data Feeds are not really meant for the poor feed readers, but it is designed to serve data to a number of higher level BI tools.

I could not figure it out from Books Online. May be I am missing something or it could be the the BOL documentation.

Resources to understand Locks in SQL Server

Jacob Sebastian — Fri, 21 Aug 2009 06:04:47 GMT

I have recently seen a number of questions on locking in the Database Engine Forums. Shankar Reddy and oj has shared links to a few interesting whitepapers and articles that helps to understand how locking works in SQL Server.

Registration for SQL Azure Database CTP is open

Jacob Sebastian — Wed, 19 Aug 2009 03:26:19 GMT

If you have been waiting for SQL Azure (formerly known as SSDS) Database CTP, there is a good news. Registration for SQL Azure Database CTP is open: http://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

Key features with this CTP:

Relational Data model supporting TSQL, including stored procedures
Self-provisioning, auto-healing and disaster recovery, with high availability with no physical database administration.

See the SQL Azure Team Blog post: http://blogs.msdn.com/ssds/archive/2009/08/18/9874133.aspx

XQuery Lab 47 – Generating HTML table from XML Data

Jacob Sebastian — Wed, 19 Aug 2009 03:00:00 GMT

Welcome to XQuery Lab 47. In this lab, we will see how to generate an HTML table from an XML document, using XQuery.

Here is the source data:

<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />

Here is the output required

<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>

Here is the TSQL code using XQuery to generate the required output.

DECLARE @x XML
SELECT @x = '
<CUST COMPANY="Company1" CONTACT="Jacob" />
<CUST COMPANY="Company1" CONTACT="Michael" />
<CUST COMPANY="Company3" CONTACT="Steve" />'

SELECT
    @x.query('
        <table>
            <tr>
                <td>Company</td>
                <td>Contact</td>
            </tr>
            {
                for $r in CUST
                return
                <tr>
                    <td>{data($r/@COMPANY)}</td>
                    <td>{data($r/@CONTACT)}</td>
                </tr>
            }
        </table>
')

/*
<table>
  <tr>
    <td>Company</td>
    <td>Contact</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Jacob</td>
  </tr>
  <tr>
    <td>Company1</td>
    <td>Michael</td>
  </tr>
  <tr>
    <td>Company3</td>
    <td>Steve</td>
  </tr>
</table>
*/

Next Lab: XQuery Lab 48 - Sorting Query files in SQL Server Management Studio (SSMS) Solution/Project

Previous Lab: XQuery Lab 46 – Extracting Zip Code from an Address Value

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

SQL Server 2008 R2 August CTP – What is new in Database Engine Features?

Jacob Sebastian — Tue, 18 Aug 2009 06:19:07 GMT

SQL Server 2008 R2 is a minor release and the number of features and enhancements added to the database engine is very limited. The public CTP is available for download at http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

The following are the main Database Engine Features added in SQL Server 2008 R2 August CTP

SQL Server Utility – It allows creating a SQL Server Utility Control Point (UCP) which provides the ability for centralized management and consolidation of SQL Server instances and data-tier applications. SQL Server Management Studio has additional explorer windows ‘Utility Explorer’ and ‘Utility Explorer Content’ for working with Utility Control Point. More information can be found here: http://msdn.microsoft.com/en-us/library/ee210548(SQL.105).aspx
Support for more than 64 CPUs - The number of CPU cores that a server can use for database operations has been increased from 64 to 512
Unicode Compression - Unicode data that is stored in nvarchar(n) and nchar(n) columns is compressed by using an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm. A number of bloggers have blogged about this feature.

Did I miss something? Please feel free to add a comment.