by Dave Garbacz
20. September 2010 18:47
After some more dabbling in Sharepoint 2007 I started to look at what sort of data integration I could do using Sharepoint and using custom web parts. Enter the DataView Web Part control.
DataView is only available through the Sharepoint Designer (which is now offered for free from Microsoft). It allows you to set up numerous data connections through the Data Source Library panel. Sharepoint can connect to database sources, Sharepoint lists and libraries, XML files, scripts, and even create linked sources between data sets that can allow for merged data views.
For our purposes the database connections was the most pertinent but there was a problem that I couldn't seem to fix for a good couple hours until a forum post online helped me figure it out. It seemed like no matter how much I toyed around in SQL Server Management Studio with user permissions and making sure that the ASP.net temprorary space for Sharepoint wasn't stale and IIS resets I still got this error.
Until finally after poking around forums and such someone noted that Sharepoint Designer, when building these queries, doesn't attach a schema name to the table. The query runs but doesn't include a schema, if any, in front of the table that's being queried. I was using sample data from Microsoft's site, the Adventure Works package, and noticed there was a schema of [SalesLT] in front of the table name. Went back to the custom query that it built and added that as a custom string, and voila!
Now the fields could be picked and bound to the web part control in whatever manner desired. I'm not really sure if this issue has been fixed in Sharepoint 2010 but it seems like Microsoft could have caught this and done something about it sooner. It could just be that the sample data I'm using is in the Sharepoint database itself and is forced to use a schema. A database without a schema would be fine and usually is the case, but this little oddity could have saved me 3-4 hours wanting to hit my head against the desk.