Wednesday, February 28, 2007

LINQ to SQL Feature Article in VSJ Magazine (UK)

"Next generation data access with LINQ" by UK developer Granville Barnett is a feature article about LINQ to SQL in VSJ Magazine's February 2007 issue. VSJ is a print publication that offers .NET coverage similar to 1105 Media's Visual Studio Magazine but—like the former FTP newsletters—includes articles in Java, XML and Web Services, Database, and Architecture Zones.

Update 4/25/2007: VSJ Magazine published Granville's second article, "Further exploration with LINQ" on April 1, 2007 covers stored procedures for LINQ to SQL data access, LINQ to SQL, and combining LINQ to XML with LINQ to SQL.

Granville also wrote C# 3.0: An Introduction" for The article briefly explains object initialization, type inference, anonymous types, lambda expressions, extension methods, LINQ queries, and expression trees with simple C# code snippet.

Update 3/12/2007: The first installment of Granville Barnett's "Introducing LINQ" series appeared today on Granville says the article covers:

  • Querying of in-memory collections
  • A subset of the standard query operators
  • Compiler generated code including a discussion on "under the covers" compiler behaviour like cached anonymous delegates and delegates
  • Lambda Expressions

Update 4/11/2007: The second installment, Introducing LINQ – Part 2, offers a simple example of using LINQ to SQL's Object/Relational Designer to create a simple LINQ to SQL data access layer (DAL).

Update 4/25/2007: Introducing LINQ – Part 3 digs deeper into LINQ to SQL enities and stored procedures for retrieving data from the underlying SQL Server data store.

Granville's blog offers several LINQ tutorials implemented in C# 3.0.

Technorati tags: , ,

Orcas March CTP Available from MSDN

Microsoft released on February 28, 2007 the Microsoft Pre-release Software Visual Studio Code Name "Orcas" - March Community Technology Preview (CTP) as a self-extracting install or VirtualPC image. The Release Notes are here. According to the Overview on the download page, highlights for LINQ and ADO.NET 3.0 are:

  • LINQ

      The LINQ Project: this CTP represents a major milestone in the LINQ project. For more information about LINQ click here

    • VB 9.0 Language Support: This CTP contains the following language features:
      • Query Expressions: Basic querying, filtering, and ordering support
      • Object Initializers
      • Extension Methods
      • Local Variable Type Inference
      • Anonymous Types
      • XML literals
      • XML properties
      • New Line and Expression IntelliSense
    • C# 3.0 Language Support: This CTP implements all of the C#3.0 language features from the May LINQ CTP including:
      • Query Expressions
      • Object and Collection Initializers
      • Extension Methods
      • Local Variable Type Inference and Anonymous Types
      • Lambdas bound to Delegates and Expression trees
      • Complete design-time support: IntelliSense, Formatting, Colorization
    • LINQ to ADO.NET
      • ADO.NET is fully integrated with LINQ and offers many options for using LINQ in various scenarios: LINQ to SQL provides direct access to database tables from the programming environment, LINQ to Entities enables developers to use LINQ over EDM models, and LINQ to DataSet allows the full expressivity of LINQ to be used over DataSets.
      • LINQ to Entities enables developers to program against a relational database using a view of the data that is appropriate for the application they are building, independent of the structure of the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. LINQ to Entities is built on the ADO.NET Provider model and will support working against different back end relational stores in addition to Microsoft SQL Server. This CTP includes a LINQ to Entities provider for SQL Server and SQL Server Compact Edition.
      • LINQ to SQL (previous name DLinq) has enhanced the functionality from the May 2006 LINQ CTP. You can find it in System.Data.Linq namespace in System.Data.Linq.dll. New in this release is that DataContext provides optimized modes for read-only use and serialization . Also new is that DataShape streamlines eager loading capabilities and adds the ability to set queries on relationships
    • LINQ To SQL Designer
      • Methods can be created from stored procedures and functions within the designer.
      • Better handling of database schemas.
      • Improved inheritance support in the designer.
    • LINQ over XML (XLinq)
      • System.Xml Bridge Classes added – There is a set of extension methods allowing XPath / XSLT to be used over LINQ to XML trees, allow XSLT transformations to produce an LINQ to XML tree, and to validate an XElement tree against an XML Schema.
      • Event Model - This allows LINQ to XML trees to be efficiently synchronized with a GUI, e.g. a Windows Presentation Foundation application
      • Class hierarchy changes - XObject class added, XStreamingElement class (temporarily) removed
      • Various understandability / usability improvements – There have been a number of relatively minor changes done in response to internal reviews, usability studies, and external feedback to make the API more clean and consistent.
    • LINQ to Objects API
      • The LINQ to Objects API supports queries over any .NET collection, such as arrays and Generic Lists. This API is defined in the System.Linq namespaces inside System.Core.dll.
    • Extended, more powerful data APIs with the ADO.NET Entity Framework
      • With the ADO.NET Entity Framework developers will be able to model the view of the data that is appropriate for each one of the applications they are building, independently of the structure of the data in the underlying database. The use of the Entity Data Model (EDM) enables developers to design models that follow the concepts built into the application, instead of having to map them to constructs available in relational stores. Once the model is in place, the powerful ADO.NET Entity Framework API is used to access and manipulate the data as .NET classes or as rows and columns, whatever is appropriate for each application.
    • Added paging and stored procedures for update (“update customization”) for ADO.NET Entity Framework:
      • Paging: the paging support in the ADO.NET Entity Framework allows developers to “page” over data in a database by indicating the start row and number of rows to be included in the result. Paging is available through Entity SQL (using the LIMIT AND SKIP keywords) and through the query-builder methods in the ObjectQuery <T> class (Top and Skip). In a future CTP the feature will also be enabled to be used in LINQ queries by means of the standard Take and Skip LINQ operators.
      • Stored-procedures for update customization: the Entity Framework by default automatically generates SQL statements for insert, update and delete operations when processing changes to entities in memory to be sent to the database. With the stored-procedures update customization feature developers have the option to override the automatic SQL generation and instead provide stored-procedures that will perform the insert, update and delete operations, which the system will call during entity change processing. Among other things, this enables scenarios where direct access to tables is restricted in the database and the only way to make changes to the data is through stored-procedures.
    • Microsoft Synchronization Services for ADO.NET
      • Provides an application programming interface (API) to synchronize data between data services and a local store. The Synchronization Services API is modeled after the ADO.NET data access APIs and gives you an intuitive way to synchronize data. It makes building applications for occasionally connected environments a logical extension of building applications where you can depend on a consistent network connection.

