OakLeaf Systems

My "compulsive obsession" to bring SQL Server 2005 stored procedures to the user-experience equivalent of dynamic T-SQL queries with a GridView bound to a LinqDataSource—regardless of the development effort required—continues.

When hydrating LINQ to SQL entities, I finally got server-side (incremental) paging to work properly with a LinqDataSource, bound DataGrid view and a stored procedure derived from a sample query. (See the introduction and "Attempts to Fix the Server-Side Paging Problems" sections of Problems Using Stored Procedures for LINQ to SQL Data Retrieval from 9/8/2007, updated 9/17/2007.) Here's a screen capture of the Orders test harness displaying 10 of the 122 Northwind Orders from customers in the USA:

Click capture to display full-size image in another window.

It was obvious that ad-hoc sorting wasn't behaving as expected for properties other than OrderID. For example, the preceding screen capture shows a descending sort on the Customer.CompanyName property. The sort's scope is the 10 rows displayed; the GridView sorts the rows. The same is true for ad-hoc sorts on all other properties except OrderID.

A quick review of a typical dynamic T-SQL query for page 2 with the simplest case, an ascending sort on the Freight property, showed the syntax required to increase the scope of the sort to all entities in the set:

exec sp_executesql N'SELECT TOP 10 [t2].[OrderID], [t2].[CustomerID],
[t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate],
[t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName],
[t2].[ShipAddress], [t2].[ShipCity], [t2].[ShipRegion],
[t2].[ShipPostalCode], [t2].[ShipCountry]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Freight], [t0].[OrderID]
DESC) AS [ROW_NUMBER], [t0].[OrderID], [t0].[CustomerID],
[t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],
[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
[t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1]
ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t1].[Country] = @p0
) AS [t2]
WHERE [t2].[ROW_NUMBER] > @p1
ORDER BY [t2].[Freight], [t2].[OrderID] DESC',N'@p0 nvarchar(3),
@p1 int',@p0=N'USA',@p1=10

The red ORDER BY expression for the ROW_NUMBER() function shows the syntax for creating the rowset from which page 2 (or greater) is obtained. Notice that both Freight and OrderID fields are included in the sort expression. The green expression is a holdover from the page 1 code and isn't required by the preceding example.

It's a better approach to use an explicit query than run sp_executesql to conditionally execute string-based queries from within the stored proc because of possible problems with caching the query execution plan and the brittleness of unchecked query syntax.

Garth Wells' demonstrated how to write CAST expressions to customize ORDER BY expressions in his 2001 Dynamic ORDER BY article; a fix by "Wildthing" in the comments thread accommodates different datatypes. Here's the stored proc code to handle various single-parameter sorts:

CREATE PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSorted(
@Country nvarchar(15) = 'USA',
@MaximumRows int = 20,
@StartRowIndex int = 0,
@SortCol nvarchar(128) = 'OrderID',
@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
t2.OrderDate, t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
t2.ShipPostalCode, t2.ShipCountry
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE -- Numeric types ascending
WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
THEN t0.Freight
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
THEN t0.EmployeeID
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
THEN t0.ShipVia
END ASC,
CASE -- Numeric types descending
WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
THEN t0.Freight
END DESC,
CASE -- Character types ascending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
THEN t0.CustomerID
END ASC,
CASE -- Character types descending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
THEN t0.CustomerID
END DESC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
THEN t0.ShippedDate
END ASC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
THEN t0.ShippedDate
END DESC
) AS ROW_NUMBER, t0.OrderID,
t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
FROM dbo.Orders AS t0
LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
WHERE Country = @Country
) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's a screen capture of a later version of the test harness that sorts all columns with the preceding stored procedure. The ORDER BY clause is CustomerID DESC, OrderID DESC with many instances of ASC, ASC, ASC, ... DESC, DESC, DESC, which the query parser appears to ignore.

Click capture to display full-size image in another window.

The basic CAST approach gives the same sort sequence for CustomerID vs. Customer.CompanyName, but different sequences for EmployeeID vs. Employee.LastName and ShipVia vs. Shipper.CompanyName. Here's the modified stored proc code (red) to sort the entire data set on EntityRef values:

ALTER PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSortedExt(
@Country nvarchar(15) = 'USA',
@MaximumRows int = 20,
@StartRowIndex int = 0,
@SortCol nvarchar(128) = 'OrderID',
@SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
t2.OrderDate, t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
t2.ShipPostalCode, t2.ShipCountry
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE -- Numeric types ascending
WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
THEN t0.Freight
END ASC,
CASE -- Numeric types descending
WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
THEN t0.OrderID
WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
THEN t0.Freight
END DESC,
CASE -- Character types ascending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
THEN Customer.CompanyName
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
THEN Employee.LastName
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
THEN Shipper.CompanyName
END ASC,
CASE -- Character types descending
WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
THEN t0.CustomerID
WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
THEN t0.ShipName
WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
THEN Customer.CompanyName
WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'DESC'
THEN Employee.LastName
WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'DESC'
THEN Shipper.CompanyName
END DESC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
THEN t0.ShippedDate
END ASC,
CASE -- Date types ascending
WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
THEN t0.OrderDate
WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
THEN t0.RequiredDate
WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
THEN t0.ShippedDate
END DESC
) AS ROW_NUMBER, t0.OrderID,
t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
FROM dbo.Orders AS t0
LEFT OUTER JOIN dbo.Customers AS Customer
ON Customer.CustomerID = t0.CustomerID
LEFT OUTER JOIN dbo.Employees AS Employee
ON Employee.EmployeeID = t0.EmployeeID
LEFT OUTER JOIN dbo.Shippers AS Shipper
ON Shipper.ShipperID = t0.ShipVia
WHERE Customer.Country = @Country
) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's the test harness with the Employee column sorted ascending and an Ext. check box to select the extended sort capability:

