Create an External Content type based on backend SQL Server Database and create an External list based on it.
Open the Site in SharePoint Designer 2010: http://portal.contoso.com
Provide username and password in the promotion for open the site
Opening the site http://portal.contoso.com by communicating with the Web service
Retrieving External Content type Report
Create new external content type: Define General Properties for External Content type
Content type name: Contact; Name Space: http://portal.contoso.com; Office Item Type: Contact; Enable offline Sync to External list
External System: Click here to discover external data sources and define operations
Add Connection
Managing Connections to external data sources and the operations for this external content type
External Data Source Type Selection: SQL Server -> Open the SQL Server Connection
SQL Server Connection: Populate the Database Server and Database name in the box
Navigate to the appropriate database (AdventureWorks) and scroll through the required table (Contact)
Table: Verify the available operations for that table
Operations for External content type means action that can be performed on the data associated with the external system -> Click on Create all Operations
Specify the parameters for the Operation
Example: Get the “Last Name” parameter-> Office property: Last Name; Check the Show in Picker box.
Filter Parameter Configuration
Add Filter Parameter: Add a Limit filter to the Query Item List Operation
Add Filter Type: Limit (Strongly Recommended to add a filter of type ‘Limit’ for this operation type)
Filter Filed: None; Default value given as “100” for the limit filter type
Save the Content type to the Business Data Connectivity Metadata Store
Create an External List for the External Content type
External List : Business Contacts ; System Instance : AdventureWorks
External List: Business Contacts Created
Set Business Connectivity Store Permissions
List and Libraries -> External List-> Business Contacts -> Open Preview in Browser
Access is being denied by Business Data Connectivity Service (Reason: There is no permission for viewing this list)
Set the permission: Open CA->App mgmt. ->Manage Service Applications-> Business Data Connectivity Service->Manage->Set Metadata Store Permissions
Add Contoso\Administrator and feed all the (Edit, Execute, Selectable in Clients, Set Permissions) permission to the user.
Propagate permissions to all BDC Models, External Systems and External Content types in the BDC Metadata Store.
Work with External Content type in the Browser: Navigate to the http://portal.contoso.com
List will display with the no permission error now
Edit Item: Edit any of the lists and include any data in the list.
Example: Data Included in one of the List