Supported operating systems are Windows Server 2003 and Windows XP. Vista support is conspicuous by its absence.

Update 3/1/2007 9:00 am: Microsoft modified the System Requirements section for the VirtualPC image as follows:

Note that the Orcas Base Image is Windows Server 2003 Enterprise Edition, not Windows Vista, for the Orcas January 2007 CTP. I haven't tested the current Orcas Base Image.

As of 9:45 am, the System Requirements section for the self-extracting install remained:

  • Supported Operating Systems: Windows Server 2003; Windows XP
  • Processor: 1.6GHz Pentium III+
  • RAM: 1 GB of available physical RAM

Lack of Vista support for the self-extracting install is surprising when you consider this post from Somasegar's blog: "Our goal with Orcas is to build the best tool set for Windows Vista ... For the first time, we have a WPF designer in the product, enabling a more complete WPF development experience."

Notice the missing SP2 requirement for Windows XP in both CTP versions.

Related Posts:

  • Charlie Calvert's C# blog has a link to a video about the CTP starring Luca Bolognese and other members of the LINQ team.
  • The XML Team's announcement is here.
  • The Visual Basic Team's annoucement is here.

Question of the Week: Where's Microsoft's VB.NET evangelist who corresponds to the C# group's Charlie Calvert?

The Feature Specifications for Visual Studio and .NET Framework "Orcas" hasn't been updated for the March 2007 CTP and contains only one reference to LINQ (the LINQ to XML Core Specification).

Tuesday, February 27, 2007

New LINQ to SQL Video from the LINQ Farm

Charlie Calvert has added a 7.5-minute video for LINQ to SQL beginners to the LINQ Farm. The video covers all the essential steps in creating a C# LINQ to SQL console application from the Northwind sample database's Customers and Orders tables using the DLinq Designer.

The video complements Charlie's earlier LINQ Farm: Connecting to a Database with LINQ to SQL post. My LINQ to SQL C# Primer post has links to Charlie's previous LINQ Farm posts and my additions to his Connecting to Northwind article.

The popularity of console apps to demo LINQ to SQL features is undoubtedly due to the KISS (Keep It Simple, Stupid) mandate, but adding a BindingNavigator and two BindingSource and DataGridView pairs to the project involves only a few mouse clicks. It's a good bet that most data-intensive Visual Studio projects that are candidates for LINQ to SQL use at least a few bound controls.

As an example, here's the UI for an early VB .NET LINQ to SQL (then DLinq) demo project with Customers, Orders, and Order Details grids that lets you update or delete rows and then apply the DataContext.SubmitChanges method to update the underlying tables. (Code is required to insert rows.)

(Click here for full-size image.)

In my books and articles, I generally take the time to provide a WinForm with bound DataGridView, text boxes, or code to prove that the LINQ objects I'm demonstrating support CRUD operations. It might take readers a few minutes more to complete the exercise but they also learn the capabilities (and limitations) of the collections they create.

LINQ Farm: One to Many and Joins with LINQ to SQL and Orcas March CTP

Update 2/28/2007: This primer on relationships and joins in the Northwind sample database is intended for relational database and SQL newbies. It just appeared on 2/28/2007 and must have been hibernating since its 2/20/2007 pub date. The source code for Charlie's last three LINQ to SQL posts requires the Orcas February 2007 CTP, "which should be out very soon."

Microsoft released on the morning of February 28, 2007 the Microsoft Pre-release Software Visual Studio Code Name "Orcas" - March Community Technology Preview (CTP). The "Orcas March CTP Available from MSDN" post has details for downloading a self-extracting install or VirtualPC image (VHD).

Thursday, February 22, 2007

Erik Meijer to Keynote QCon London Conference

Erik Meijer will deliver the "Democratizing the Cloud" keynote to the QCon Conference to be held March 12 - 16, 2007 in London. Erik, whom the conference management calls "the creator of LINQ," describes democratizing as:

[T]rying to stretch the .NET framework to cover the Cloud such that it will become possible to incrementally and seamlessly design, develop, and debug complex distributed applications using your favorite existing and unmodified .NET compiler and deploy these applications anywhere.

