Step 1: Open your sharepoint website in sharepoint desinger 2010 and click on External Content Types in left hand side “site objects”. See here
Step 2: Now Click on “External Content Type” on Ribbon. See here
Step 3: Click on “Click here to discover external data sources and define operations” (showned in picture). See here
Step 4: Click on Add Connection button. It will pop up “External Data Source Type selection”.See here
Step 5: Select SQL Server as Data source type.Click on OK. It will pop up “SQL Server Connection”.See here (1) ,(2) ,(3)
Step 6: Now Enter your SQL Server Name, Database Name, and display name.
select “Connect with Impersonated Custom Identity” and enter Secure Store Application ID(remember this Application ID should be
unique, will tell explain how important it is.basically it store sql server username and password). Now click on OK. See 1, 2
Step 7: After click on OK button it will ask “SQL Server username and Password”.Enter SQL Server Credentials and Click on “OK”. See 1, 2
Step 8: now External content type is created. But we didnt created any operation for content type.
See here
step 9: Expand Database which in Data Source Explorer.
Step 10: Select your table which use used to fetch data and display in sharepoint list.See here
Step 11: Right click on table and select “Create All Operations”. See here
Step 12: Click on “NEXT”. See here
Step 13: now set of all column fields will shown here. we can select all or only few. But one column sholud be unique identifier.
Click on “NEXT”. See here
Step 14: Here we can filter table. Otherwise simply click on “Finish”. See here
Step 15: Now we can see all operation in “External Content Type Operations”. See here
Step 16: Now we can create external list.Click on “Create Lists & Form” which located on ribbon.will pop up one box. See here
Step 17: Give external list name in popup box.Click on “OK”. See 1, 2, 3.
Step 18: Now we can see newly create external list in “External Lists” panel. See here
Step 19: Now open your site in browser. See here
Step 20: Select your external list in leftside navigation.It will show error message like “Access denied by Bussiness Data
Connectivity”. See here
While creating external content type we given credential which are not saved anywhere those are only used for to display database in
“Data source Explorer”.
Did get my point leave it follow my steps.
Step 21: Now Go To your central administrator and select “manage service applications” which is “Application Management” section. See here
Step 22: Click on Bussiness Data Connectivity. See here
Step 23: Now you can find your external content type and select your content type. See here
Step 24: Now Click on “Set Object Permissions”,it will open “Set Object Permissions” pop up.
See 1, 2.
Step 25: Enter Account name how can use this content type and Click on Add. Select Permissions Check boxes. Then Click on “OK”.See here
Step 26: Now to go user website and click on ur external list. Here You will Get error message again,but this time error message is different which is “Unable to display this webpart. To troubleshoot the problem, open this web page in a microsoft sharepoint Foundation-coompatible HTML editor such as Microsoft Sharepoint Designer”. See here
Step 27: GO to your central administration site and Select “Application Management” which is in Quick Launch.See here
Step 28: click on “Manage Service applications” which is in “Service applications” section.See here
Step 29: Now select “Secure Store Service”. See here
If you are using this first time it will show one message like “Before creating a new secure store target application, you must first generate a new key for this Secure store service application from the ribbon”. See here
Step 30: Click on Generate new key from the ribbon. See here.
step 31: Give any name which should be strong name. Click on “OK”. See 1, 2
Step 32: Click on “New” on the ribbon. See here
Step 33: SPecify a unique Target application id, display name,contact email(any but should be valid) and Click on “NEXT”.(Remember that This name should be already given while creating External Content Type in Step 6). See 1, 2
Step 34: Click on “NEXT”. See 1, 2
Step 35: Enter Target Application Administrators and Click on “OK”. See here
Step 36: Now you can see newly create Target Application ID. See here
Step 37: now select newly create Target Application ID and click “set” on the ribbon. See here
Step 38: Enter Credentials owner(full control user like administrator), SQL server Database username and password. Click on “OK”. See here
Step 39: Now go to your website and click on external list from quick launch.This time u will get list without any error. See here
Step 40: this step is not require but if you get any error in previous step. GO to your central administrator–>Application Management–>Service application–>select your External content type and Click “Configure” on the ribbon. See here
BCS so beautiful… we can operate all operation on the list and we can find the changes on server.
NOTE: all these screenshot are placed in one location. Each screenshot is a step.
Give your valuable feedback to make this blog more valuable. Thank you.
SharePoint 2010 