Yaroslav and I did a presentation at VANTUG on November 22, 2007 (Thanks to Graham Jones, President of VANTUG, for inviting us to speak!). As promised, here are the presentation materials. These are the same materials we are going to use for DEVTEACH on November 28, 2007.
As we mentioned in the presentation, we used a real business case as a basis for the demo. The solution in our project involved:
– Sharepoint as portal/framework (specifically MOSS)
– WCF for communication between Sharepoint and backend databases
– Custom Web Part for entry forms and client-side validation
– BDC for search and data display
– SSNS for notification and audit trails
We tried to create a simpler version that uses most of the features above, and highlights the flexibility of Sharepoint as a framework.
PRESENTATION FILE – Download the Powerpoint Presentations from here:
VANTUG November 2007 – Sharepoint Integration
Looking for additional references? You can check out the resource list for this presentation from my previous blog:
DevTeach/VANTUG Sharepoint 2007 Integration Presentation (Nov 2007): Resources
Screenshots: At A Glance
BDC |
SSRS |
SSNS |
Custom Web Part with SSNS |
Looking for additional references? You can check out the resource list for this presentation from my previous blog:
DevTeach/VANTUG Sharepoint 2007 Integration Presentation (Nov 2007): Resources
PRESENTATION FILE – ProductDetail
Database File (setup_productdetail.zip)
This contains the ProductDetail table, and related views and stored procedures. This .sql file is enough for you to get the BDC and SSRS part of the demo working.
We used the AdventureWorksLT database as our base database, and added a few tables/view/stored procedures.
This is the ProductDetail ERD:
1. Download the AdventureWorksLT database. You can download the AdventureWorksLT database from CodePlex (http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).
2. Download the SQL file for the presentation here.
3. Open up the setup_productdetail.sql file in SQL Server Management Studio.
4. Execute
PRESENTATION FILE – BDC:
BDC File (AdventureWorksProductDetailADF.zip)
This file contains the ProductDetail BDC Application Definition File
The BDC is a MOSS (Microsoft Office Sharepoint Server) only feature. This will not run on WSS.
1. If you don’t have access to MOSS, you can download the MOSS trial version from http://www.microsoft.com/downloads/details.aspx?FamilyId=2E6E5A9C-EBF6-4F7F-8467-F4DE6BD6B831&displaylang=en
2. Open Central Administration
3. Go to Shared Services
4. Under the Business Data Catalog section, click on Import Application Definition.
5. Browse to AdventureWorksProductDetailADF.xml, accept defaults and click on OK.
Some people asked during the presentation what I use to create the BDCs. Since I started working on the BDC since MOSS’s B2TR, I’ve grown accustomed to writing the ADF by hand. I did try the BDC Metaman at that time – which was then still a beta (and free!) version developed by Todd Baginski.
To debug BDC’s to date, however, you have 2 indispensible friends:
1. Event Viewer
2. 12 Hive LOGS folder (C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12LOGS). If you can’t see any relevant logs, check Central Admin and get MOSS to log “Business Data”.
3. When updating the BDC:
– you will need to manually update the Version in the ADF
– you may need to disable caching (from Web Part properties)
– at times, you may need to delete the BDC Application Instance and recreate
(Not sure exactly when these things happen, but this will be one my next discovery quests!)
4. Last but not least, make sure you check the permissions. Whoever’s using the BDC needs to be added to the BDC permissions list with EXECUTE permissions.
PRESENTATION FILE – Sample SSRS Project:
Sample Report Project on ProductDetail table
The bulk of the work in this section is not about creating the reports, it is configuring the environment.
I’ve used three main resources, which I reference in a previous blog (DevTeach/VANTUG Sharepoint 2007 Integration Presentation (Nov 2007): Resources)
Raju Sakthivel. Microsoft SQL Server Reporting Services – Installation and Configuration Guide for SharePoint Integration Mode (very good resource, and a must read if you are planning to deploy SSRS Integrated Mode)
http://blogs.msdn.com/sharepoint/attachment/4194088.ashx
How to: Configure SharePoint Integration on Multiple Servers
http://technet.microsoft.com/en-us/library/bb677365.aspx
How to: Configure SharePoint Integration on a Standalone Server
http://technet.microsoft.com/en-us/library/bb677368.aspx
SSRS Integrated Mode introduces 3 new content types to Sharepoint: Report Builder Model, Report Builder Report and Report Data Source.
Points to note before you pull your hair out:
– You must get Kerberos working. Make sure your SPNs are in place.
– You must install SQL Server 2005 SP2.
– Don’t look for Report Manager. It’s not there. Sharepoint Integrated mode does away with the Report Manager. Other unsupported features are documented here: http://technet.microsoft.com/en-us/library/bb326290.aspx
– When deploying, you need to provide the URL to the document libraries and URL to the site you are deploying to. This could be easy to miss especially if you are used to the Report Manager. To check this:
– Open your Project in Visual Studio
– Right click on your project from the Object Explorer > Properties
– Make sure your
PRESENTATION FILE – Basic Custom Web Part:
Yaro-Softlanding.SampleWebpart.zip
These are the files provided by Yaroslav for creating your basic hello world custom web part:
PRESENTATION FILE – SSNS:
SSNS File (SSNS – NSProduct2.zip)
This file contains the ADF, ICF, XSL, schemas, sample events you need to get started on Notification Services.
Microsoft has provided a number of good step-by-step tutorials. You can start with:
MSDN. SQL Server 2005 Notification Services Tutorial
http://msdn2.microsoft.com/en-us/library/ms170337.aspx
Here’s a quick start guide:
1. If you don’t have Notification Services installed, install notification service:
a. Run the SQL Server 2005 Setup Disk
b. Choose to add components.
1. Create your ADF file.
2. Create your ICF file.
3. Create a new SQL Server Notification Services instance and enable
4. Register the Notification Service you created as a Windows Service
5. Assign permissions to an Event folder if you are using a FileWatcher
6. Start the service
7. Add subscribers
8. Test, test, test
If you are debugging:
1. Check the NotificationDistribution table in the generated [InstanceName][ApplicationName] database. This should tell you if your notifications were successfully delivered or not.
2. Check the Event Viewer
3. Check the SQL Logs
Other things worth checking:
1. throttle
2. illegal XML characters
Oh, and if you want to update your CSS/HTML/XSL file, you need to update your notification instance. It’s not just going to pick up the style or content changes.
UNFORTUNATELY, to my dismay, the July CTP of Katmai’s readme file announces very silently that Notification Services days are over (or at least that’s what it sounds like). I liked working with Notification Services 
“SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.”
- Tweet
Related posts:
- DevTeach/VANTUG Sharepoint 2007 Integration Presentation (Nov 2007): Resources …
- VANTUG (Nov 2007) Sharepoint 2007 Integration Presentation …
- DevTeach/VANTUG Presentation: Resources …
- Thank you to DevTeach/VANTUG attendees! …
- Branding – Sharepoint 2007 – CSS Reference …
- Victoria Code Camp Materials …