The Democratizing Backstory

You can preview Erik's keynote in a eponymous January 13, 2007 Channel9 video, and read a seminal January 24, 2006 post in Lambda the Ultimate: "Beyond LINQ: A Manifesto For Distributed Data-Intensive Programming." This post marks Erik's first known public use of the term democratizing in the context of programming distributed systems.

Erik runs Microsoft's Tesla incubation team that's tasked with democratizing the Cloud, "like Visual Basic democratized programming Windows many years ago." Erik chose VB as the project's language because "it's uniquely positioned" between strict, static-typed languages like C# and Java and loose lenient, dynamic-typed languages, such as Ruby and Python. The goal is to enable static typing when necessary and dynamic typing when appropriate.

One approach the Tesla team is investigating to give democratized VB applications greater reach is compiling CLR IL to JavaScript. An alternative is to compile IL to Perl 6 and then compile Perl to JavaScript with an existing implementation. The Perl 6 group also is working on compiling Perl 6 to the CLR IL.

Note: Another Tesla team incubation project is LINQ to XSD.

Update 3/16/2007: Read "LINQ 2.0" in Early Returns from QCon 2007, London, which provides links to reviews of Erik's keynote.

Update 4/24/2007: Read Mary Jo Foley's ‘Volta’: Microsoft’s dev platform in the Cloud? article about the "Live development platform" that Soma Somasegar plans to divulge at MIX 07. Read desicriptions of the four sessions that contain LINQ content in LINQ-Related Sessions at MIX 07.

Presentation: "Introduction to Microsoft Language Integrated Query (LINQ)"

Erik also will present a one-hour sales pitch for technical presentation about LINQ and Visual Studio vNext as part of the .NET Enterprise Development Track hosted by Ted Neward.

Note: Thanks to Mary Jo Foley for posting "Why the Sharp languages still matter" on her ZDNet All about Microsoft blog. The post includes links to her "Sharp Words with Microsoft's Erik Meijer" interview and "Look Sharp: How C#, F# and other experimental programming languages are driving next-generation development" article for Redmond Developer News.

Update 2/24/2007: Minor corrections.

Wednesday, February 21, 2007

An Open Letter to Joe Wilcox re Free Microsoft Software

Your "Virtual PC and the Value of Free" Microsoft Watch column didn't mention SQL Server 2005 Express Edition (SSX) SP2 and its predecessors, MSDE 1.0 and 2000. These freely-distributable databases undoubtedly are the most widely used of all "free" Microsoft application software, with the possible exception of the Jet database.*

The three free databases have limitations on the number of processors and amount of memory they can use, and MSDE versions had a query throttle. However, these restrictions don't impede their use in the majority of data-intensive Windows applications at the workgroup or divisional level. SSX SP2 runs only under Windows XP, Vista, or Server 2003. However, MSDE 1.0 also runs under Windows 95 and 98; MSDE 2000 runs under Windows 98 and XP but not Vista. SSX SP2 offers optional full-text search and Reporting Services features that weren't available in MSDE versions.

SQL Server 2005 Compact Edition (SSCE) v. 3.1 and higher is a free, lightweight relational database that can run on desktops, laptops, Tablet PCs and devices. (Previous versions were limited to Tablet PCs and devices). Free Microsoft Synchronization Services (now in the CTP stage) enable synchronizing SSCE client with SSX server databases.

Microsoft offers a free, graphical database management application for SSX and SSCE, SQL Server Management Studio Express (SSMSX) SP2, which is based on SQL Server 2005's SQL Server Management Studio.

Despite giving away SSX SP2 and its predecessors, the SQL Server segment has had the greatest revenue and profit growth of all Microsoft product lines for the past several quarters. Microsoft increased the license fee for SQL Server 2005 Standard edition by $1,000 over the SQL Server 2000 version.

Other Free Microsoft Applications

Another free Microsoft product you missed is Windows SharePoint Services (WSS) 2.0 and 3.0, which run under Windows Server 2003. WSS is a full-featured intranet-based collaboration and document-sharing application that also can be deployed on the Internet. WSS 3.0 is the foundation for the definitely not-free Microsoft Office SharePoint Server (MOSS) 2007.

SQL Server 2005 Express is the data store for Office Accounting Express (and probably for the forthcoming full version), as well as WSS 3.0. (MSDE 2000 was the data store for WSS 2.0). SSX also is the preferred data engine for Visual C# Express, Visual Basic Express, and Visual Web Developer Express.

Your article emphasizes Microsoft's free Virtual PC 2007, but Virtual Server 2005 R2 is free also.

Obviously, Microsoft's free offerings, including SSX SP2, Visual Studio Express editions, WSS 3.0, and Virtual Server 2005 R2 have a well-defined upgrade path. It's also understandable that these products run only under Windows and aren't distributed under an "open source" license.

Damnation by Faint Praise

Although your column doesn't directly castigate Microsoft for offering free products, you say:

Many factors account for Microsoft's success, but two longstanding business practices stand out: release of software that achieves a "good enough" standard and offer of lower-cost, or free software that enhances the value of its platform products like Windows.

The preceding sentence appears to me to be damnation by faint praise.

Question: How much better than "good enough" must software be to warrant a significantly higher price?

Your column quotes Paul DeGroot, an analyst with Directions on Microsoft.

