1

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?

Improve this question

1 Answer 1

Reset to default
1

It seems there is some bad communication going between SPD and sql server. And the workaround is to use a custom query preferably store procs which doesn't work sometime because i think SPD does some caching.

Behind the scenes of a "Refresh Schema", the DataSource issues an ADO.NET "FillSchema" call. This, in turn, calls SQL Server with a "SET FMTONLY ON; EXEC(your sql)" which returns the metadata on the result set(s).

This can/is problematic with dynamic sql

This behavior is by design. When FMTONLY is enabled, we return metadata of all possible result sets in the batch or stored procedure, ignoring the logical condition of IF statements. It is primarily used by client drivers to discover parameter and result set metadata.

So apparently, if your Dynamic SQL is especially interesting, it may not be possible for FMTONLY to determine what your resultset should be, which also means that Refresh Schema may not work.

I hope this kinda answers your question!

Improve this answer
3
  • I have not yet tried this with any other database with several schemas like AdvWorks but should i take it this behavior is always expected from SP designer using a Data source? Does this also mean that using External Content Type would be a better option? I am just reading up on ECT and will experiment with it next. Though i am still trying to understand when to choose Data Source and when to go for ECT.
    – Ron
    Commented Aug 15, 2012 at 13:41
  • tbh I dont know why it behaves like this and it shouldnt realy! also im not that much a great fan of spd atm lol. I would hope they can improve on the next version. Its a hit or miss behavior that "shouldnt" be expected but its a hit or miss with data source. using external contenttypes does have it benifits msdn.microsoft.com/en-us/library/ee556391.aspx creating external contenttypes msdn.microsoft.com/en-us/library/ee557243.aspx
    – Ali Jafer
    Commented Aug 15, 2012 at 14:41
  • this link also explains the two and should hint to when to use them social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/…
    – Ali Jafer
    Commented Aug 15, 2012 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.