Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, April 6, 2010 4:02 PM
Hey guys,
I was wondering if there was any easy way to call a stored procedure from SQL and have it list in a table (like in Excel) in sharepoint 2010?
I've gotten it to work in excel and I was just going to upload the excel form to the sharepoint, but you can't use certain things in excel services(excel web-mode).
All replies (15)
Tuesday, April 6, 2010 6:24 PM | 1 vote
Have you considered creating a view of the data and then creating an external content type using BCS? Here is a good walk through that may offer some thoughts.
Shannon Bray - MCT, MCPD, MCTS, MCITP Blog http://www.shannonbray.com User Group Site: http://www.cospug.com
Thursday, April 8, 2010 12:28 PM
That looks like exactly what I need, but whenever I go to add an external content type in sharepoint designer I get an error saying that "Some checks cannot be completed". The details mention that the server was unable to process the request due to an internal error.
I googled it and I'm looking around now but I haven't found anything yet.
Exact error -
Some checks cannot be completed.
"The server was unable to process the request due to an internal error. For more information about the error, turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs."
I'd like to mention that I have no control of the server itself.
Thursday, April 8, 2010 4:49 PM
What kind of object are you working with: table, view, etc? I am curious to see if I can replicate it.Shannon Bray - MCT, MCPD, MCTS, MCITP Blog http://shannonbray.wordpress.com User Group Site: http://www.cospug.com
Thursday, April 8, 2010 5:33 PM
Well I'm trying to connect a SQL server to the sharepoint one, so I can run a stored procedure database on the sharepoint. So I follow the URL's instructions and open up sharepoint designer, and click external content types. When I do it gives me a warning about "The business data connectivity metadata store is currently unavailable". So then I continue to follow the instructions and click on "external content type" in the ribbon menu. When I click it, it gives me that error that I described earlier.
Once I get the server admins to enable that IncludeExceptionDetailInFaults thing, I'll give the error description.
Thursday, April 8, 2010 7:39 PM
So I got access into central admin and the server manager. How do I edit the app.config in order to change that command (IncludeExceptionDetailInFaults) to true?
Monday, April 12, 2010 7:44 PM
So I fixed my previous predicament and now I'm in another one where every web part I try to use with an external content type to the sql server gives me the common "Unable to display this Web Part. To Troubleshoot...etc"
I've tried to link a stored procedure this way, as well as a table.
I don't know if it's because I might lack access/permissions to the SQL server or what. I was wondering if there was another way to display the table data without using a web part?
Tuesday, April 13, 2010 12:04 AM | 1 vote
There can be any number of reasons why you are getting that error. Did you check the application logs? You can also turn of verbose error messages with the web.config.
If you have never turned them on, here is a blog post that walks you through it.
http://www.shannonbray.com/2008/07/verbose-errors-in-sharepoint.html
Shannon Bray - MCT, MCPD, MCTS, MCITP Blog http://shannonbray.wordpress.com User Group Site: http://www.cospug.com
Tuesday, April 13, 2010 2:30 AM
Koopah, I don't think you're supposed to run stored procedures directly against the SharePoint internal databases. Why do you need to do this? There are many ways to get data out of sharePoint using the tools at hand without directly accessing it with sprocs. Can you let us know exactly what you need to accomplish?
Unable to display this web part is due to permissions. You have to make sure your user has rights to the ECT inside the BDC service application within Central Admin > App Mgmt > Manage Service Applications. From here, edit the permissions of your ECT that was created it in SPD2010. Also, be sure that after creating the ECT, you have to then do something with it, such as create an External List. What type of web part are you trying to use?
SharePoint Architect || Microsoft MVP || My Blog
Tuesday, April 13, 2010 1:11 PM
Well I was asked to find out how to query stored procedures in SQL from directly on the sharepoint site. An example being, a user goes to the website and sees a table with a bunch of data in it or whatever, changes one number in the table(say in "customerID") and it will automatically update/populate the rest of the information from the updated number being sent to the SQL database.
I've tried making an excel sheet, which works but the user needs to open it in excel in order to view/edit it because excel services can't use VBA's/external data. I've tried multiple "SQL Viewer" web parts from various places as well as making my own in visual studio 2008, and they've all given me misc. errors or would not load correctly. I've also tried the built in data view web part for Sharepoint 2010, which is pretty much where I am at now.
Once again I have no real prior knowledge of SQL or Sharepoint, they just sort of put me in front of a server and said figure this out.
*edit* - In the virtualmachine folder for my sharepoint there are two numbered folders, 80 and 15780. I assume that 80 is the one I want to use right because that's the html port.
Tuesday, April 13, 2010 1:49 PM
And it's for that very reason that you should just lay out your requirements and let us guide you to the answer. Doing SQL sprocs agains tthe SharePoint database is unsupported by MSFT, and it ignores all the available cool ways to get and display data that is built-in to the product. SharePoint 2010 gives us an immense amount of capability to retrieve, manipulate, and display data.
Could you walk through an entire scenario of what you want to do by applying it to a use case? My current opinion is that you should be using the Data Source capabilities, which you access within SharePoint Designer 2010. SharePoint lists and libraries can be queried dynamically using REST services that are built-in to the product. This is a far better option than performing direct SQL queries, imo.
SharePoint Architect || Microsoft MVP || My Blog
Tuesday, April 13, 2010 2:03 PM
Alright I'll do my best to explain everything. Right now I'm on a single machine which is set up on Windows Server 2008 RC2 and has three virtual machines on it using Hyper-V manager.
1) SQL 2008 server set up on Windows Server 2008 RC2
2) Sharepoint 2010 server set up on Windows Server 2008 RC2
3) Windows 7 User machine for regular use
I have no idea what you mean by a use case, but I want to be able to access information that we already have stored inside of our SQL server but on Sharepoint. Like in the example I listed before, mostly contact/sales info. I don't think it matters how the data is displayed as long as any user can edit/view the data. The reason I want to use a SP is because it seemed like an easy idea at the time, just link an exec command to a text box and whenever a number gets put in send a query, but if it's really impossible then I'm open to other ideas.
That's really the best I can explain it. I don't have full access to the SQL server so that's probably a problem. I do have full access to the sharepoint server however.
*edit* I tried something different. I made a new database on the server, and made a new table inside that database. Then I tried connecting it to the sharepoint server via external content types. I still got the unable to show web part error, but when I try to open the read list inside of designer it gives me a different error. "soap:ServerException of type 'Microsoft.SharePoint.SoapServerException' was thrown.An error has occured."
Wednesday, April 14, 2010 3:22 PM
I figured I would reply and explain what I've done and am doing. They let me reinstall the sharepoint server so I could do it with the proper accounts. This is because when it was installed last time, it was only done with one account which produced several errors in the monitoring/reports section. I also think that this might have caused other errors and such. As of right now the sharepoint is completely clean and there are no errors, with five managing accounts that were suggested in my other question about minimal accounts.
I'll continue to construct the sharepoint back to what it was and I'll report back here if I get the same errors again.
I also changed this question into a discussion for better organization, and because it's now become more of a discussion that a single question/answer topic.
Thursday, April 15, 2010 6:18 PM
So after all that, everything looks to be working correctly. However, I followed the directions from http://www.zimmergren.net/archive/2010/01/18/sp-2010-getting-started-with-business-connectivity-services-bcs-in-sharepoint-2010.aspx and I still get a "unable to display this web part".
*edit* - I found this article. http://blogs.msdn.com/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx Which helped a lot. The table is now showing correctly and I can add and remove items, which is awesome. Good game BDC, good game.
Friday, April 16, 2010 8:19 PM
So back to my question about stored procedures, I found something in SharePoint designer 2010 in the data source objects when you add a database. Well I created a database, and linked it to my SQL server just fine. In fact there's even an option to link it to a stored procedure, which is fantastic. So I made the database and set everything up, but I have no idea how to utilize the connection I just created. There are no errors, so I assume that the database connection was created successfully.
When you make a database connection, you can right click on it in sharepoint designer and go to properties. While in properties you click the edit custom query box, and you can reset what stored procedure or SQL statement you want to use. While I believe everything is setup correctly, what would be the best way to use this in a pass and receive type situation. For example, having a web page with a textbox and a button, someone types a parameter in the textbox and presses the button. Once the button is pressed it executes the stored procedure with the parameter and reports the information back to the webpage.
I've tried making lists and blank pages and linking the database to it, but the database doesn't show up anywhere. It's like I've done the hard part, now what?
Wednesday, July 28, 2010 10:43 PM
You said you were able to get past the "Some checks not able to be completed" error. How did you do that?
Shawn