"You can even see it with a product like SQL Server, by giving away OLAP," DeGroot said. "It doesn't generate direct revenue for [Microsoft], but it harms Oracle. It used to be a revenue stream for Oracle."

Question: Does harming Oracle "by giving away OLAP" violate federal or state statutes?

Bundling Discounts

You address the Office bundling discount issue with this observation:

When, in the early 1990s, WordPerfect was the dominate word processor, Microsoft responded by bundling together Word, Excel and PowerPoint as "Office." The three products together sold for about the same price as WordPerfect. The lower-cost tactic is most effective when the software is "good enough"—meaning covers the 70 to 80 percent of features most end users would use most of the time.

Microsoft has repeated this pattern over the years. I remember when in the later 1990s, computer stores sold Microsoft's FoxPro for about $100; meanwhile, competing products from companies like Borland sold for many hundreds of dollars more.

Microsoft introduced Access 1.0 in November 1992 as a standalone desktop database application priced at $99; dBASE IV 1.1 cost $795 at the time. According to the February 1994 "Desktop Batch Processing" paper by Jim Gray, formerly of Microsoft Research, Access was generating revenue of about US$300 million per year by a bit more than a year after its release. Microsoft created Office 95 Professional by adding Access to Office 95 Standard's Word, Excel, PowerPoint and Schedule+ applications, and $100 to the Estimated Retail Price.

Today, the list price of Microsoft Office Standard 2007 (full product) is $399.95 and Microsoft Office Professional 2007 (full product) is $499.95.

Microsoft Free Software's Effect on Competition

SQL Server's primary competitors are Oracle and IBM, both of which offer "Express" (i.e. "free") versions of their flagship relational database management systems (RDBMSs): Oracle Database XE and IBM DB2 9 Express C. Oracle Database XE doesn't run under Vista, but IBM DB2 9 Express C (finally) does. It's very likely (if not certain) that SSX is responsible for two more free RDBMSs entering the market.

SQL Server and SSX compete with "free and open source" databases, such as MySQL, PostgreSQL, and others. MySQL AB earns its revenue by selling MySQL Enterprise Service and Support for prices ranging from $595 to $4995 per year and running the MySQL Conference and Expo in conjunction with O'Reilly Conferences, which costs from $595 to $1,495 to attend. It's highly probable that SSX, Oracle Database XE and IBM DB2 9 Express C all contribute to lower revenues at MySQL AB, but quantifying the degree of damage to MySQL's business is difficult, if not impossible. emphasizes the "free and open source" nature of its RDBMS, but has a large number of registered commercial support providers who might suffer reduced income as a result of free commercial RDBMS competition.

The effect of SSX on MySQL AB and PostgreSQL support provider income is much less than either Oracle or IBM free RDBMSs because SSX doesn't run on any Unix flavor.

Free Visual Studio Express editions are integrated development environments (IDEs) that compete with the IBM-sponsored, Java-oriented, open-source Eclipse development platform and language IDE, free Oracle JDeveloper, and Sun Microsystem's "free standards-based tools. The only commercial competitor of note is Eclipse-based JBuilder 2007 from CodeGear (formerly Borland), which sells for $399 to $1,999; C#Builder 2006 ranges from $1,090 to $3,490. (The $399 JBuilder Developer edition corresponds approximately to VS Express versions.) The VS Express editions undoubtedly have reduced C# Builder revenue, but free or fully-licensed VS editions don't support Java.

You mention that Microsoft's Virtual PC 2007 competes with offerings from IBM, VMware and Sun, but HP also appears to be a virtualization player. VMware, which offers a free VMware Player, is a division of EMC. All of these firm are more interested in the server than the desktop virtualization market and have the wherewithal to compete successfully with Microsoft.

Update 2/26/2007: Mary Jo Foley's February 26, 2007 All About Microsoft post, "VMware attacks Microsoft virtualization strategy," included a link to a February 24, 2007 New York Times article, "A Software Maker Goes Up Against Microsoft" that describes VMware and its objects to Microsoft's licensing policies for virtualizing operating systems and virtual hard drive (VHD) images. The Times article included a chart of VMware's revenue—$709 million in 2006 and more than $1 billion (estimated) for 2007. The Times estimates VMware's virtualization application market share at 80%.

The Monday following appearance of the Times story, VMware made public a white paper, "Microsoft Virtualization Licensing and Distribution Terms," which details VMwares complaints about Microsoft's increasingly restrictive virtualization terms. Mary Jo cites in her later "Microsoft has a virtualization white paper, too" post a white paper and license calculator—"Licensing Microsoft Server Products with Microsoft Virtual Server 2006 and Other Virtual Machine technologies" (linked in the VM whitepaper) and "Windows Server Virtualization Calculator." These two articles appear to validate several VMware complaints.

Your "VMware Strikes Back" post on the same day includes links to the VMware white paper, but not to the NY Times story or Microsoft's white paper or license calculator documents.

Update 3/5/2007: ZDNet's George Ou weighs in with The facts on VMware vs. Microsoft.

Intuit has a monopolist's share of the retail (small business) accounting software market. According to NPD, Intuit controls 91.6% of the market, which is similar to Microsoft's share of the office productivity applications market. The probability of free Office Accounting Express significantly reducing Intuit's revenue from QuickBooks is close to zero. The free suite is a much more serious threat to the Office suite's contribution to Microsoft's bottom line, but so far it appears to me that isn't "good enough."

