Thursday, March 27, 2008

Mirroring Databases in SQL Server 2005

Introduction

This tutorial will show you how to mirror one database from one instance of SQL Server 2005 to another. I will be using the AdventureWorks database for this demonstration and will not be using a Witness server (only a Principal and Mirror server [i.e., Source and Target]).

Please note that both the Principal and Mirror servers must be of the same SQL Server edition. If they are not, mirroring will not work.

Enable Mirroring in SQL Server 2005 if SP1 is Not Installed

On computer/instance mirror A:

  1. Open the SQL Server Configuration Manager for SQL Server 2005, typically, this is located in the Programs menu of the Start button (Microsoft SQL Server 2005, then Configuration Tools).
  2. Select the SQL Server 2005 Services node.
  3. In the right pane, right-click on the SQL Server service, then click on Properties.
    SQL Server Configuration Manager for SQL Server 2005
  4. Click on the Advanced tab.
  5. Locate the Startup Parameters field, then append trace flag 1400 to it: ; -T 1400
  6. Right-click on the SQL Server service, then click on either Restart or Start.

On computer/instance mirror B:

  1. Repeat the steps outlined for On computer/instance mirror A for this computer.

Please note mirroring doesn't seem to work if you use this trace flag with SQL Server 2005 without any Service Packs installed. The trace flag seems to work ONLY with older RTM versions. So, if you have SQL Server 2005 with no Service Packs installed, please install them on all running instances.

Preparing Databases for Mirroring

  1. Using SSMS (SQL Server Management Studio), set the Recovery Model of the source database to Full, if it's not already.
  2. Backup the source database AND transaction log(s), a full backup first, then a transaction log(s) backup.
  3. Restore the just-backed up source database AND transaction log(s) to the target server/instance using the WITH NORECOVERY option for each restore(s), starting with the full database restore, then transaction log(s) restore(s), ensuring that the data file paths are not physically the same (i.e., do not overwrite the source data files with this restore).

Configuring and Starting Mirroring

  1. Using SSMS, launch the Mirror... task on the source database.
  2. Click on the Configure Security... button.
  3. Click on Next.
  4. Select No to NOT include a witness server.
  5. Click on Next.
  6. Click on Next to save the security configuration.
  7. Select the source server, which should already be selected, in the Principal server instance field, set or leave the port, then specify a unique Endpoint name.
  8. Click on Next.
  9. Select the target server in the Mirror server instance field, set or leave the port, then specify a unique Endpoint name.
  10. Click on Next.
  11. Set the Principal and Mirror Service Accounts, preferrably, they will be the same account.
  12. Click on Next.
  13. Click on Finish.

    If you run into problems, re-click on the Configure Security button to change the parameters. You should get Successful results for both Endpoints.
  14. Click on Do Not Start Mirroring.
  15. Start mirroring by clicking on the Start Mirroring button. If successfull, the Pause button should be enabled.

Using the Database Mirroring Monitor

  1. In SSMS, right-click on the source database, navigate to Tasks, then click on Launch Database Mirroring Monitor...
  2. Click on Register mirrored database.
  3. Click on the Connect button, then select the Mirror/target server.
  4. Click on the Register checkbox in the Mirrored databases list.
  5. Click on OK.
  6. In the left pane, expand the Database Mirroring Monitor node by clicking on the plus sign.
  7. Click on the mirrored database. You should now see the status of the mirror.

Failing Over to the Mirrored/Target Database

  1. Execute the following T-SQL command at the mirror:ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Issues to Consider About Database Mirroring and this Tutorial

  1. The source/Principal and target/Mirror SQL Server Service account(s) must be granted CONNECT permission on the target/Mirror and source/Principal servers respectively. If not, mirroring will fail upon start.
  2. Encryption - By default, data is encrypted, sent to the mirror, then decrypted. This uses up more server resources and bandwidth than it would without encryption. So, ONLY use encryption if the data being mirrored is sensitive and should be encrypted.
  3. Witness - This tutorial doesn't use a Witness server. A witness server can provided automated failover capabilities.
  4. Asynchronous mirroring is only available in SQL Server 2005 Enterprise & Developer Editions, Synchronous mirroring is available in all others, except for Express Edition. So, fully evaluate the performance implications when using mirroring with SQL Server 2005 Standard Edition, as performance might be negatively impacted in a production environment.

Monday, March 24, 2008

Business Data Catalog Definition Editor Installation Error 'SetParent failed for Database'

If you get a 'SetParent failed for Database' error during the installation of the Microsoft Business Data Catalog Definition Editor, allow remote connections to the local SQL Server 2005 Express Edition server:

  1. Start the "SQL Server Surface Area Configuration" application.
  2. Click on "Surface Area Configuration for Services and Connections".
  3. Under Database Engine, select Remote Connections.
  4. Select "Local and remote connections".
  5. Click on OK.
  6. Close the application.
  7. Retry the BDC Editor installation.

Wednesday, March 19, 2008

Displaying External Web Services Data in SharePoint Server 2007

Prerequisites

  • SharePoint Server 2007 (MOSS 2007) Enterprise Edition
  • SharePoint Server 2007 SDK v1.3 or newer

