Walkthrough: Create a simple BCS connection with SharePoint Designer 2010

First: You need a SharePoint 2010 platform. You can use Foundation because BCS = Business Connectivity Services – former BDC = Business Data Catalog in SharePoint 2007 – included in this “free” edition.

Second: You need a database for the walkthrough. You can use Microsoft’s sample database “AdventureWorks”. You can download them here: http://msftdbprodsamples.codeplex.com – I’ll use the database “AdventureWorksLT2008R2”: This is the lite version of the sample database.

Third: The BCS Application of SharePoint 2010 must be configured. – Create this application in the Central Administration –> Manage Service Applications

Fourth: You need a Site Collection. – In my sample it’s “http://sharepoint.local”

1. Open SharePoint Designer 2010 (“SPD”)

Open the site collection.

image

2. Click “External Content Types” in the Site Objects Navigation.

image

3. Click “External Content Type” in the Group “New” on the Ribbon.

In the “External Content Type Information” group click the text message “New external content type” beside the label “Name”. 

image

Enter “AdventureWorks SalesOrderDetails”.

After that the same text will appear beside “Display Name”.

4. Click on the text message beside “External System”:

image

5. Click “Add Connection”

image

Select “SQL Server” in the drop down “Data Source Type” in the dialog.

image

On the next dialog enter the Database Server Name, Database Name and the Name of the new connection. Select “Connect with User’s Identity”.

image

6. Now the Data Source Explorer view is filled:

image

Scroll down and select the table “SalesOrderDetails” in the tree view.

Right click on “SalesOrderDetails”. Click on “Create All Operations”.

image

7. You get this dialog.

image

Click “Next”.

image

Click “Finish”.

8. Click “Save” or Ctrl+S.

9. After the upload click on “Create Lists & Form”.

image

Now the list will be created:

image

10. Open the browser and navigate to the site collection. You’ll see the previously created list:

image

11. Open the list view.

12. You may get the error “Access denied by Business Data Connectivity”.

image

13. Then open “Central Administration” –> Manage Service Application –> Business Connectivity Service Application

Select the BCS application. Open the context menu. Click “Set Permissions”.

image

On the next dialog add all users that should have access to the BCS app and it’s lists.

image

Click “OK”.

14. Try to open the list on the site collection.

15. You may get the error message “Login failed for user NT AUTHORITYIUSR”.

This means: IUSR account has no rights to access the AdventureWorks database.

Open SQL Server Management Studio. Open “Security” –> “Logins” (at server level).

Add “NT AUTHORITYIUSR” as new login.

image

On the “User Mapping” tab change the security settings:

image

Click “OK”.

 

16. Open the list again.

image

Now it works (for me).

Walkthrough: How to move or copy a SharePoint Designer 2010 List Workflow to another list on the same site or another site.

I had to develop a Holiday Approval Workflow with SharePoint Designer 2010. – I used a “List Workflow”. – Bad mistake! – It’s not possible to copy this kind of workflow to another list on the same site or another site.

I thought I could use the “Export to Visio” functionality with the workflow at my development site and the “Import from Visio” functionality at the destination list. – That’s impossible. You get this message:

image

(“This workflow cannot be imported because it was created in SharePoint Designer for a different site, or the original workflow has been moved or deleted. To move a workflow between sites, use Save as Template (.wsp file) instead of a Visio workflow drawing.”)

You cannot “Save as Template” because it’s a “List Workflow”.

Okay. – I’ve found a workaround.

Let’s do it in detail.

1. Create a site collection “http://sharepoint.local/sites/dev”.

2. Open SharePoint Designer 2010. Open the previously created site.

3. Open the “Workflows” view.

4. On the Ribbon click “List Workflow” and select “Shared Documents”. – We will create a workflow for the list “Shared Documents”.

image

Name the workflow “Workflow 1”. Leave “Description” empty.

image

5. Insert any workflow steps you like.

image

6. Change the workflow settings. Select “Task List” and “History List”.

image

7. Click “Publish” to publish the workflow Winking smile

8. Click “Export to Visio”. Save to Workflow as .vwi file, e.g. on the desktop.

image

9. Let’s test the workflow. – I’ve created a text file “test.txt” on the desktop. Now I upload the file to the “Shared Documents” library of “http://sharepoint.local/sites/dev”.

image

Open the item context menu. Click “Workflow”.

image

Manually start the “Workflow 1”.

*image

Click “Start”.

image

In the list view you see the workflow status.

image

Click on the status. In my case it’s “Completed”.

image

The workflow history says “Workflow executed” as specified in the workflow definition above.

10. Okay. Let’s copy the workflow to the production site “http://sharepoint.local”. – A simple “import” is impossible, because it’s a list workflow… Follow my steps.

11. Start the SharePoint Designer 2010 and open the site “http://sharepoint.local”.

12. On the Ribbon click “List Workflow” and select “Shared Documents”. – We will create a workflow for the list “Shared Documents”.

image

Name the workflow “Workflow 1 Production”. Leave “Description” empty.

image

11. Do not add any workflow step!

12. Click on “Workflow Settings” menu item on the Ribbon.

13. Click “Publish” to publish the (empty) workflow.

14. After publishing click “Export to Visio” and save the file as “workflow 1 production.vwi” on the desktop.

15. Open a Windows Explorer. Select “Desktop”. You see both “.vwi” files.

16. Add the extension “.zip” to both files.

image

 

17. Open both “.vwi.zip” files. You see:

image

18. Copy “workflow.xoml.wfconfig” from “workflow 1 production.vwi.zip” to “workflow 1.vwi.zip”.

image

19. Now remove the “.zip” extension from both files. You’ll get two “.vwi” files.

image

20. In SharePoint Designer 2010 open the site “http://sharepoint.local”.

21. Click “Import from Visio” and select “workflow 1.vwi”

image

image

Click “Next”. (Maybe you get a message: “You must close all views of this workflow before importing this file.” Then stop the import. Close all views of the Workflow and restart from Step 21.)

22. Now you see the this window:

image

That it!

23. Now the workflow designer opens:

image

24. Now you can publish the workflow and test it…

25. Be as happy as me with that !!!

PS: Of course you can use any library as destination. In the sample above it does not have to be “Shared Documents” on “http://sharepoint.local” It could be another list or library as well!

Enumerate, add, update and remove SQL Server aliases by using PowerShell

I’ve created a PowerShell script for adding, updating, enumerating und removing SQL Server aliases. – You can use the script to create/remove/enumerate both 32bit und 64bit aliases in one step or only 32bit or only 64bit aliases.

You can change this script into a PowerShell module. Just see the comment below inside the script.

I’ve tested the script on Windows Server 2008 R2 with SQL Server 2008 R2 on it. – Please post your comments how it works on other systems.

Here is the script:

#region Author: Ingo Karstein / Blog: blog.kenaro.com