Predatory Pricing

A comment to your post by Philip Marcus, J.D., raises the issue of predatory pricing by Microsoft:

This technique of giving away software or charging les[s] than competitors is an old Microsoft trick to kill competition. Once the competitor is dead, MSFT can jack up the price for the now tethered user.

It is called "predatory pricing" and it is illegal, albeit hard to prove in court. The competitors generally do not have the money to take on MSFT in a lawsuit, and DOJ has already had their one shot at MSFT.

Added 2/26/2007: According to a November 25, 1998 Seattle Weekly article, "Be careful what you wish for: Why winning its antitrust battle might destroy Microsoft," by Mike Romano:

There is currently no statutory definition for predatory pricing. In 1993, however, the Supreme Court applied a standard test developed by professors Philip Areeda and Donald Turner in Brooke Group v. Brown & Williamson, a case involving cut-rate generic cigarettes. According to Areeda-Turner, competition in any industry will naturally drive prices toward marginal costs (i.e., the cost of material and labor in making the last widget, excluding the start-up cost of building a widget factory). Pricing below marginal cost serves no purpose except to drive out competitors, and is therefore predatory. Once competitors are driven out of business, a predatory monopolist can recoup its losses by charging supracompetitive prices, to the eventual detriment of consumers. ...

"I think Areeda-Turner is terrifically important," says New York University economics professor Lawrence J. White, who was chief economist for the Justice Department's antitrust division just before Rule took over and who remains a harsh critic of Microsoft. "But it does create a dilemma for something like software. I rarely agree with Rick, but [predatory pricing] is not a useful way of thinking about this."

This is most likely why the DOJ is not relying on predatory-pricing charges in US v. Microsoft; instead, it is trying a more nuanced case of illegal bundling—the tying of an inferior product to a monopoly product in order to drive out a competitor in the inferior product's market.

Clearly, the marginal cost of free software delivered by downloading a file over the Internet is close to nothing—just the miniscule bandwidth cost.

Unlike Mr. Marcus, I'm not an attorney but I haven't seen any evidence of "MSFT jack[ing] up the price for the now tethered user." Internet Explorer 7.0 remains a free download but continues to lose market share to equally free FireFox. As mentioned earlier, the price of Access has remained about $100 since it knocked former market leaders dBASE IV and Paradox out of the desktop database market.

Free Windows Media Player hasn't put Real Player or QuickTime out of business. I'm sure Apple has "the money to take on MSFT in a lawsuit." Apple seems to have no qualms about taking on Cisco Systems in a dispute over the iPhone trademark. Virtual machine marketers IBM, EMC, Sun and HP certainly are large enough to take on Microsoft, as demonstrated by earlier litigation with IBM and Sun.

Apple and Linux have begun to loosen Windows users' tethers, and the market share of OS X and Linux distributions is likely to continue to increase (albeit slowly) in the foreseeable future.

* Note: The Jet database infrastructure (consisting of msjet40.dll, msjetoledb40.dll, and vbajet32.dll) is included in the \Windows\System32 folder as part of the Windows XP and Vista operating systems.

Monday, February 19, 2007

SQL Server 2005 SP2 Released to Web

Microsoft announced on February 19, 2007 the availability of the Release-to-Web (RTW) versions of SQL Server 2005 Service Pack 2 for Enterprise, Standard, Workgroup, Developer, and Express editions. The What's New in Microsoft SQL Server 2005 SP2 page offers a detailed list of additions and improvements to the Database Engine, Analysis Services, Integration Services, Replication, Reporting Services, and shared tools. Links to SP2 upgrades for all SQL Server 2005 editions and replacements for earlier versions of SQL Server Express (SSX) with Advanced Services, (SSXAS), SQL Server Management Studio Express (SSMSX), and Microsoft SQL Server 2005 Express Edition Toolkit (SSXTK) are on the SQL Server 2005 Service Pack 2 page. There's also a link on the page to download the updated SQL Server Books Online SP2 version. Note: As of 11:00 am PST on 2/19/2007, the download links on the SSX Download Now! page that's linked from the above SQL Server 2005 Service Pack 2 page all refer to SP1. As of about 9:50 a.m. PST on 2/20/2007, the SP1 references changed to SP2. If you want to ensure that you're downloading the SP2 RTW build 9.00.3042.00 go to:

SSXAS SP2 adds full-text search, merge replication subscription and Reporting Services functions to bare-bones SSX, and includes SSMSX-SP2. Note: SSMSE is supported on x64 and EMT64 systems in Windows On Windows (WOW). SSMSE is not supported on IA64 systems. To determine the build before and after updating SQL server, execute SELECT @@version or SELECT serverproperty('ProductVersion'). Service Pack 2 Vista Support

The primary improvements to SSX are support for operation with normal user credentials under Windows Vista's User Access Control, SSMSX compatibility with Vista, and improvements to backup, restore, and Reporting Services.

Following is the Configuration Options dialog that enables normal Windows users to run SSX instances (enabled by default) and optionally adds the installing Vista user as a member of the instance's SysAdmin (sa) role that's not enabled by default:

SSMSX has been enhanced with support for SQL Server 2005 Compact Edition (SSCE) databases, Vista compatibility, and new options for generating scripts.