Building the Application Definition File

  1. If you haven't already installed the BDC Definition Editor, install it. It should be located here: "%ProgramFiles%\2007 Office System Developer Resources\Tools\BDC Definition Editor". Just launch the setup.exe program to install it.
  2. From the Programs menu, startup the Microsoft ® Business Data Catalog Definition Editor.
  3. Click on "Add LOB SYstem".
  4. Click on "Connect to Webservice".
  5. Enter the following Web Service URL in the Url field: http://ws.cisa.ca/WehireWS/JobsWs.asmx?WSDL.
  6. Click on Connect.
  7. Click on the "Add Web Method" side-button.
  8. Drag the GetJobsByTitle method onto the Design area.
  9. Call the LOB System Name "JobsWesternCanadaWebService" (without quotes).
  10. In the Metadata Objecta pane, click on the JobsWesternCanadaWebService system.
  11. In the Properties pane, set the WildcardCharacter field to "$" (without quotes).
  12. Ensure that that WsdlAuthenticationMode is set to PassThrough.
  13. Set the DefaultDisplayName to "Jobs in Western Canada Web Service" (without quotes).
  14. In the Metadata Objects pane, expand the Entities node, then click on Entity0.
  15. In the Properties pane, set the Title to "Jobs in Western Canada" (without quotes).
  16. Set the Name property to "JobsWesternCanada" (without quotes).
  17. Set the DefaultDisplayName to "Jobs in Western Canada" (without quotes).
  18. In the Metadata Objects pane, right-click on the Identifiers node, then click on Add Identifier.
  19. In the Properties pane, set the Name to "Title" (without quotes).
  20. In the Metadata Objects pane, locate the GetJobsByTitle method, then locate the title parameter.
  21. Expand the title parameter's nodes until you reach the Item node, then select it.
  22. In the Properties pane, set the Identifier property to Title[JobsWesternCanada].
  23. Set the name property to "Title" (without quotes).
  24. In the Metadata Objects pane, expand the Return parameter's nodes until you reach the Item node, then click on the Title node.
  25. In the Properties pane, set the Identifier property to Title[JobsWesternCanada].
  26. In the Metadata Objects pane, locate the Filters node of the GetJobsByTitle method.
  27. Right-click on the Filters node, then click on Add Filter.
  28. In the Properties pane, set the Name to "TitleEqualsFilter" (without quotes).
  29. Set the DefaultDisplayName to "Title" (without quotes).
  30. Set the FilterType to Equals.
  31. In the Metadata Objects pane, locate and expand the last title parameter node of the GetJobsByTitle method, then select it.
  32. In the Properties pane, set the FilterDescriptor to TitleEqualsFilter.
  33. In the Metadata Objects pane, right-click on the Instances node of the GetJobsByTitle method, then click on Add Method Instance.
  34. Select Association as the Method Instance Type.
  35. In the Source Entities pane, set the entity to JobsWesternCanada.
  36. Click on OK.
  37. In the Properties pane, set the Name property to "GetJobsByTitleAssociationInstance" (without quotes).
  38. In the Metadata Objects pane, right-click on the Instances node of the GetJobsByTitle method, then click on Add Method Instance.
  39. Select Finder as the Method Instance Type.
  40. Click on OK.
  41. In the Properties pane, set the Name to "GetJobsByTitleFinderInstance" (without quotes).
  42. Test to ensure the Finder method works by a) right-clicking on the GetJobsByTitleFinderInstance method, b) clicking on Execute, c) entering ".NET Web Developer" (without quotes) in the Value field, d) then clicking on the Execute button. You should get results back from the web service. Close the test form.
  43. In the Metadata Objects pane, select the JobsWesternCanadaWebService LOB system.
  44. Click on the Export button, then save the file as "JobsWesternCanadaWebService.xml" (without quotes).

Importing the BDC Definition File

  1. Open up the SharePoint 3.0 Central Administration web page.
  2. Under Shared Services Administration, click on the shared service listed.
  3. Under Business Data Catalog, click on "Import application definition".
  4. Click on the Browse button, then locate and open the JobsWesternCanadaWebService.xml file.
  5. Click on Import.
  6. You should've gotten a success message. Click on OK.
  7. Click on Manage Permissions.
  8. Add the users/groups you'd like to grant permission to execute the LOB system.

Displaying the Web Service Data via a Business Data List

  1. In the SharePoint Server 2007 site, navigate to a web part page where you'd like to see the web service in action.
  2. Click on Site Actions, then Edit Page.
  3. Click on "Add a Web Part".
  4. Expand the "All Web Parts" node.
  5. Under Business Data, check the "Business Data List" web part.
  6. Click on Add.
  7. Click on Edit in the "Business Data List" web part, then click on "Modify shared web part".
  8. Click on the Browse icon, next to the Type field.
  9. Select JobsWesternCanadaWebService_Instance, then click on OK.
  10. Click on the Apply button.
  11. Click on the OK button.
  12. Click on the Publish button, if necessary, to save the page changes.
  13. In the "Jobs in Western Canada List" web part, enter ".NET Web Developer" (without quotes) in the "Title is equal to" field.
  14. Click on Retrieve Data. As of March 23, 2008, there is one result listed.

Monday, March 17, 2008

What to do if you get MOSS 2007 COM/W3SVC-WP 80070005 Errors

I recently installed a MOSS 2007 server and applied several service packs, including WSS SP1 and MOSS 2007 SP1. This resulted in COM+ application security settings being reset to their default settings, specifically the COM+ application, OSearch.

The event viewer revealed the following error, for me:

The Execute method of job definition Microsoft.Office.Server.Search.Administration.IndexingScheduleJobDefinition (ID 86e55a13-e654-4458-aa3c-87f910d6ec9e) threw an exception. More information is included below. Retrieving the COM class factory for component with CLSID {3D42CCB1-4665-4620-92A3-478F47389230} failed due to the following error: 80070005.

Other associated errors produced by this problemare shown below and can be resolved by the following steps:

Starting up database 'SharedServices1_Search_DB_GUID'.

The worker process failed to initialize the http.sys communication or the w3svc communication layer and therefore could not be started. The data field contains the error number.

Unable to reach Excel Calculation Services http://HOSTNAME:PORT/SharedServicesProvider/ExcelCalculationServer/ExcelService.asmx.

Ensure that all Relevant COM+ Applications are Accessible by the ASPNET, WSS & Service Accounts

To fix this problem, follow these steps on the computer hosting MOSS 2007:

  1. Click on the Windows' Start button.
  2. Click on Run.
  3. Type "%WINDIR%\system32\Com\comexp.msc" (without quotes), then press the Enter key.
  4. Navigate to the Component Services, Computers, My Computer, DCOM Config folder.
  5. Right-click on the OSearch application, then click on Properties.
  6. Click on the Security tab.
  7. In the Launch and Activation section, click on Edit.
  8. Ensure that any and all WSS* accounts are listed. If they are not, add them.
  9. Grant these accounts all of the permissions available.
  10. Click on OK.
  11. In the Access Permissions section, click on Edit.
  12. Ensure that all of the the ASPNET, WSS*, and SP service accounts are listed. If they are not, add them.
  13. Grant these accounts all of the permissions available.
  14. Click on OK twice.
  15. Repeat steps 5 through 14 above for the following other SP COM applications: SharedAccess, SPSearch.
  16. Restart IIS by running "iisreset" (without quotes) from a command prompt.

