SQL Server 2008 – AdventureWorksDW BDC Demo

After installing MOSS 2007 on Visual Studio 2008, I wanted to see if I could get the BDC working against the new version of the AdventureWorksDW database for SQL Server 2008. 

I used the AdventureWorks2005.xml application definition file that ships with the MOSS 2007 SDK to create a connection to the SQL Server 2008 AdventureWorksDW database.  BDC’s can be very temperamental at times, so I was expecting some hiccups along the way.  As it turns out it, it was pretty painless and worked without a hitch. 

After I imported the BDC application definition, I setup a little BDC demo to test things out. 

To try this out, you’ll need MOSS 2007 and SQL Server 2008 installed and configured.  MOSS 2007 will run seamlessly on SQL Server 2008 as long as MOSS 2007 SP1 is installed on your WFE servers.

1. Install the AdventureWorks Sample DW BI Database

First, you need to install the AdventureWorks Sample DW BI database.  You can download it from CodePlex. 

I downloaded the full version for 2008 with the msi installer, which is nice because it installs the database and attaches it for you. Running the installation is pretty straight-forward, but if you have any trouble, you can refer to an earlier post on my blog here.

2. Install the Office SharePoint Server 2007 SDK (1.3)

Download and install the Office SharePoint Server 2007 SDK.  I’m working against the 1.3 version of the SDK.  The SDK contains a BDC application definition that were created for the SQL Server 2005 AdventureWorksDW  database.

3. Modify the AdventureWords2005DW.xml file

Before we can import the file, we need to make some minor modifications:

1. Open the AdventureWorks2005DW.xml file using your favorite xml editor.  The file is, located in the C:\Program Files\2007 Office System Developer Resources\Samples\Business Data Catalog\AdventureWorks Samples folder.
2. Modify the RdbConnection Data Source property under the LOBSystemInstance and to reflect your server name
3. Modify the RdbConnection Initial Catalog property to reflect your database name (AdventureWorksDW2008 by default)
4. Do a Save As to rename the file and save your changes

4. Import the BDC Application Definition

We’re now ready to import the application definition file to the MOSS 2007 to create a Business Data Connector (BDC).

