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!

Using Dataviews to edit and update SQL data

If you hooked up your dataviews with SQL data and enable editing you most probably have come across following error message:
The data source control failed to execute the update command

The reason for this can be manyfold, either you might have a data type issue where you have non numerical data being pushed into a numerical field or in my case as simple as a database constraint getting in your way.
Dealing with the select, update, insert and delete commands manually can be a bit of a pain. So I really like the ability to pick my table and let SharePoint Designer worry about the rest. Just a shame that SharePoint designer does not understand one simple rule. You can not update a column which is the identity column of a table. SQL server reserves the right to auto increment that column. How to fix it? two ways:

The simple way:
Start with a blank Data View and make sure not to add the ID column to the list.

What if you want to show the id though?
The hard way:
same as above but you then you add the ID not by using the Edit Columns... task but simply by adding a new column to the dataview itself (click into first column of the DataView, right click and choose Insert...Column to the left) and injecting the ID manually. Easiset way to do that is to copy one of the other fields and then changing its databinding to point to the ID field.