Tip for SSX SP2 CTP and SSXAS SP2 CTP Only: If you've installed the SSX SP2 Community Technical Preview (CTP), upgrading SSX to SP2 RTW version will be blocked. In this case, you must stop and remove the SSX SP2 CTP instance and, if you installed SQL Server Reporting Services, remove the ReportServer$...mdf and ...ldf files from or rename the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Don't forget to run the SQL Server Surface Area Configuration tool and SQL Server Configuration Manager to reconfigure the default instance to that of the instance it replaced. (You can upgrade SQL Server 2005 Developer Edition or higher CTP and SSMSX SP2 CTP with no problem.)

SQL Server 2005 User Provisioning Tool for Vista

It's unlikely that many fully-licensed SQL Server 2005 Standard Edition or higher instances will run under Windows Vista, but you have the option of launching the SQL Server 2005 User Provisioning Tool for Vista after SQL Server installation completes:

If you mark the Launch check box, clicking Finish displays the following SQL Server 2005 User Provisioning Tool, which lets you assign a normal user to the SysAdmin role of each instance on the server:

Updating the SQL Server Client Tools doesn't install the February 2007 downloadable update to SQL Server Books On Line. (Only the English edition has been updated.)

Saturday, February 17, 2007

"Friends of Jim" Halt Volunteer Search for Jim Gray

The Tenacious blog reports that the search for Jim Gray, the renown large-scale database and transaction-processing pioneer, by a team volunteers known as the Friends as Jim has ceased its activities as of February 16, 2006. The "Search Status" post by Mike Olson states:

In the last several days, the Friends of Jim group has reviewed all the data with Coast Guard officials. The fact is that we have no evidence as to what has happened to Tenacious or to Jim Gray. Neither we nor the Coast Guard can come up with a surface search plan that is likely to find either Tenacious or Jim, given everything that has been done already.

Accordingly, the Friends of Jim group is suspending its active effort to find Tenacious that has been centered here at the blog. For both the Coast Guard and the Friends of Jim, “suspension” means that the active search has been discontinued due to exhausting all present leads and the lack of new information. Of course, should we or the Coast Guard receive any new information, we will investigate it.

James Nicholas Gray 1944 - 2007? Rest In Peace

The "U.S. Coast Guard Searching for Jim Gray off San Francisco Coast" post on this blog includes a chronology of the search efforts with quotes from Coast Guard sources, video news segments, and print stories. There are links to additional articles about Jim and the search on the James N. Gray Wikipedia page. The Help Find Jim site has a photograph of Jim's sailboat, "Tenacious," and links to downloadable posters that volunteers hung at marinas and other Pacific Coast locations.

Microsoft Research's "Update on Search for Jim Gray" page expands on the Tenacious site's statement:

There is tremendous interest both inside and outside Microsoft in paying appropriate tribute to Jim. But his family believes it would be premature at this time to hold a memorial service, because it has been less than three weeks since he disappeared. We are respecting the family’s wishes.

This has been an extremely emotional time for all of us who consider Jim a colleague and a friend. The tremendous outpouring of support is a true testament to how many lives he has touched. We all miss him, and we continue to keep Jim and his family in our thoughts.

All who knew him or knew of his work will miss Jim greatly.

Update 5/2/2007: Mark Whitehorn has posted an eloquent "Tribute to Jim Gray" on the UK's The Register.

LINQ to SQL C# Primer

The seventh in Charlie Calvert's LINQ Farm series, "Connecting to a Database with LINQ to SQL," shows beginning LINQ programmers how to write a simple C# console application that uses LINQ to SQL for connecting to and querying the Northwind Customers table.

Charlie also posted a "Connecting to Northwind" article to aid users new to database applications in downloading and installing the Northwind sample database in an SQL Server or SQL Server Express instance. My "Tips for Using Northwind with Visual Studio Express" extends Charlie's article with a few suggested additions and modifications, especially for Vista users.

Following are links to all seven members of Charlie's LINQ Farm series:

  1. LINQ for Beginners
  2. Query Expressions
  3. Query Operators
  4. Using Distinct and Avoiding Lambdas
  5. Focus on Grouping
  6. LINQ Sets: Union, Intersect and Except
  7. Connecting to a Database with LINQ to SQL

I first covered LINQ Farm in an "Orcas December 2006 CTP Coming with New LINQ/EDM Bits" post of November 25, 2006. However, my "Orcas February 2007 CTP Delayed to March" post notes that "we LINQ and ADO.NET 3.0 early adopters need a new Il Duce to make the Orcas trains run on time."

VB 9 Extension Methods Documentation Series

Scott Wisniewski, a developer on the VB 9 Compiler Team has posted the first five parts of a series of articles on the new extension methods included in Visual Basic 9.0's "Orcas" release.

Series topics are:

Here's a part of Scott's description of extension methods:

[Extension methods] provide a very powerful and elegant way to inject your own custom functionality into other people's types. In particular, through a great deal of compiler magic, Extension methods enable you to create a method in a module decorated with an attribute and have that method appear as if it was an instance method defined on another type.

Extension Methods and LINQ

Extension methods play a major role in LINQ. According to Don Box and Anders Hejlsberg in "The LINQ Project" (September 2005):

