You might have noticed most of my examples that query a database go against Northwind. Northwind was an older database sample that came with SqlServer 2000, but you can still download and attach it for Sql Server 2005 (I use Express here at home and it works fine).

The reason I haven’t been using AdventureWorks, which comes with 2005, is that it uses a new feature called Schemas. That’s the “Person” part of “Person.Address” and the like. Unfortunately, Visual Studio 2005 features such as the Configure Data Source wizard do not understand schemas, so any query you try, you end up having to hand-modify to include the schema. If you don’t, you get a fun “invalid object name” error.

This is a known bug in Visual Studio and they are NOT going to fix it for this release.

So, rather than have to modify every query I write or construct views and tables for the default schema, I simply chose Northwind for my examples. I’m writing example code, not trying to be a DBA, so mucking with the database isn’t my idea of a good time.

However, I finally got around to researching this issue (read: pissed off at the damn thing), and I found that you can remove the schema and assign the tables and views back to the default, dbo. When you do that, Studio will be able to talk to it again.

You can either change it manually using the Properties, or use SQL statements such as

alter schema dbo transfer HumanResources.Department

If you run into tables that won’t let you modify the schema, such as the Person.CountryRegion, and instead tell you that you can’t transfer a schema bound object, go find the view vStateProvinceCountryRegion, click Design, and in the properties, change the option where it says “bind to schema” to NO and let it drop the index on the view. Repeat for any others that give you a hard time.

I went through every table and view in the database and got it back to being owned by dbo, and now my SqlDataSource and other controls can query/preview the data and I don’t hit my head against the wall repeatedly.

Remember, I’m going through all this because it’s A TEST DATABASE and I want my test queries to run smoothly. Do NOT do this to a production database!

Once you’ve assigned them all to dbo (took me about 30 minutes to get it all), you can use AdventureWorks for simple test queries just like Northwind.

Did this post help you? Please share it on Digg, DZone, or your favorite social networking site!