1

Can I connect a result of SQL query to external database in SharePoint list?

As I understand - External Content types could map only one table for list, but I need to get some combined data from few tables, not single one. So I am trying to get result with "Content Source" in SPD, it allows queries as I understand.

By example I have external database with tables 'CountryRegion' and 'SomeTable', screenshots are below:

enter image description here

enter image description here

I make some logic in query and get result, by example

SELECT *
 FROM AdventureWorks2008.Person.CountryRegion R
 INNER JOIN
AdventureWorks2008.dbo.SomeTable T
 ON R.CountryRegionCode = T.CountryRegionCode 

I get this result:

enter image description here

Now I created "Content Source" in SharePoint Designer, it allowed me to make this select query:

enter image description here

But how can I map this result to SharePoint list?

3

I would also like to add that you can also accomplish this with SQL Table Views. So if you create a custom view within your SQL DB, you can connect to it in BCS using SP Designer as if it was a regular table. My understanding is that if the View is update-able as defined here, you will also be able to add Update controls to it. To add a view as an ECT please follow the steps here: http://prairiedeveloper.com/?p=395 However, instead of adding your operations to the table, you can now add the operations to the view instead (Step 3 in Map Parameters).

Just another suggestion to accomplish this.

1

You can use the stored procedures with BCS, so instead of a single table, you can gather information (Read only) from a result of a query. You need to create the stored procedure in SQL first, then connect to BCS through SHarePoint Designer, then you will be able to create a External Content Type based on that stored procedure.

Here is an article that explains it a bit better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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