Extension methods combine the flexibility of "duck typing" made popular in dynamic languages with the performance and compile-time validation of statically typed languages. With extension methods third parties may augment the public contract of a type with new methods while still allowing individual type authors to provide their own specialized implementation of those methods.

Extension methods are defined in static classes as static methods, but are marked with the [System.Runtime.CompilerServices.Extension] attribute in CLR metadata. Languages are encouraged to provide a direct syntax for extension methods. ...

Extension methods are resolved at compile-time based on which extension methods are in scope. When a namespace is imported with C#'s using statement or VB's Import statement, all extension methods that are defined by static classes from that namespace are brought into scope.

The standard query operators are defined as extension methods in the type System.Query.Sequence. When examining the standard query operators, you'll notice that all but one of them is defined in terms of the IEnumerable<T> interface (the exception is OfType, which is described later). This means that every IEnumerable<T>-compatible information source gets the standard query operators simply by adding the using System.Query statement in C# [or Imports System.Query in VB].

Most extension method examples are written in C#. A serious exposition of VB's forthcoming extension methods is most welcome.

As usual, I'll update this post as Scott adds parts to his series.

Technorati tags: , , ,

Friday, February 16, 2007

Tips for Using Northwind with Visual Studio Express

The C# team's Charlie Calvert posted "Connecting to Northwind" on February 16, 2007. The post contains instructions for enabling programmers new to Visual Studio 2005 Express editions to connect to the Northwind sample database. Note: Charlie's post is intended to assist readers of his "LINQ Farm: Connecting to a Database with LINQ to SQL" to create a local Northwind database SQL Server Express instance for demonstrating LINQ to SQL queries. The post is for beginners, so here are a few additions and clarifications that might aid new users:

1. Viewing Northwind in SQL Server Management Studio. "SQL Server Management Studio ships as part of SQL Server and SQL Server Express." Technically, SQL Server Management Studio Express (SSMSX) ships as a part of SQL Server 2005 Express Edition with Advanced Services SP1. Alternatively, you can download SSMSX separately or as a component of Microsoft SQL Server 2005 Express Edition Toolkit SP1.

2. Vista Problems. SQL Server Express (SSX) SP1 creates a login for members of the BUILTIN\Administrators and BUILTIN\Users groups. Administrators have SysAdmin (sa, or system administrator) permissions; Users have no permissions. Under Vista's User Account Control (UAC) feature, ordinary users can log in to SSX, but can't do anything else. The SQL Server Express Weblog's "Getting things working on Vista" post explains how to add a new login with SysAdmin privileges.

SSX SP2 will correct this problem; you can download the December 2006 CTP here. According to the post, Microsoft Update will automatically install SSX SP2 at RTM.

Tip: If you're running Vista, download SQL Server Management Studio Express Service Pack 2 - Community Technology Preview (CTP) December 2006.

3. Under Figure 3. Visual Studio Express editions are restricted to User Instances by default. A User Instance attaches an SQL Server database file (Northwind.mdf) as a database (Northwind) when you open a project that uses the database and detaches it when you close the project. This feature enables XCopy distribution of your project by releasing SSX's lock on the database file when it's not open in your project.

When you first connect to a database file, you are offered the option to create a copy of the file in the folder with your project's .exe file. Unless you have a good reason not to do this, create the copy in the default file location.

4. Under Figure 5. Another option is "If you are using a remote instance of SQL Server Express, type the name of the server followed by \SQLEXPRESS: MyServer\SQLEXPRESS. If the remote server is a named instance of SQL Server, substitute the instance name for SQLEXPRESS."

5. Connecting to a pre-attached database. If you want to connect a VS Express Edition project to the database you've attached to SSX with SSMSX instead of attaching and detaching the file as a User Instance, you must change the default connection string or write your own. Creating a starter User Instance connection string and then modifying it is the easiest approach.

Here's a typical User Instance connection string in the project's app.config file:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=C:\Path\Northwind.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

Open the project's app.config file from Solution Explorer in the VS XML editor and change the connection string to the following:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

Note: You can't open the database of the preceding instance in VS Express editions' Database Explorer. Use SSMSX SP1 or SP2 CTP instead.

6. Connecting to a remote SQL Server or SSX instance. By default VS Express editions can't connect to databases of remote SQL Server or SSX instances. However, you can modify the project's connection string to connect to the remote instance:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=SERVERNAME\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

You must use SSMSX SP1 or SP2 CTP instead of Server Explorer in this case, too.

Note: Mary Jo Foley says SQL Server 2005 SP2 RTM should be available for download on Monday, February 19, 2007. However, that's Presidents Day, so it might be delayed until Tuesday.

Update 2/19/2007: Mary Jo was right (as usual). See SQL Server 2005 SP2 Released to Web.

Monday, February 12, 2007

Microsoft Sync Services 1.0 Books Online CTP

