Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies

The Microsoft® SQL Server™ 2005 Reporting Services Add-in for SharePoint Technologies is a Web download that provides features for running a report server within a larger deployment of Microsoft Windows® SharePoint® Services 3.0 or Microsoft Office SharePoint Server 2007. The Reporting Services Add-in provides the following functionality:

  • A Report Viewer Web Part, which provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.
  • Web application pages so that you can create subscriptions and schedules, set model item security, and manage reports, models, and data sources.
  • Support for standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types. You can use alerts, versioning (check in/out), and Filter Web Parts with reports. You can add the Report Viewer Web Part to any page or dashboard on a SharePoint site and customize its appearance. You can use SharePoint permission levels and roles to control access to report server content. You can also use SharePoint forms authentication to support access over Internet connections.
  • Support for additional languages. The Web application pages, Report Builder, and ReportBuilder.chm are available in the standard SQL Server languages (Chinese Simplified, Chinese Traditional, English, French, German, Italian, Japanese, Korean, Russian, and Spanish), and the following additional languages: Czech, Danish, Dutch, Finnish, Greek, Hungarian, Norwegian (Bokmal), Polish, Portuguese (Portugal), Portuguese (Brazilian), Swedish, and Turkish.

The Reporting Services Add-in works together with updated program files that are in SQL Server 2005 Service Pack 2 (SP2). SP2 is required on the report server and provides the following functionality for a report server that is configured for SharePoint integrated mode:

  • Synchronization from SharePoint content databases to the report server database.
  • A custom security extension that uses SharePoint permissions to control access to report server operations.
  • A new delivery extension that you can use in subscriptions to deliver reports to SharePoint libraries.
  • A revised Reporting Services Configuration tool that you can use to configure a report server for SharePoint integrated operations.
  • Publish reports and models from Report Designer and Model Designer to a SharePoint library.
  • Open reports and models in Report Builder from a SharePoint library, and save reports to SharePoint libraries across the site.
  • A new SOAP endpoint for managing report server content in SharePoint integrated mode.

The add-in is available as a download from Microsoft.

Configuring and Customizing the Content Query Web Part

A co-worker of mine linked me to this wonderful post with tips and tricks on configuring and customizing the lovely content query web parts in MOSS.

Adding table headers and/or footers to a Content Query Web Part Layout

A good post about playing with the ItemStyle.xsl and ContentQueryMain.xsl files can be found here:

Creating a MOSS 2007 VPC

The best resource that I have found to help in creating a MOSS 2007 VPC is found at

Microsoft Certified Techincal Specialist: Microsoft Office SharePoint Server 2007 (Application Development) Exam

During March 2007, Microsoft released exam 70-542 (TS: Microsoft Office SharePoint Server 2007 – Application Development). For my professional development, my boss has asked me to complete this exam in order to become certified in MOSS 2007 development. After doing some research, I found that there are no Microsoft endorsed courses for this exam and only a single book that covers some of the material covered by the exam. So, I am going to attempt at putting together a series of posts to help me prepare for the exam.

 The topics covered by the exam include:

  1. Managing Enterprise Content
  2. Creating Business Intelligence Solutions by Using Office SharePoint Server 2007
  3. Integrating Business Data with Office SharePoint Server 2007 by Using the Business Data Catalog (BDC)
  4. Accessing Office SharePoint Server Application Platform Services
  5. Searching Data by Using the Search Service
  6. Targeting Content Based on Audience Membership
  7. Customizing Functionality by Using Profiles

The target audience for the exam are MOSS 2007 consultants and developers who have the following experience:

  1. Six months to one year of experience developing applications by using Microsoft Office SharePoint Server 2007
  2. Experience using the following related technologies
    1. The Microsoft .NET Framework (at least six months with ASP.NET 2.0)
    2. Windows SharePoint Services development (six months to one year)
  3. Experience working with XML documents
  4. Experience with XML Web services development and consumption
  5. Working knowledge of Windows WorkFlow Foundation
  6. Working knowledge of IIS 6

