Yesterday I was writing a demo on the use of Data View web parts from within the SharePoint Designer for our Development courses. I tried to explain how to display a Data View connecting to a SQL Server 2005 database. Piece of cake I thought, but that was far from true.

Firs t I tried to configure my connection into the Data Connection Library task pane: I filled out server name and selected the SQL Server .NET Provider. As I don’t have Single Sign-On configured on my MOSS installation, I opted for filling out username and password. In the next step I got a list of databases and tables to select from so I chose to work with the AdventureWorks database and its ProductCategory table. Next I defined a Filter and Sort order and saved my connection. But when I clicked Show Data to have my Data View filled with data, I got the following error:

This is very weird: I was able to get metadata from the database because I was able to select a table, fields, define a filter and a sort order but I’m not able to view the data. I suspected that there was a problem in the communication between SharePoint Designer and SQL Server 2005.

So I opened SQL Server Profiler to trace the calls done by SharePoint Designer to the AdventureWorks database. When executing the Show Data action again, I found out in the SQL Server Profiler that SharePoint Designer tries to execute following SQL statement:

SELECT [ProductCategoryID], [Name] FROM [ProductCategory]

Execution of this statement in SQL Server 2005 Management Studio taught me that it fails because the AdventureWorks database works with schemas. Schemas in SQL Server 2005 are what namespaces are in .NET: it is a logical structure, a way of grouping tables. The AdventureWorks database has several schemas defined and one of the schemas is Production, under which the ProductionCategory resorts. Following statement works fine:

SELECT p.[ProductCategoryID], p.[Name] FROM [Production].[ProductCategory] p

So, the conclusion is: SharePoint Designer ignores database schemas.

There are 2 solutions to this problem:

• In the connection configuration wizard select a database but don’t select a table to work with. Instead choose the option to specify a custom SQL statement.
• Even better is to work with stored procedures. Follow the same steps as for a custom SQL statement but instead of choosing the option SQL Statement, choose Stored Procedure and select your stored procedure from the list.

Anyway, if you need a query that joins 2 or more tables, you have no other option than to use a custom SQL query or a stored procedure.

A small side node: SharePoint Designer seems to cache certain things because when executing Show Data multiple times, there was nothing to see in the SQL Server Profiler, meaning that no request were sent to the database.

I spent a lot of time looking at this problem and trying to understand why SharePoint Designer didn’t want to retrieve the data from the AdventureWorks database. I hope this post saves you from loosing time.