Ensure that the Identity Account is the Same Across All WSS/MOSS Shared Services (ONLY) Web Application Pools

  1. Login to SharePoint Central Administration.
  2. Under Central Administration, click on Operations.
  3. Under Security Configuration, click on Service accounts.
  4. Select Web application pool.
  5. Select the Web Service pool.
  6. Select the affected Web service.
  7. In the list of Application pools, for each Shared Service Pool, configure the account to be the same and that that account has been granted access to the COM+ applications in the preceding section.
  8. Click on OK once done.
  9. Restart IIS6 by executing "iisreset" from the Windows' Run dialog box.

Monday, March 10, 2008

Creating Custom SharePoint Server 2007 Workflows with VS.NET 2005

Table of Contents

Introduction

As an enterprise developer, it's no surprise that one business process often encountered is the approving of documents, such as legal contracts, budgets, etc. Business workflows are often followed, but in a way that is typically inefficient. For example, an employee might fill out an expense report and forward it to HR for approval. This typically involves filling out an Excel or Word document, e-mailing it to HR for processing, then waiting for either a check and/or a response or request for changes. SharePoint offers a simplified solution for routing documents through a workflow process. The problem is that there are few workflows available in SharePoint, some may not meet the requirements/needs of the business, and you can't modify existing, builtin SharePoint workflows.

There are two ways to add custom-built workflows to SharePoint Server 2007: SharePoint Designer 2007 and Visual Studio .Net 2005 with Extensions for Workflows (WF). SharePoint Designer 2007 offers limited workflow authoring, but VS.NET 2005 (and later) offers significantly extensible capabilities.

The following tutorial shows you how to develop a simple workflow that allows an approver to reject, approve, or set other statuses of a document.

Requirements: (All of the following must be installed and performed on the SharePoint Server 2007 server.)

(All of the following must be performed on the SharePoint Server 2007 server.)

Back to TOC