There a number of skills that are measured by the exam. In the near future, you will find links to posts that cover the measured skills. Keep your eyes peeled.

WSS Content Deployment Violation of Primary Key Constraint Error

Recently at a client’s site, we began experiencing consistent failures of an incremental content deployment job with the following error:

Violation of PRIMARY KEY constraint ‘PK__#ExportObjects____462064A9’. Cannot insert duplicate key in object ‘dbo.#ExportObjects’. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.SharePoint.Deployment.SPExport.CalculateObjectDependencies(Boolean linksOnly) at Microsoft.SharePoint.Deployment.SPExport.CalculateObjectsToExport() at Microsoft.SharePoint.Deployment.SPExport.Run()

According to this blog entry, this is caused when a single page is approved more than once between incremental updates. Microsoft has a hotfix for this issue that is available via a support call (see Microsoft KB Article 936867).

 I believe that a work around (without the hotfix mentioned above) is to perform a full content deployment job (instead of the incremental). I will be attempting this work around later this evening and will let you know of the results.


The work around that I mentioned earlier didn’t work. It only left me with the following failure during a full deployment:

Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). at System.Guid..ctor(String g) at Microsoft.SharePoint.Deployment.FieldTemplateSerializer.ParseLookups(SPField fieldTemplate, ExportObjectManager objectManager) at Microsoft.SharePoint.Deployment.FieldTemplateSerializer.GetDataFromObjectModel(Object obj, SerializationInfo info, StreamingContext context) at Microsoft.SharePoint.Deployment.DeploymentSerializationSurrogate.GetObjectData(Object obj, SerializationInfo info, StreamingContext context) at Microsoft.SharePoint.Deployment.XmlFormatter.SerializeObject(Object obj, ISerializationSurrogate surrogate, String elementName, Boolean bNeedEnvelope) at Microsoft.SharePoint.Deployment.XmlFormatter.Serialize(Stream serializationStream, Object topLevelObject) at Microsoft.SharePoint.Deployment.ObjectSerializer.Serialize(DeploymentObject deployObject, Stream serializationStream) at Microsoft.SharePoint.Deployment.SPExport.SerializeObjects() at Microsoft.SharePoint.Deployment.SPExport.Run()

I am not sure if the two errors are related to the same source. However, it is interesting to know that the incremental job fails even when testing the job and the full deployment passes when tested. In the end, both deployment approaches fail. Leading me to beleive that the only option is to obtain the hotfix from Microsoft.

SPFolder and Content Approval

Question:How do I approve a folder in a document library that requires content approval using the SharePoint object model?

Answer:It may seem like a trivial question. After working with the SharePoint object model to create and approve files, it would make sense that any item or folder object would also contain methods needed to check in, publish, and approve them. Well after checking the members of the SPFolder and SPListItem classes on MSDN, I found that they don’t contain properties or methods to intuitively check in, publish, and approve them.

At first, I figured I could use the approve method from SPFile class since the folder object has a property to access the underlying item which has a a property to access the underlying file. Well, it turns out that file property of the item property of the folder object returns a null reference. So, I was back to square one.

As it turns out, the solution to this problem lies in working with the underlying item object. The underlying item object has a property that allows you to access the moderation information object (SPModerationInformation) which contains information about the approval status of the folder. You can then check the status and change as it needed by manipulating the moderation information object directly. Below is a method that I wrote to solve my problem (which was just to approve the folder):

private void ApproveFolder(SPFolder spFolder, SPDocumentLibrary spDocumentLibrary)
  if (spFolder.Exists && spDocumentLibrary.EnableModeration)
    SPModerationInformation spModerationInformation = spFolder.Item.ModerationInformation;
    switch (spModerationInformation.Status)
      case SPModerationStatusType.Pending:
        spModerationInformation.Status = SPModerationStatusType.Approved;
        spModerationInformation.Comment = "Got approval?";
    spModerationInformation = null;

Obviously there is a lot more that you can do with the moderation information object. Hopefully, you will find my experience useful.