Microsoft promised documentation for the Microsoft Synchronizaton Services CTP would be available in "early February" when the Occasionally Connected Services (OCS) team released the Microsoft Synchronization Services for ADO.NET v1.0 CTP (OCS-CTP) on January 22, 2007. Surprise! The OCS team posted the Microsoft Synchronization Services for ADO.NET Books Online Community Technology Preview (OCS-BOL) on February 12, 2007. The download site offers SYNCSVCSBOL-ENU.msi, the 16.7-MB installer for OCS-BOL and SyncServicesCtpSamples.exe, which generates a "Two-Tier Download-Only Sample" application. The sample app synchronizes the Adventure Works sample database's Products table with an SQL Server Compact Edition (SSCE) client database. You need the OCS-CTP, which also installs an updated SSCE v3.5 CTP, the Orcas successor to the SSCE v3.1 RTM bits, which Microsoft released on January 11, 2007. (The prerequisites are the same as those for Rafik Robeal's four OCS demo applications that I've previously described here and here, plus a current version of AdventureWorks.) The SyncComponents_Sample1 Project SyncComponents_Sample1.sln is a C# project that generates a new 276-kB SSCE 3.5 database from AdventureWorks' Customer, Employee, Product, ProductModel, ProductSubcategory, UnitMeasure, SalesOrderHeader, SalesOrderDetail, and Store tables. Synchronization is DownloadOnly, so changes only propagate from the server to the client. Here's an overview of the steps required to test the sample project:

  1. Prepare the server tables for synchronization. Execute the ServerDb_CreateSyncObjects.sql script to add InsertOriginatorID, UpdateOriginatorID and CreateDate tracking columns, a TableName_Tombstone table, and a trgDel_TableName delete trigger to each of the nine tables.
  2. Create the SSCE database and populate its tables. Build and run the project, and then click the Synchronize button. The DataGridView displays changes, not rows added for initial table population.
  3. Add insert, update and delete stored procedures. Execute the ServerDb_DMLSyncObjects.sql script to create uspProductInsertCommand, uspProductUpdateCommand, and uspProductDeleteCommand stored procedures.
  4. Make changes to the server data. Execute the stored procedures a few times to generate changes to the Product table.
  5. Synchronize the client. Click Synchronize to pull and display the server changes.

The following screen capture reflects inserting 21 rows with the uspProductInsertCommand.

This sample project won't win any awards for comprehensive coverage of Sync Services code. Unlike Rafik's demos, it doesn't support Bidirectional synchronization. On the whole, Rafik's projects provide a much better overview of Sync Services programming techniques. Tip: If you receive an unhandled exception that starts with "Table '[ProductSubCategory]' could not be created," you have an old AdventureWorks version in which the the ProductSubCategoryID had the smallint data type. The GetSchema method attempts to add it to the SSCE ProductSubCategory table as a smallint identity column, which SSCE doesn't suport. Update your AdventureWorks database from the link provided. You must delete the AWClientDB.sdf file in the \Program Files\Sync Services\Samples\SyncComponents_Sample1\SyncComponents_Sample1.Client\bin\Debug folder before you can successfully populate the client database. Technorati tags: , , , , , , , , .

Wednesday, February 07, 2007

Conflict Resolution with Sync Services

Rafik Robeal has posted the fourth Microsoft Synchroniztion Services (OCS) demo project, Offline Application—Conflict Handling, on his The Synchronizer blog. This C# project demonstrates handling Sync Services' ApplyChangesFailed event to determine the failure location (server or client) and type of concurrency conflict, and then specifying an action to ignore or resolve the conflict. Rafik's post, which augments three preceding Sync Services demo projects, describes the ApplyChangesFailedEventArgs.Conflict.ConflictType enumeration:

  • ClientInsertServerInsert also known as PK Collision; occurs when both client and server insert a row with same PK
  • ClientUpdateServerUpdate the most common conflict; occurs when client and server make changes to the same row independently
  • ClientUpdateServerDelete occurs when the client updates a row and the server independently deletes the same row
  • ClientDeleteServerUpdate occurs when the client deletes a row and the server independently updates the same row

The SyncForm has four added buttons to generate random conflicts on the client or server:

Actions available to attempt to resolve concurrency conflicts are:
  • ApplyAction.RetryApplyingRow without changing to the datarow the retry will fail causing the event to be raised again. The demo does not allow you to edit the datarow though.
  • ApplyAction.RetryWithForceWrite has support built in the ClientSyncProvider. On the server side, you need to add support in your sync adapter commands; the demo shows an example for update-update conflict.
  • ApplyAction.Continue ignores the conflict
  • Abort the sync operations by throwing an exception; all changes will be rolled back and re-synced in the next sync session.
The ConflictForm's four action buttons are enabled as appropriate for the conflict type and location:

After taking the appropriate action, the ProgressForm opens: Rafik explains that the synchronization framework defines the following two commands to find the rows in conflict:
  • SyncAdapter.SelectConflictUpdatedRowsCommand this command finds conflicting rows in the base table. The sync runtime executes this command if insert, update or delete command failed (i.e. returned 0 row count)
  • SyncAdapter.SelectConflictDeletedRowsCommand this command finds conflicting rows in the tombstone table. The sync runtime executes this command if the conflicting row was not found in the base table. This is how ClientUpdateServerDelete conflict is detected.

The two commands are implemented for the two tables—orders and order_details—by four T-SQL stored procedures on the SQL Server 2005 [Express] server.

Note: Be sure to run the demo.sql script (to the -- Test Sample comment only) to create (or recreate) the pub database and the four server-side tables (orders, order_details, orders_tombstone, and order_details_tombstone) on an SQL Server 2005 [Express] instance, then run the server_procs.sql to generate the stored procedures, and finally build and run OfflineAppDemo-Conflicts.sln. Note that the tombstone tables have a new schema to support multiple deletions of records with the same primary key.

Technorati tags: , , , , , , , , .