Click capture to display full-size image in another window.

The preceding code doesn't attempt to sort multiple columns. It might be tempting to write a stored proc for two or three successive sort sequences with EXEC or sp_executesql and dynamic T-SQL. However, LINQ to SQL won't codegen function calls for stored procs that execute dynamic SQL. Also considering the security and query plan issues, it probably makes more sense to pass two or three pairs of @SortCol/@SortDir values and stack a set of CASE statements for each.

Update 9/19/2007: Minor edits and typos corrected .

Posted by Roger Jennings (--rj) at 10:58 AM 5 comments  

Labels: LINQ, LINQ to SQL, LinqDataSource, SQL Server 2005, SQL Server 2005 Express, SQL Server 2005 SP1, SQL Server 2005 SP2, TOP (100) PERCENT, VB 9.0

My September 11, 2006 SQL Server 2005 Ordered View and Inline Function Problems post described a change of behavior between SQL Server 2000 and 2005 when displaying views created with a SELECT TOP 100 PERCENT ... ORDER BY Whatever query: SQL Server 2000 sorts the resultset and SQL Server 2005 [Express] doesn't.

The post received many comments, including several that objected to my claiming this behavior was an issue for SQL Server users because the behavior was by design. Unfortunately, comments posted before the change of the OakLeaf blog format were lost in the transition to the wider page.

Yesterday Microsoft issued a hotfix, FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2005, the result is still returned in random order, which is only available from Microsoft Support, describes the following Symptoms:

You have a view in a database in SQL Server 2005. In the definition of the view, the SELECT statement meets the following requirements:

• The SELECT statement uses the TOP (100) PERCENT expression.
• The SELECT statement uses the ORDER BY clause.

When you query through the view, the result is returned in random order.
However, this behavior is different in Microsoft SQL Server 2000. In SQL Server 2000, the result is returned in the order that is specified in the ORDER BY clause.

The hotfix involves modifications to 11 files, including Sqlservr.exe and the workaround described in my post is simple, so Microsoft must have received many complaints about the problem.

Thanks to IDisposable (Marc Brooks) for the heads-up on the hotfix.

Technorati tags: Databases, SQL Server 2005, TOP (100) PERCENT, ORDER BY, SQL Server, SQLServer, MSSQL, SQL Server 2005 Express, SQL Server Express, SQL Express, da Vinci Toolset, Visual Data Tools, SQL Server Management Studio, Access 2007, Microsoft Access, Access Data Projects

Posted by Roger Jennings (--rj) at 11:04 AM 3 comments  

Labels: Access 2007, Access Data Projects, ADO.NET, ADO.NET 3.0, da Vinci Toolset, Databases, SQL Server, SQL Server 2005, SQL Server 2005 Express, SQL Server 2005 Express SP2, SQL Server 2005 SP1, SQL Server 2005 SP2, Visual Data Tools

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:

• Microsoft SQL Server 2005 Express Edition Service Pack 2 (SSX SP2, file date 2/18/2007, 55.4 MB for 32/64-bit version or 36.5 MB for 32-bit only version)
• Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2 (SSXAS SP2, file date 2/18/2007, 256.8 MB)

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:

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.)

Posted by Roger Jennings (--rj) at 12:12 PM 0 comments  

Labels: Management Studio Express SP2, SQL Server 2005 Express SP2, SQL Server 2005 SP1, SQL Server 2005 SP2

Fawcette Technical Publications' .NETInsight newsletters for June 12 and 13, 2006 brought readers Tech•Ed 2006 Special Issues. Here are links to the newsletters together with their article titles: Day 1—June 12, 2006

• New MS Forefront Tools to Improve Security by Peter Varhol
• New VSTS Tool Makes Database Dev Easier by Jeff Levinson
• Get Ready for WSS v3 and MOSS 2007 by Roger Jennings
• Architecture Takes Center Stage by Patrick Meader
• Prepare for WPF Properly by Billy Hollis
• Taking Stock of SQL Server by Roger Jennings
• Delve Into LINQ and Visual Basic by Roger Jennings

Day 2—June 13, 2006

• Realize New Functionality in .NET 3.0 by Rockford Lhotka
• New Technology Offers UI Improvements by Billy Hollis
• SQL Server Everywhere: A New Lightweight Database by Roger Jennings
• New Features in SQL Server 2005 SP-1 by Roger Jennings
• Latest Tools for Debugging, Refactoring by VSM editors
• Validate Business Objects Declaratively by Steve Michelotti

Posted by Roger Jennings (--rj) at 12:09 PM 0 comments  

Labels: Databases, DLinq, LINQ, LINQ to SQL, MOSS 2007, SQL Server 2005, SQL Server 2005 SP1, SQLX, Tech Ed 2006, WSS 3.0