Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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

share|improve this answer
    
Should be noted that the prime purpose of BCS in SharePoint is the basic CRUD (create, read, update delete) operations. Maintenance of the schema is NOT recommended through the BCS infrastructure. –  James Love Feb 15 '11 at 23:05
add comment

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.

share|improve this answer
add comment

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

enter image description here

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

Hope that helps.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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