Fun and Games with the Data Source Configuration Wizard

Sigh. I’ve been working on the data chapters for the book I’m writing about Visual Studio 2005. I decided to use the Adventure Works sample database because M$ recommends it with SQL Server 2005. Unfortunately, the database makes extensive use of schemas, and the Data Source Configuration Wizard in ASP.NET doesn’t understand schemas. (Works like a champ in Windows apps!) Sure, you can make a connection and see database objects. But the resulting SQL doesn’t fully qualify the table’s name. You can even browse the data all day long in Server Explorer, but when it comes time to run the query, you get an invalid object error.

There are a few workarounds:

  • Manually edit the commands generated by the wizard
  • Use the Query Builder
  • Run the synonym script that creates synonyms for all the table objects

I decided to manually edit the commands. That allows me to walk the user completely through wizard. Eventually, the wizard will be able to handle schemas, and this will be a non-issue.

One other thing, the table object name is case-sensitive. To access the Department table, your FROM clause must read HumanResources.Department or [HumanResources].[Department].