Data Views and the AdventureWorks Database

If you ever tried to get Data Views to work with the AdventureWorks DB you probably have had as much pain and frustration as I have had in the past.
First you'll need to get SSO set up to connect to the DB. That's simple once you know how!
Then you select the table you want and when you try to show the data you keep on getting error messages! The problem here lies in the fact that AdventureWorks uses Schemas everywhere. And you need to fully qualify your tables to be able to access them. SarePoint Designer is not clever enough and forgets to put the schema name infront of the select command.

Here are two possible solutions:

1. simply write the select statement yourself instead of picking the table from the list. A simple statement like Select * From Production.Product will get you going in no time.
2. Create a deidacted login for a specific schema and define the defauls schema.

Here are the steps for the latter option:

1. Create a login on the SQL Server called AWProduction.
2. Set its default database to AdventureWorks.
3. In its user mappings give it pubilc access to AdventureWorks and specify the Default Schema to be Production.
4. go to the schema definition on the AW database and give AWProduciton all the required permissions on the Production Schema.
5. Set up a new SSO Application that uses the new AWProduction Login
6. Use that SSO login for your Data Connection

Hope that helps!