1. Open the MOSS 2007 Central Administration web site
2. Click your SSP link under Shared Services Administration to open the SSP Administration site
3. Click the Import application definition link under the Business Data Catalog heading
4. Browse to the modified version of the application definition file (I called mine AdventureWorks2008DW.xml
5. Leave the default settings of Model with Localized Names and Properties checked
6. The installation should import the application file.  When it’s complete, click the OK button to view the Application information for the BDC

 

5. Setup a BDC Demo Page

To test the BDC and setup a nice demo, add 4 web parts onto a page on your site.

1. Add a Business Data List web part wired to Product Categories
2. Add a Business Data Related List web part wired to the Product Subcategory list on the ProductCategoryToProductSubcategory relationship defined in the BDC
3. Add a Business Data Related List web part wired to the Product list, filtered by the ProductSubcategoryToProduct relationship
4. Business Data Detail web part, with a connection to the Product List web part
5. Click the Edit View button on the list web parts and modify the views to hide the ID columns and turn off filtering for the Product Category list
6. Modify the titles on each of the the web parts
7. Try the web parts out and drag them around the page till you get them the way you like them

Now you’ve got a nice BDC demo that looks something like this the screen shot below.  Nice!

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

SQL Server 2008 – Installing the AdventureWorks Sample Databases

About the Databases

There are several versions of the AdventureWorks databases out there.  There are versions for SQL Server 2000, 2005 and 2008, but this post is concentrated on the 2008 versions.  For SQL Server 2008, there are 3 databases.

• AdventureWorks OLTP – A standard online transaction processing database containing a year of data of data for a fictitious bicycle manufacturer (Adventure Works Cycles). This database is useful for demo scenarios, and includes data for Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources functions.
• AdventureWorks DW -  A data warehouse database containing a year of data in a star schema, containing multiple fact and dimension tables.  This database is excellent for constructing SQL Server Analysis Service (SSAS) OLAP cubes and demonstrating the Business Intelligence (BI) capabilities of SQL Server and BI client technologies like MOSS 2007, Reporting Services, and Excel.
• The AdventureWorks LT (Light) - A highly simplified and smaller sample database which is helpful for those who are new to relational database technology.

Preparing for the Installation

Before attempting the installation, ensure the following services:

Full Text Search
Ensure you have Full-text Filter Daemon Launcher Service running. NOTE: You may receive an error stating that FILESTREAM is not enabled during the install if this service is disabled, which is a bit misleading.

FILESTREAM
If you did not enable FILESTREAM services during the SQL Server 2008 install, you’ll need to light it up prior to installing the sample AdventureWorks OLTP and DW databases.  If you don’t, the AdventureWorks database installation will fail trying to restore the database, and will warn you that FILESTREAM needs to be enabled. To enable FILESTREAM services on SQL Server 2008, see my post, How To Enable FILESTREAM Feature On SQL Server 2008.

Installing the Sample Databases

1. Download the msi installer for SQL Server 2008 AdventureWorks databases you want to install from CodePlex
2. Ensure you have Full-text Filter Daemon Launcher Service running
3. Ensure you have FILESTREAM services enabled on your SQL Server 2008 server (see notes above)
4. Run the msi installer and click through the intro and license screens
5. Turn on the restore database options during the install, as shown below.  This option will automatically restore the databases, but it is turned off by default. 
6. Select the database server and click Next to complete the installation
7. Repeat steps 1 through 6 to install the DW BI and/or LT databases databases
8. If you did not turn on the restore database options (step 5), you will need to attache the database manually after the install is complete.  You can attach them by running database restore SQL scripts located in C:\Program Files\Microsoft SQL Server\100\Tools\Samples\.

Notes From the Installation Wizard

The following notes are displayed in the wizard when you attempt to restore the database:

Select the name of the local instance to restore to from the dropdown list provided.

Note: The AdventureWorks databases will not be automatically removed during uninstallation.

The AdventureWorks databases can be automatically installed ONLY to a local instance of Microsoft™ SQL Server™ 2008, and both Full-Text Search and FILESTREAM must be installed and enabled if the AdventureWorks2008 OLTP database is included.

If required, the selected instance will have FILESTREAM automatically enabled, and it will be restarted automatically as necessary.

Warning: Both FILESTREAM and Full-Text Search must be installed and enabled for the AdventureWorks2008 OLTP database to be installed successfully.

If you choose to install the AdventureWorks databases automatically (as determined by the selected features of this installer package), the data files for the AdventureWorks databases will be created in the same directory as the data file (MDF) for the master database.

The AdventureWorks databases can be restored to another server by copying the BAK file to that server and restoring the database there with SQL Server Management Studio or the included T-SQL scripts.

For more information visit: http://go.microsoft.com/fwlink/?LinkId=87843

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

How To Enable FILESTREAM Feature On SQL Server 2008

FILESTREAM is a new feature of SQL Server 2008 that supports BLOB file storage on NTFS file shares managed by SQL Server.  The FILESTREAM feature is disabled during the default SQL Server 2008 installation.

To enable FILESTREAM feature on SQL Server 2008:

1. Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager)
2. Navigate to the SQL Server Services node and select the SQL Server instance you want to modify SQL Server (MSSQLSERVER)
3. Click the FILESTREAM tab and select the checkboxes to enable FILESTREAM and enter a share name for the files, as shown

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

SQL Server 2008 with SharePoint 2007 – Enable Remote SQL Connections

Installing SharePoint 2007 (WSS or MOSS) to use a SQL Server 2008 database is straight forward.  Other than differences in the initial SQL Server 2008 setup routine, there are very few differences to a SQL Server 2005 installation. 

One of the differences that I found involves enabling remote SQL connections.  For SharePoint 2007 to work properly, remote connections should be enabled over Named Pipes and TCP/IP.  Remote connections are disabled by default in both SQL Server 2005 and SQL Server 2008.

Enabling Remote Connections in SQL Server 2005

In SQL Server 2005, you enable remote connections by configuring SQL Server using the SQL Server Surface Configuration Tool, as shown below:

1. Open the SQL Server Surface Area Configuration Tool (Start > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
2. Click the link titled Surface Area Configuration for Services and Connections
3. Select the Database Engine > Remote Connections node in the tree view
4. Turn on the option for Local and remote connections > Using both TCP/IP and named pipes.
5. Click the OK or Apply button (you’ll be warned that these settings will not take effect until the Database Engine is restarted)
6. Restart the Database Engine (SQL Server Service)

 

Enabling Remote Connections in SQL Server 2008

In SQL Server 2008, the SQL Server Surface Configuration Tool is no longer part of the product.  It has been replace with the SQL Server Configuration Manager.  I personally find the old version to be more user-friendly, but either way, it gets the job done.  To enable remote connections in SQL Server 2008:

1. Open the SQL Server Configuration Manager (Start > SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager
2. Navigate to the SQL Server Network Configuration > Protocols for MSSQLSERVER node in the tree view
3. Enable TCP/IP and Named Pipes (you’ll be warned that these changes will not apply until you the service is shut down)
4. Restart the SQL Server Service

 

SharePoint 2007 Service Pack 1 Required

Please note that to run SharePoint 2007 on Windows Server 2008 or SQL Server 2008, you must have Windows SharePoint Services 3.0 Service Pack 1 and Microsoft Office Servers Service Pack 1 (if you’re running MOSS) installed on your SharePoint WFE server.

Related Links

• SQL Server 2008 Support for SharePoint Products and Technologies
• Installing MOSS 2007 on Windows Server 2008 and SQL Server 2008
• Upgrading SharePoint SQL Servers to SQL Server 2008

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

Attach a Custom XML Schema to a Word Document Programmatically

If you find the need to add a custom XML schema to a Word document or other Office document programmatically, this function might come in handy.  This function uses the Word InterOp assemblies to attach a schema to a document.

using System;
using System.Xml;
using System.Xml.Schema;

using Word = Microsoft.Office.Interop.Word;
using Office = Microsoft.Office.Core;
...

/// <summary>
/// Attaches a custom xml schema to a document
/// </summary>
/// <param name="document">The Word document to modify</param>
/// <param name="schemaUrl">The url to the schema</param>
/// <param name="schemaNamespaceUri">The schema namespace defined in the schema</param>
/// <param name="schemaAlias">The alias used for the schema</param>
public static void AttachCustomXmlSchema(Word.Document document, string schemaUrl,
            string schemaNamespaceUri, string schemaAlias)
{
    try
    {
        // Objects to use when passing strings as ref to word
        object schemaNamespaceUriObj = schemaNamespaceUri;
        object schemaAliasObj = schemaAlias;

        // Find the custom xml namespace (if we have one declared)
        Word.XMLNamespace customXmlNamespace = null;
        try
        {
            // Find the custom xml namespace
            customXmlNamespace = document.Application.XMLNamespaces.get_Item(ref
                        schemaNamespaceUriObj);
        }
        catch { };

        // Did we find it the namespace?
        if (customXmlNamespace == null)
        {
            // We didn't find it
            // Add the custom xml schema to the available namespaces
            customXmlNamespace = document.Application.XMLNamespaces.Add(schemaUrl,
                        ref schemaNamespaceUriObj, ref schemaAliasObj, true);
        }

        // Attach the custom xml schema to the document
        object documentObj = document;
        customXmlNamespace.AttachToDocument(ref documentObj);
    }
    catch (Exception ex)
    {
        string attachFailed = string.Format("Unable to attach the {0} xml schema",
                schemaAlias);
        throw new ApplicationException(attachFailed);
    }
}

 

Share This Post

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

XAML Soup – XBAP vs. Silverlight 1.0 vs. Silverlight 2.0

Microsoft offers several new technologies to provide Rich Internet Applications (RIAs) to end users using WPF technology.  Silverlight 2 gets the most press as Microsoft’s “Flash Killer”, but it’s good to understand how these technologies relate, and the differences between them.

• WPF Browser Applications (XBAPs)
• Silverlight 1.0 (formerly dubbed WPF/E, Released)
• Silverlight 2.0 (formerly dubbed Silverlight 1.1, currently in Beta 2)

All 3 technologies allow you to build dynamic user interfaces for the browser using the Windows Presentaion Foundation (WPF) using a browser plug-in.  You can use XAML to build front end UI elements, including some way cool transitions, blends, and animation.  You use code-behind to code against the UI and to add functionality to wire up the controls and respond to events, similar to the way you would in ASP.Net.

The primary difference between the three technologies is:

• Browser / OS support
• .Net Framework suppport
• Language support

XAML Advantages Over Flash

Because these technologies are XAML, the markup is XML content, not binary like Flash.  This is a subtle but MAJOR difference:

• XAML pages are indexable by search engines today.
• XAML is accessible via the Document Object Model. This means that the same techniques you use in AJAX to update a particular part of the tree can be used to update a particular portion of a Silverlight application.  Document.GetElementById just got a lot more powerful.
• Most of the skills you’ve developed in using the .Net Framework and building WPF applications are transferable to Silverlight application development

XBAP vs. Silverlight

.NET 3.0 introduced the Windows Presentation Foundation (WPF) which allows developeres to create stunning user interfaces for desktop applications using managed code. If you still haven’t experienced the power of WPF check out the WPF applications here.

The first step toward bringing WPF to the web was XBAP – XAML Browser Application (XAML is eXtensible Application Markup Language). XBAPs allow you to run Rich Internet Applications that look and function like WPF desktop applications. These XBAPs run inside the Internet Explorer in a limited-trust sandbox to prevent applications from accessing resources on the local system.

A restriction on XBAPs is that they need .NET framework 3.0 or higher to be installed on the client machine to run, and they only run on Windows systems under IE.   If you’re not in a tightly controlled intranet environment where you can dictate IE on Windows with the 3.0 framework on every client, Silverlight is the way to go.

Silverlight 1.0

Silverlight (formerly dubbed WPF/E) extends the boundaries by removing the dependency on the .Net framework, and providing cross-platform, cross-browser support to WPF applications with a browser plug-in.  As of now, Silverlight supports a subset of XAML and .Net functionality.

Silverlight 1 provides a solid framework for building RIAs using XML Application Markup Language (XAML) and JavaScript. Using XAML elements available in version 1 along with some JavaScript code you can animate objects in creative ways, skew and resize objects and interact with data dynamically using AJAX technologies and Web Services.

Although Silverlight 1.0 is powerful, it is a 1.0 release, and it has some serious shortcomings. 

• Very limited control set.  Aside from a few very basic controls (Canvas, TextBlock, MediaElement, and basic shape controls) no user input or data display controls were included in version 1.  Control layout was also much too difficult. 
• No support for the .Net framework, C# or Visual Basic.  Code behinds mucst be coded in Javascript, which is a web developer skillset, which may not overlap with WPF developers and doesn’t integrate natively with the Visual Studio development and debugging experience. 
• Limited support for browsers and operating systems.  It’s currently supported with plug-ins for IE and FireFox on Windows.  Additional plug-in’s for  popular browsers on Linux and Mac platforms are currently in the works from 3rd party vendors.

Make Way For Silverlight 2.0

Silverlight 2.0 recently RTM’d, and moves the framework ahead nicely, with major improvements over the 2.0 release, including:

• Adds a pared-down version of the .Net Framework complete with a miniature CLR hosted by a browser plug-in
• Provides many new controls that can be used to capture user input and display collections of items
• Ability to write C# or VB.NET code that runs in the Internet Explorer, Firefox, and Safari browsers
• Includes networking features that allow data from local and remote services to be integrated into applications

Getting Started

To get started, you should install Visual Studio 2008 and Silverlight Tools for Visual Studio.  For more information, see the Getting Started page on the Silverlight site.

Sources

• Light Up the Web: The Official Silverlight Site
• “WPF/E” is Silverlight
• XBAP and Silverlight
• Dr. Dobbs: What’s New In Silverlight 2?

Share This Post

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

 

What is OBA anyway?

OBA (Office Business Applications) is using Microsoft Office products and related applications to put your applications in the hands of users where they spend most of their time every day, in Office applications.  It’s a ubiquitous term, but at the heart of it, OBA solutions deliver information and functionality from a variety of systems to the user where they need it most.

OBA’s can expose data from custom applications and ERP systems, or even merged data from multiple systems, and deliver custom UI’s and custom automation interfaces available directly in Word, Excel, PowerPoint or Outlook.  You can use VSTO tools to build Office Add-Ins and templates that can inject data from these applications into their documents, or simply to display inside these applications as users work. 

With Visual Studio 2008 and VSTO, Microsoft has made it easy to surface your data inside the Office suite in a variety of ways.  The flexibility is stunning, and will spin your head a bit.  The new toolset, and the integration of Office and Visual Studio, Microsoft has given us the power to build some amazing applications.

When you build OBA’s, you’ll be leveraging the following tools:

Microsoft Office SharePoint Server 2007 (MOSS)

• Web Site Provisioning
• Custom Lists and Document Libraries
• Content Types
• Business Data Catalog
• Forms Server
• Excel Services
• BI Dashboards and KPI’s
• Custom Features and Solutions
  • Custom Content Types
  • Custom Lists
  • Workflow
  • Event Receivers
  • Custom web parts

Office 2007

• Document Information Panels
• Custom Ribbon Add-In’s
• Custom Task and Action Panes
• Custom Add-Ins
• Code-Behind Templates
• Custom XML Parts
• Word Custom Controls
• Excel List Databinding
• Excel User-Defined Functions

Visual Studio 2008 and the .Net Framework

• SharePoint Project Types
• Office Project Types
• Integrated Debugging and UI Support
• Click-Once Deployment

Here’s a list of resources to get you going

• Office Business Applications Developer Portal
• The OBA Product Team Blog
• MSDN OBA Architecture Portal
• Patrick Tisseghem’s Blog

Transitioning to SharePoint 2007 (MOSS) Development

My background is in ASP.Net C# development.  I recently transitioned to SharePoint 2007 (MOSS) and OBA (Office Business Application) development.  As I learn, I hope to share my experiences here.

MOSS + Office + Visual Studio is a powerful toolkit – a platform for building productivity applications that goes way beyond building portals.  Throw in some Reporting Services, InfoPath, BI, AJAX, and SilverLight and things really get interesting.  It’s also a huge space, with a steep learning curve. I’ve learned a few things along the way, and I hope to find time to document some of them here to help others that want to make the transition or learn more about MOSS and OBA.

To get started, I recommend the following books, to be followed in the order listed.  Read them, build out your own lab environment, do the labs, absorb as much as possible, and you’ll be on your way.  It’s an exciting space, with much more to come.

Microsoft SharePoint: Building Office 2007 Solutions in C# 2005 (Expert’s Voice in Sharepoint) by Scot P. Hillier
An excellent overview of the functionality and capabilities of SharePoint 2007, WSS and MOSS. If you’re getting your feet wet with MOSS development, this is a great place to start. Includes complete labs that walk you through setting up a networked MOSS environment using VPC’s. My copy is heavily abused, I refer to it often.

Workflow in the 2007 Microsoft Office System by David Mann
A great reference for MOSS Workflow development. This is the only WF book I’ve found that covers custom MOSS workflow in-depth. If you’re doing your first custom MOSS workflow in Visual Studio, this book will save you lots of time and frustration.

Pro SharePoint Solution Development: Combining .NET, SharePoint and Office 2007 (Expert’s Voice in Sharepoint) by Ed Hild
Covers a bare minimum of MOSS basics and dives deep into custom Office solution development with real-world samples projects building OBA’s leveraging MOSS, Word, Excel, PowerPoint and Outlook. Covers custom feature development including content types, custom lists, custom actions and event receivers, BDC’s, Workflow, and Open Xml and document packaging code samples. Lots of useful code goodies.

Microsoft® Office SharePoint® Server 2007 Administrator’s Companion by Bill English
This book is a great reference book for SharePoint Administrative tasks and guidelines. Makes for a very boring read, but is an excellent reference for SharePoint infrastructure guidelines.