SQL Query in SharePoint 2010

Question

I made a query (SQL) where I can put the extended properties (right click on table, go to properties, then to extended properties and enter metadata under name/value) of a table in a separate table.

I want SharePoint 2010 to be able to run that query and display the metadata (results) in SharePoint. Do I need a custom web part for that? How is this done? I'm new to SharePoint 2010 and SQL.

Thanks!

p.s. I already know about BDC/BCS, etc. I'm wondering how SharePoint can actually run the query and show me the results (final table).

In other words, how can I have SharePoint run a SQL Query and give me the data I want? Thanks!

For clarification, I can do all this stuff manually on SQL Server, but I want SharePoint to do it.

Answer 1

Regardless whether in SharePoint or not , to run the query in a application you need to connect to your external database server using some drivers and execute a command through the connection and you will get your results back.

For your task,it can break down to 2 sub-tasks.

1.Connect to external database ,execute query and return query results.

2.Display results in SharePoint.

If you like to grab data from external data source like external databases in SharePoint , you should look into Business Data Connectivity(BCS).You can find more info here for bcs http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

Answer 2

How you do this depends entirely on when you want the change to the database to occur, and why it has to be done.

Do you want an event in SharePoint (ie: adding/changing a list item) to trigger it? Or a business event (workflow)?

If it is purely a maintenance task that needs to be done now and then by some kind of administrator, then I'd create an Application Page (that would be security trimmed) which would have a form that lets users input what's needed, and a button which has code behind that updates the database.

This is the most secure method - exposing a database's schema with a generic web part that lets you dabble is VERY insecure, and as such I doubt you will find such a web part in the wild. If you do - don't deploy it without getting an experienced expert evaluate how the web part operates and what security is in place.

Answer 3

You can simply connect any native SharePoint list with external data (e.g. SQL query as shown here:

You need to have the "Business Data List Connector" shareware installed for this.

Hope that helps.