Creating the InfoPath Initiation Form

  1. Startup InfoPath 2007.
  2. Click on Design a Form Template, in the Design a Form area.
  3. Click on Blank, in the Form Template area.
  4. Click on OK.
  5. Click on Data Source, under Design Tasks.
  6. Right-click on the myFields folder, then click on Properties.
  7. Set the Name of the Data Source to "initiationFields" (without quotes).
  8. Click on OK.
  9. Right-click on the initiationFields folder, then click on Add.
  10. Enter "initApprover" (without quotes) in the Name field.
  11. Click on OK.
  12. Right-click on the initiationFields folder, then click on Add.
  13. Enter "initExpires" (without quotes) in the Name field.
  14. Set the Data type to Date and Time.
  15. Click on OK.
  16. Right-click on the initiationFields folder, then click on Add.
  17. Enter "initComments" (without quotes) in the Name field.
  18. Click on OK.
  19. Click on Design Tasks, then click on Controls.
  20. Uncheck the Automatically create data source check box.
  21. From the Controls toolbox, drag a Text Box control onto the design area.
  22. Click on initApprover, then click on OK.
  23. Rename the label to "Approver:" (without quotes).
  24. Drag and drop a Date Picker control onto the design area.
  25. Click on initExpires, then click on OK.
  26. Rename the label to "Expires:" (without quotes).
  27. Drag a Text Box control onto the design area.
  28. Click on initComments, then click on OK.
  29. Rename the label to "Comments:" (without quotes).
  30. Drag a button onto the design area.
  31. Right-click on the button, then click on Button Properties.
  32. Set the Label to "Send for Approval" (without quotes).
  33. Click on Rules.
  34. Click on Add.
  35. Click on Add Action.
  36. Select Submit using a data connection, from the Action drop-down list.
  37. Click on Add.
  38. Select Create a new connection to.
  39. Select Submit data.
  40. Click on Next.
  41. Select To the hosting environment...
  42. Click on Next.
  43. Name the data connection "Initiation" (without quotes).
  44. Click on Finish.
  45. Click on OK.
  46. Click on Add Action.
  47. Select Close the form, from the Action drop-down list.
  48. Uncheck the "... prompt to save ..." check box.
  49. Click on OK four times.
  50. Click on Tools, in the menu bar, then click on Form Options.
  51. Click on Security and Trust.
  52. Uncheck "Automatically determine security level...".
  53. Select Full Trust.
  54. Check the "Sign this form template" check box.
  55. Click on Create Certificate.
  56. Click on OK.
  57. Click on Compatibility.
  58. Check the "Design a form template that can be opened in a browser...".
  59. In the "Enter the URL of a server..." field, enter the HTTP path to the SharePoint 2007 server (e.g., http://dotnetfun).
  60. Click on OK.
  61. Attaching an Item Metadata Schema File which allows us to set form fields from the VS.NET workflow.

  62. Open/start an instance of Windows' Notepad.
  63. Enter the following XML code:<z:row xmlns:z="#RowsetSchema" ows_initApprover="" ows_initExpires="" ows_initComments="" />
  64. Save the document as ItemMetadata.xml (ensuring that "Save as Type" is set to "All types").
  65. Close Windows' Notepad.
  66. Click on Data Source, under Design Tasks.
  67. Click on Data Source.
  68. Click on Manage Data Connections.
  69. Click on Add.
  70. Select "Create a new connection to:", then select Receive data.
  71. Click on Next.
  72. Select XML document, then click on Next.
  73. Click on Browse, then locate the ItemMetadata.xml file you just saved.
  74. Open the ItemMetadata.xml file.
  75. Click on Next.
  76. Select "Include the data as a resource...", then click on Next.
  77. Set the name to "ItemMetadata" (without quotes), and ensure the check box is checked.
  78. Click on Finish.
  79. Click on Close.
  80. In the design area, right-click on the initComments text box, then click on Properties.
  81. In the Default Value area, click on the button next to the Value field.
  82. Click on "Insert Field or Group".
  83. From the Data Source drop-down list, select ItemMetadata.
  84. Click on/select the initComments field.
  85. Click OK three times.
  86. In the design area, right-click on the initExpires Date Picker, then click on Properties.
  87. In the Default Value area, click on the button next to the Value field.
  88. Click on "Insert Field or Group".
  89. From the Data Source drop-down list, select ItemMetadata.
  90. Click on/select the initExpires field.
  91. Click OK three times.
  92. Publishing the form.

  93. Click on File, from the menu bar, then click on Publish.
  94. Click on "To a network location...".
  95. Click on Next.
  96. Click on the Browse button, then press Alt-4.
  97. Call the folder "Published InfoPath Forms" (without quotes).
  98. Click on OK.
  99. Set the file name to "ApprovalInitiation.xsn" (without quotes).
  100. Click on OK.
  101. Set the "Form template name" to "ApprovalInitiation" (without quotes).
  102. Click on Next, ensuring the user access path/field is empty.
  103. Click on Publish.
  104. Click on Close.
  105. Generating an initiationFields class file for use with the VS.NET workflow project.

  106. Click on File, from the menu bar, then click on "Save as Source Files".
  107. Navigate to where the form was published in the preceding steps, then click on OK, clicking on OK to ignore the warning.
  108. In Windows, click on the Start button, then navigate to Programs, Microsoft Visual Studio 2005, Visual Studio Tools, then click on "Visual Studio 2005 Command Prompt".
  109. Navigate to the location where the form was published. For example, try typing and entering this: cd "%HOMEPATH%\My Documents\Published InfoPath Forms"
  110. Type and enter the following command: xsd /c myschema.xsd /o:.
  111. Back in InfoPath, Save the InfoPath document, calling it "ApprovalInitiation.xsn" (without quotes), clicking on OK when prompted about Publishing the form.
  112. Close InfoPath.

Back to TOC

Creating the InfoPath Action Form

  1. Startup InfoPath 2007.
  2. Click on Design a Form Template, in the Design a Form area.
  3. Click on Blank, in the Form Template area.
  4. Click on OK.
  5. Click on Data Source, under Design Tasks.
  6. Right-click on the myFields folder, then click on Properties.
  7. Set the Name of the Data Source to "actionFields" (without quotes).
  8. Click on OK.
  9. Right-click on the actionFields folder, then click on Add.
  10. Set the Name to "actionStatus" (without quotes).
  11. Click on OK.
  12. Right-click on the actionStatus field in the actionFields data source, then click on Properties.
  13. In the Defaul Value area, click on the button next to Value.
  14. Click on "Insert Field or Group".
  15. Activate the Data Source drop-down list box, then select ItemMetadata.
  16. Click on actionStatus, then click on OK.
  17. Click on OK two times.
  18. Right-click on the actionFields folder, then click on Add.
  19. Set the Name to "actionComments" (without quotes).
  20. Click on OK.
  21. Click on Design Tasks, then click on Controls.
  22. Uncheck the Automatically create data source check box.
  23. Drag a Drop-down List Box onto the design area.
  24. Select actionStatus, then click on OK.
  25. Rename the label to "Approval Status:" (without quotes).
  26. Right-click on the drop-down list box, then click on Properties.

  27. In the "List box entries" section, ensure that "Enter list box entries manually" is selected.
  28. Select the first list entry, then click on Modify.
  29. Enter "Approve" (without quotes) in the Value and Display Name fields.
  30. Click on OK.
  31. Click on Add.
  32. Enter "Needs Revising" in the Value and Display Name fields.
  33. Click on OK.
  34. Click on Add.
  35. Enter "Reject" in the Value and Display Name fields.
  36. Click on OK.
  37. Click on OK.
  38. Drag and drop a Text Box control onto the design area.
  39. Select actionComments, then click on OK.
  40. Drag a button onto the design area.
  41. Right-click on the button, then click on Button Properties.
  42. Set the Label to "Submit" (without quotes).
  43. Click on Rules.
  44. Click on Add.
  45. Click on Add Action.
  46. Select Submit using a data connection, from the Action drop-down list.
  47. Click on Add.
  48. Select Create a new connection to.
  49. Select Submit data.
  50. Click on Next.
  51. Select "To the hosting environment...".
  52. Click on Next.
  53. Name the data connection "Action" (without quotes).
  54. Click on Finish.
  55. Click on OK.
  56. Click on Add Action.
  57. Select Close the form, from the Action drop-down list.
  58. Uncheck the "... prompt to save ..." check box.
  59. Click on OK four times.
  60. Click on Tools, in the menu bar, then click on Form Options.
  61. Click on Security and Trust.
  62. Uncheck "Automatically determine security level...".
  63. Select Full Trust.
  64. Check the "Sign this form template" check box.
  65. Click on Create Certificate.
  66. Click on OK.
  67. Click on Compatibility.
  68. Check the "Design a form template that can be opened in a browser...".
  69. In the "Enter the URL of a server..." field, enter the HTTP path to the SharePoint 2007 server (e.g., http://dotnetfun).
  70. Click on OK.
  71. Attaching an Item Metadata Schema File which allows us to set form fields from the VS.NET workflow.

  72. Open/start an instance of Windows' Notepad.
  73. Enter the following XML code:<z:row xmlns:z="#RowsetSchema" ows_actionStatus="" ows_actionComments="" />
  74. Save the document as ItemMetadata.xml (ensuring that "Save as Type" is set to "All types").
  75. Close Windows' Notepad.
  76. Click on Data Source, under Design Tasks.
  77. Click on Data Source.
  78. Click on Manage Data Connections.
  79. Click on Add.
  80. Select "Create a new connection to:", then select Receive data.
  81. Click on Next.
  82. Select XML document, then click on Next.
  83. Click on Browse, then locate the ItemMetadata.xml file you just saved.
  84. Open the ItemMetadata.xml file.
  85. Click on Next.
  86. Select "Include the data as a resource...", then click on Next.
  87. Set the name to "ItemMetadata" (without quotes), and ensure the check box is checked.
  88. Click on Finish.
  89. Click on Close.
  90. In the design area, right-click on the actionComments text box, then click on Properties.
  91. In the Default Value area, click on the button next to the Value field.
  92. Click on "Insert Field or Group".
  93. From the Data Source drop-down list, select ItemMetadata.
  94. Click on/select the actionComments field.
  95. Click OK three times.
  96. Publishing the form.

  97. Click on File, from the menu bar, then click on Publish.
  98. Click on "To a network location...".
  99. Click on Next.
  100. Click on the Browse button, then navigate to and open the "Published InfoPath Forms" folder.
  101. Click on OK.
  102. Set the file name to "ApprovalAction.xsn" (without quotes).
  103. Click on OK.
  104. Set the "Form template name" to "ApprovalAction" (without quotes).
  105. Click on Next, ensuring the user access path/field is empty.
  106. Click on Publish.
  107. Click on Close.
  108. Save the InfoPath document, calling it "ApprovalAction.xsn" (without quotes), clicking on OK when prompted about Publishing the form.
  109. Close InfoPath.

Back to TOC

Creating the Approval Workflow in VS.NET 2005

  1. Startup VS.NET 2005.
  2. Click on File-->new--Project.
  3. Select SharePoint, under Project Types.
  4. Select SharePoint Server Sequential Workflow Library, under Templates.
  5. Name the project and solution "SharePointApprovalWorkflow" (without quotes).
  6. Click on OK.
  7. Adding the initiationFields class file to the project.

  8. In the Solution Explorer, right-click on the SharePointApprovalWorkflow project, then navigate to Add, then click on Existing Item...
  9. Navigate to the folder where the initiationFields class file was generated using the VS.NET xsd command. This might be "%HOMEPATH%\My Documents\Published InfoPath Forms"
  10. Select the myschema.cs class file, but DO NOT open it.
  11. Press F2 to rename the class file, calling it "initiationFields.cs" (without quotes), then press Enter to commit the rename.
  12. Click on Add, ensuring the class file was added in the Solution Explorer.
  13. Creating the workflow.

  14. In the Solution Explorer, right-click on Workflow1.cs, then click on View Designer.
  15. In the designer, right-click on onWorkflowActivated1, then click on Generate Handlers.
  16. Directly above the onWorkflowActivated1_Invoked method, but NOT inside of the method itself, type the following: public String initApprover = default(String); public DateTime? initExpires = default(DateTime?); public String initComments = default(String);
  17. In the designer, right-click on onWorkflowActivated1, then click on Properties.
  18. Inside of the onWorkflowActivated1_Invoked method, type the following code: XmlSerializer serializer = new XmlSerializer(typeof(initiationFields)); XmlTextReader reader = new XmlTextReader(new System.IO.StringReader(this.workflowProperties.InitiationData)); initiationFields initFields = (initiationFields)serializer.Deserialize(reader); this.initApprover = initFields.initApprover; this.initExpires = initFields.initExpires; this.initComments = initFields.initComments;
  19. In the Solution Explorer, right-click on Workflow1.cs, then click on View Designer.
  20. Right-click on onWorkflowActivated1, then click on Properties.
  21. In the Toolbox, drag and drop a CreateTask object onto the design area, directly beneath the onWorkflowActivated1 object, from the SharePoint - Workflow Task area.
  22. In the Properties pane, click on the CorrelationToken property's empty field, then type "taskToken" (without quotes).
  23. Click on the plus sign next to the CorrelationToken property, then select Workflow1 in the OwnerActivityName property.
  24. Click on the TaskID property, then click on the elipses button.
  25. Click on the "Bind to a new member" tab."
  26. Select Create Field, then type "taskID" (without quotes) in the "New member name" field.
  27. Click on OK.
  28. Click on the TaskProperties property, then click on the elipses button.
  29. Click on the "Bind to a new member" tab."
  30. Select Create Field, then type "taskProperties" (without quotes) in the "New member name" field.
  31. Click on OK.
  32. Press Ctrl-Shift-S to save all of your changes.
  33. In the designer, right-click on createTask1, then click on Generate Handlers.
  34. At the top of the class file, add the following using statement:using Microsoft.SharePoint.Utilities;
  35. Somewhere in the class file, add the following method: private SPPrincipalInfo ResolveUser(String UserName) { return SPUtility.ResolvePrincipal( this.workflowProperties.Web, UserName, SPPrincipalType.All, SPPrincipalSource.All, null, false); }
  36. In the createTask1_MethodInvoking method, type the following code: this.taskID = Guid.NewGuid(); this.taskProperties.AssignedTo = this.ResolveUser(this.initApprover).LoginName; this.taskProperties.Description = "Approve " + this.workflowProperties.Item.Name + " Task Assigned to " + this.ResolveUser(this.initApprover).DisplayName; this.taskProperties.DueDate = (this.initExpires != null ? this.initExpires.Value : DateTime.Now.AddDays(7)); this.taskProperties.Title = "Approve " + this.workflowProperties.Item.Name; this.taskProperties.ExtendedProperties["actionStatus"] = "Approve"; this.taskProperties.ExtendedProperties["actionComments"] = DateTime.Now.ToString() + ": " + this.workflowProperties.OriginatorUser.Name + " wants you to approve this document. Comment(s): " + this.initComments + "\r\n"; this.taskProperties.SendEmailNotification = true;
  37. Click on the Workflow1.cs [Design] tab.
  38. From the Toolbox, drag and drop a While to directly beneath the createTask1 object.
  39. In the Properties pane, click on the Condition property, then activate the drop-down list.
  40. Select Code Condition.
  41. Click on the plus sign next to Condition, then type "whileActivity1_CheckIfTaskComplete" (without quotes) in the empty Condition property field.
  42. Press the Enter key, this will take you back to the code view.
  43. Right above the whileActivity1_CheckIfTaskComplete method, NOT inside of it, type the following code: private bool blnIsTaskComplete = false;
  44. Inside of the whileActivity1_CheckIfTaskComplete method, type the following code: e.Result = !this.blnIsTaskComplete;
  45. Click on the Workflow1.cs [Design] tab.
  46. In the Toolbox, drag and drop an OnTaskChanged object directly into the whileActivity1 area.
  47. In the Properties pane, click on the AfterProperties property, then elipses button.
  48. Click on the "Bind to a new member tab", then select the Create Field option.
  49. In the "New member name" field, type "taskChangedAfterProperties".
  50. Click on OK.
  51. In the Properties pane, click on the BeforeProperties property, then elipses button.
  52. Click on the "Bind to a new member tab", then select the Create Field option.
  53. In the "New member name" field, type "taskChangedBeforeProperties".
  54. Click on OK.
  55. Click on the CorrelationToken property, then activate the drop-down list.
  56. Select taskToken from the list (NOT workflowToken).
  57. In the Properties pane, click on the Executor property, then elipses button.
  58. Click on the "Bind to a new member tab", then select the Create Field option.
  59. In the "New member name" field, type "taskChangedExecutor".
  60. Click on OK.
  61. In the Properties pane, click on the TaskID property, then elipses button.
  62. In the "Bind to existing member" tab, locate and select the taskID property.
  63. Click on OK.
  64. Press Ctrl-Shift-S to save all of your changes.
  65. In the designer area, right-click on onTaskChanged1, then click on Generate Handlers.
  66. In the onTaskChanged1_Invoked method, type the following code: this.blnIsTaskComplete = (this.taskChangedAfterProperties.ExtendedProperties["actionStatus"].ToString() == "Approve" || this.taskChangedAfterProperties.ExtendedProperties["actionStatus"].ToString() == "Reject"); // If the task has expired, set it to complete: if (this.initExpires != null && DateTime.Now > this.initExpires) this.blnIsTaskComplete = true;
  67. Click on the Workflow1.cs [Design] tab.
  68. From the Toolbox, drag and drop a CompleteTask object directly beneath the whileActivity1 object.
  69. In the Properties pane, click on the CorrelationToken property, then activate the drop-down list.
  70. Select taskToken.
  71. In the Properties pane, click on the TaskId property, then click on the elipses button.
  72. In the "Bind to an existing member" tab, locate and select the TaskID property.
  73. Click on OK.
  74. In the Properties pane, click on the TaskOutcome property, then click on the elipses button.
  75. Click on the "Bind to a new member" tab, then click on Create Field.
  76. In the "New member name" field, type "taskOutcome".
  77. Click on OK.
  78. Right-click on completeTask1, then click on Generate Handlers.
  79. In the completeTask1_MethodInvoking method, type the following code: this.taskOutcome = this.taskChangedAfterProperties.ExtendedProperties["actionStatus"].ToString(); this.taskProperties.PercentComplete = 100;
  80. Press Ctrl-Shift-S to save all of your changes.
  81. Build the project by pressing F6.
  82. Sign the project with a strong-name key and add it to the GAC.

  83. In the Solution Explorer, right-click on SharePointApprovalWorkflow, then click on Properties.
  84. Click on the Signing tab/option.
  85. Ensure "Sign the Assembly" is checked.
  86. Under "Choose a strong name key file", click on the drop-down list, then click on/select New...
  87. Set the "Key file name" to "SharePointApprovalWorkflow.snk" (without quotes).
  88. Uncheck the option to password-protect the key.
  89. Click on OK.
  90. Press Ctrl-Shift-S to save all of your changes.
  91. Build the project by pressing F6.
  92. In Windows, open/startup the Microsoft .Net Framework 2.0 Configuration MMC tool, typically from the Administrative Tools folder in the Programs menu of Start button (aka mscorcfg.msc).
  93. Navigate to and select My Computer.
  94. Click on "Manage the Assembly Cache".
  95. Click on "View List of Aseemblies in the Assembly Cache".
  96. Right-click on the Assembly Cache node in the left pane, then click on Add...
  97. Navigate to the bin\Debug folder where the SharePointApprovalWorkflow project compiled the assembly, typically "%HOMEPATH%\My Documents\Visual Studio 2005\Projects\SharePointApprovalWorkflow\SharePointApprovalWorkflow\bin\Debug", then select and open the SharePointApprovalWorkflow.dll assembly.
  98. In the right pane, locate the SharePointApprovalWorkflow assembly, then write and store the Public Key Token, as you will need this value later (you can copy it by double-clicking on it, then selecting the text from the dialog box and copying it to the clipboard).
  99. Close the MMC.

Back to TOC

Deploying the VS.NET workflow and InfoPath forms to SharePoint Server 2007

  1. In the VS.NET 2005 workflow project, click on Tools from the menu bar, then click on Code Snippets Manager.
  2. Click on the Add button.
  3. In the Language drop-down list, select XML.
  4. Navigate to and Open the following folder: "%ProgramFiles%\Microsoft Visual Studio 8\Xml\1033\Snippets\SharePoint Server Workflow"
  5. Click on OK.
  6. Creating the Feature.xml file.

  7. Right-click on the SharePointApprovalWorkflow project, navigate to Add, then click on New Item.
  8. Select XML File, then call the file name "Feature.xml" (without quotes).
  9. Click on Add.
  10. In the code editor, place the cursor directly beneath the first line, the XML declaration line.
  11. Press Ctrl-K-X.
  12. Double-click on "SharePoint Server Workflow".
  13. Double-click on Feature.xml Code.
  14. Remove the ElementFile element/node from the XML file.
  15. Generate a random GUID from a website such as this one: http://www.famkruithof.net/uuid/uuidgen.
  16. Copy and paste the generated GUID into the ID property, replacing the GUID value currently present.
  17. Change the Title from "Default Title" to "Custom Approval Workflow" (without quotes).
  18. Set the Description property to "Custom-designed approval workflow." (without quotes).
  19. Press Ctrl-Shift-S to save all of your changes.
  20. The Feature.xml file should look something like this:<!--xml version="1.0" encoding="utf-8" ?--> <Feature Id="7d943930-f3ad-11dc-95ff-0800200c9a66" Title="Custom Approval Workflow" Description="Custom-designed approval workflow." Version="12.0.0.0" Scope="Site" ReceiverAssembly="Microsoft.Office.Workflow.Feature, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" ReceiverClass="Microsoft.Office.Workflow.Feature.WorkflowFeatureReceiver" xmlns="http://schemas.microsoft.com/sharepoint/"> <ElementManifests> <ElementManifest Location="workflow.xml" /> </ElementManifests> <Properties> <Property Key="GloballyAvailable" Value="true" /> <!-- Value for RegisterForms key indicates the path to the forms relative to feature file location --> <!-- if you don't have forms, use *.xsn --> <Property Key="RegisterForms" Value="*.xsn" /> </Properties> </Feature>
  21. Creating the Workflow.xml file.

  22. Right-click on the SharePointApprovalWorkflow project, navigate to Add, then click on New Item.
  23. Select XML File, then call the file name "Feature.xml" (without quotes).
  24. Click on Add.
  25. In the code editor, place the cursor directly beneath the first line, the XML declaration line.
  26. Press Ctrl-K-X.
  27. Double-click on "SharePoint Server Workflow".
  28. Double-click on Workflow.xml Code.
  29. Set the Name property to "Custom Approval Workflow" (without quotes).
  30. Set the Description to "Custom-designed approval workflow." (without quotes).
  31. Generate a random GUID from a website such as this one: http://www.famkruithof.net/uuid/uuidgen.
  32. Copy and paste the generated GUID into the ID property, replacing the GUID value currently present.
  33. Set the CodeBehindClass property to "SharePointApprovalWorkflow.Workflow1" (without quotes).
  34. In the CodeBesideAssembly property, replace the publicKeyToken with the copied Public Key Token obtained via the GAC earlier.
  35. In the Association_FormURN element, replace the associationFormURN value with the URN of the ApprovalInitiation.xsn document. To get this a) Open up InfoPath and the ApprovalInitiation.xsn document, b) click on Open when you get the security notice, c) click on Tools, then Design this Form, d) click on File, then Properties, e) copy the contents of the ID field, f) then paste this value into the Association_FormURN element.
  36. In the Instantiation_FormURN element, replace the associationFormURN value with the URN used in the preceding step.
  37. In the Task0_FormURN element, replace the existing value with the URN of the ApprovalAction.xsn document. To get this a) Open up InfoPath and the ApprovalAction.xsn document, b) click on Open when you get the security notice, c) click on Tools, then Design this Form, d) click on File, then Properties, e) copy the contents of the ID field, f) then paste this value into the Task0_FormURN element.
  38. Comment/remark out both the Modification_GUID_FormURN and Modification_GUID_Name elements, as we will not be using them (once the initiation is complete, there's no going back in this example/workflow).
  39. Press Ctrl-Shift-S to save all of your changes.
  40. The Workflow.xml should look something like this:<!--xml version="1.0" encoding="utf-8" ?--> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <Workflow Name="Custom Approval Workflow" Description="Custom-designed approval workflow." Id="d5df1be0-f3ae-11dc-95ff-0800200c9a66" CodeBesideClass="SharePointApprovalWorkflow.Workflow1" CodeBesideAssembly="SharePointApprovalWorkflow, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3e823471b9dbc370" TaskListContentTypeId="0x01080100C9C9515DE4E24001905074F980F93160" AssociationUrl="_layouts/CstWrkflIP.aspx" InstantiationUrl="_layouts/IniWrkflIP.aspx" ModificationUrl="_layouts/ModWrkflIP.aspx" StatusUrl="_layouts/WrkStat.aspx"> <Categories/> <!-- Tags to specify InfoPath forms for the workflow; delete tags for forms that you do not have --> <MetaData> <Association_FormURN>urn:schemas-microsoft-com:office:infopath:ApprovalInitiation:-myXSD-2008-03-16T04-37-49</Association_FormURN> <Instantiation_FormURN>urn:schemas-microsoft-com:office:infopath:ApprovalInitiation:-myXSD-2008-03-16T04-37-49</Instantiation_FormURN> <Task0_FormURN>urn:schemas-microsoft-com:office:infopath:ApprovalAction:-myXSD-2008-03-16T05-02-49</Task0_FormURN> <!-- <Modification_GUID_FormURN>modificationURN</Modification_GUID_FormURN> <Modification_GUID_Name>Name of Modification</Modification_GUID_Name> --> <AssociateOnActivation>false</AssociateOnActivation> </MetaData> </Workflow> </Elements>
  41. Installing the workflow in SharePoint Server 2007.

  42. In Windows' Explorer, navigate to the following folder: "%ProgramFiles%\\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES"
  43. Create a new folder here called "CustomApprovalWorkflow" (without quotes).
  44. Copy and paste the following files into the CustomApprovalWorkflow folder: ApprovalAction.xsn, ApprovalInitiation.xsn, Feature.xml, and Workflow.xml
  45. Open up a Windows' Command Prompt.
  46. Type the following command: cd "%ProgramFiles%\Common Files\Microsoft Shared\web server extensions\12\BIN"
  47. Type the following command: stsadm -o installFeature -name CustomApprovalWorkflow -force
  48. Press Enter.
  49. You should've gotten the following message: Operation completed successfully.
  50. Type the following command: stsadm -o activateFeature -name CustomApprovalWorkflow -url http://URLToSharePointServer
  51. Ensure the URLToSharePointServer is replaced with the site URL of the SharePoint Server 2007 to where this workflow will be employed/used.
  52. Restart IIS by typing and entering "iisreset" (without quotes) from a Windows' Command Prompt.

Back to TOC

Using the Deployed Custom Workflow in SharePoint Server 2007

    Associating a workflow with a document library.

  1. Locate a document library to which you'd like to associate this workflow.
  2. Click on the Settings menu on the Toolbar of the list/library.
  3. Click the Document Library Settings.
  4. Click on Workflow settings.
  5. Select on "Custom Approval Workflow".
  6. Call the workflow "Custom Approval Workflow" (without quotes).
  7. Select "New task list" from the Task List drop-down menu.
  8. Click on Next.
  9. You're now at the association form, which is the same as the initiation form (the ApprovalInitiation.xsn form). What's entered here will determine what's set by default for all new workflows initiated for this library, so ensure you leave each field blank, except for the Comments area.
  10. In Comments, enter "Please approve this document." (without quotes).
  11. Click on "Send for Approva". This will not actually send the form for approval, just save the form's values for future workflows.
  12. Initiating a document workflow.

  13. Upload a test document to this library. We'll refer to this document as the "test document".
  14. In the library, locate the test document, and activate its drop-down menu.
  15. Click on Workflows from the drop-down menu.
  16. Click on "Custom Approval Workflow".
  17. In the Approver field, type in your full name, or the name of a person who has been added to SharePoint Server 2007 and is allowed to interact with workflows.
  18. In the Expires field, enter the date after today.
  19. In Comments, enter "Please approve this document." (without quotes).
  20. Click on "Send for Approval". The workflow status of this document should set to In Progress. If it's set to Error, please revisit the preceding steps to determine what you might've missed, and visit the section referring to fixing problems (below)
  21. Locate the test document in the library, then click on the In Progress link. This will allow you to view the status of this document's workflow.
  22. At this point, the document needs approval, so no task has been started, as it's set not to start automatically. So, under Tasks, click on the test document's link to start a task.
  23. This page/form is what the approver(s) should see in order to approve, reject, or revise a document. To approve this document, leave the Approval Status to Approve.
  24. In Comments, enter "Approved." (without quotes).
  25. Click on the Submit button. The workflow is now complete, as either Reject or Approve are the status values necessary to complete the workflow.

Back to TOC

If Problems Arise, and Making Changes

If changes need to be made to the VS.NET project.

Making changes to the workflow VS.NET 2005 project(s) involves re-adding the assembly to the GAC and restarting IIS.

  1. Make the changes in VS.NET, then recompile the project/solution.
  2. Open up the Microsoft .Net Framework 2.0 Configuration MMC.
  3. Under My Computer, click on "Manage the assemly cache".
  4. Click on "View List of Assemblies in the Assembly Cache".
  5. Locate the SharePointApprovalWorkflow assembly.
  6. Right-click on SharePointApprovalWorkflow, then click on Delete.
  7. Click on Yes.
  8. Right-click on Assembly Cache, then click on Add...
  9. Locate and open the SharePointApprovalWorkflow.dll assembly.
  10. Restart IIS by running "iisreset" (without quotes) from a command prompt.

If changes need to be made to the InfoPath form(s).

Making changes to the InfoPath form(s) involves changing the form(s)'s content(s), publishing and saving them, then uninstalling the reinstalling the workflow in SharePoint Server 2007. You do not need to restart IIS if you're just making changes to the InfoPath form(s).

  1. Open up the InfoPath form from the location where it was saved, not published. Note: the locations where InfoPath forms are saved and where they are published are two different locations altogether--NEVER modify a published InfoPath form. Instead, ALWAYS modify the original, saved form(s).
  2. Click on Open if you get a security notice warning.
  3. Click on Tools from the menu bar, then click on "Design this form". This will open the form in another instance of InfoPath. You can close the other, read-only instance.
  4. Make any changes then save the form.
  5. Click on File, from the menu bar, then click on Publish.
  6. Click on "To a network location...".
  7. Click on Next.
  8. Click on the Browse button, then locate and open/select the folder to which the form was published, if it's not already in the field.
  9. Click on OK.
  10. Click on Next.
  11. Click on Next, ensuring the user access path/field is empty.
  12. Click on Publish.
  13. Click on Close.
  14. Open up a Windows' Command Prompt.
  15. Type the following command: cd "%ProgramFiles%\Common Files\Microsoft Shared\web server extensions\12\BIN"
  16. Type the following command: stsadm -o uninstallFeature -name CustomApprovalWorkflow -force
  17. Press Enter.
  18. You should've gotten the following message: Operation completed successfully.
  19. Type the following command: stsadm -o installFeature -name CustomApprovalWorkflow -force
  20. Press Enter.
  21. You should've gotten the following message: Operation completed successfully.

Back to TOC

What's Missing from this Workflow?

This sample workflow is extremely basic and is missing several elements that you will probably want to develop in a real-world, enterprise environment. The following are just some:

  • E-mails: One thing that you might want to implement is the sending of e-mail messages upon changes made to the workflow. For example, upon a new workflow initiation, you'll probably want to notify the approver(s)/assignee(s) that an approval is awaiting him or her, and provide links to the appropriate content in the HTML-encoded message. I personally use the SPUtility.SendEmail() method for this.li>
  • Workflow History Logging: Another thing you'll probably want to implement is the tracking of what's being done and the status of the workflow at particular action points in the flow. For example, when an approver/assignee rejects or approves a document, you'll want to log the comments and status to the workflow's history. I personally use the Workflow's CreateHistoryEvent() method for this.

Back to TOC

Additional Information & Resources

Thursday, March 06, 2008

Understanding the SharePoint Server 2007 (MOSS) Content Structure/Architecture

A MOSS 2007 server is an ASP.NET 2.0 Web site. Pages are rendered via ASPX (ASP.NET Web Forms) files, typically. Each ASPX page is tied to a Master Page and a Page Layout. Master Pages dictate a consistent look throughout the SharePoint site, and Page Layouts dictate how the individual page looks. Master Pages and Page Layouts are stored in the Master Page and Page Layout Galleries. Each Page Layout is associated with a Content Type, which dictates what type of content a Page Layout will render. Three default, out-of-box Content Types exist: Page, Article Page, and Welcome Page. Each Content Type defines one or more Column Templates, which are columns, or fields, that store data pertaining to the type of content (per the Content Type). Column Templates define what type of controls are assigned to data colums (such as DropDownList, Label, Picture, or TextBox controls).

| ASPX Page |----------------> Master Page |----------------> Page Layout ---> Content Type --> Column Templates

Editing SharePoint Server 2007 (MOSS) ASPX Files Directly

You can directly edit the ASP.NET ASPX files used by SharePoint by opening them up either via Notepad, VS.NET, or any other text editor. The files are stored in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\12 (most are in the TEMPLATE folder).

Wednesday, March 05, 2008

Visual Source Safe's ANALYZE Tool

VSS 6 and later have a tool for verifying the VSS database: ANALYZE.EXE. It's locationed in the VSS DB's win32 directory. You can run ANALYZE.EXE from anywhere, but you have to pass it the path to the VSS DB. For example, from a command shell, run...

analyze -x -v3 \\myDFS.com\VSS\data

\\myDFS.com\VSS\ is an example path to a share to a VSS DB, and data is a VSS DB folder.

The preceding command will verify the DB and report any errors and/or inconsistencies.

To fix the DB, run the following...

analyze -f -v3 \\myDFS.com\VSS\data

Sunday, March 02, 2008

Differences Between MS SQL Server 2005 and Oracle Database 10g

  • Oracle Tablespaces & SQL Server Filegroups

    Tablespaces in Oracle are equivalent to SQL Server Filegroups. They each are logical groupings of physical data files used to store related database objects, such as tables, indexes, etc. In Oracle 10g, two tablespaces are created when creating a database, by default: SYSTEM and SYSAUX. In SQL Server, one filegroup is created when creating a database, by default: PRIMARY. In both RDBMSes, you can create user-defined tablespaces and filegroups, in addition the the preceding mentioned.

    In Oracle 10g, you have the added benefit of being able to mark a tablespace as temporary, allowing tables added to be managed on a per-user-basis. For example, data added to a table in a temporary tablespace will only be modifiable by that user and that user only. There are other tablespace features, such as making them dictionary or locally managed.

  • Oracle Blocks, Extents, and Segments & SQL Server Pages & Extents