Following some of the steps in this office article I successfully managed to use Sharepoint Designer 2010 to create a data source to the Adventureworks db, selecting the products table. However when i try to insert the datasource in the web part page i get an error - "Unable to retrieve schema. Ensure the ConnectionString and SelectCommand properties are valid. Invalid object name 'Product'."
I finally got it to work using the Custom select query option and using- select * from [AdventureWorks].[Production].[Product]
My question is why doesn't SP Designer use the correct schema in the first place?