Category Archives: SQL Server

Virtual PC / SQL Server – Restore Database Failure – Media Family Incorrectly Formed

I ran into a problem with SQL Server restore operations recently.  I got burned for quite a while, because I thought I had a problem with my SQL Server 2000 and SQL Server 2005 installation, a SQL Server or O/S version issue, or a problem with my database backup file.  Small SQL backup files restore fine, but large backups fail on the restore on VPC images

When I restored the large backup files, ISQL RESTORE DATABASE commands fail with the following symptoms:

  • Msg 3241, Level 16, State 37, Line 1 The media family on device ‘C:\Temp\MyLargeDatabase.bak’ is incorrectly formed. SQL Server cannot process this media family.
  • The media set shows a Backup Set Name of *** INCOMPLETE ***
  • RESTORE HEADERONLY FROM DISK=’C:\Temp\MyLargeDatabase.bak’ returns BackupName of *** INCOMPLETE ***, Position = 1, the rest of the fields are NULL

The Problem

Virtual PC Shared Folders corrupt files!  Especially large files.  Small files seem to work fine.  I’ve heard reports of problems with files over 1GB to 4GB in size.  My database was around 8GB.

You get the errors shown above when you reference a file across a VPC Shared Folder, or when you create a local VPC file copy by copying a file to your VPC disk over a Shared Folder connection.

The Solution

Copy or reference files using a traditional networking file share between the host and guest, and avoid using Shared Folders for large files (or altogether).

Troubleshooting

If you think you may be having a similar issue, you can use a tool like SlavaSoft’s fsum to perform checksum comparisons file on the host and guest systems to verify what’s going on.  Or you can take my word for it and save yourself the trouble.  I’ve experienced this (the hard way) with Virtual PC 2007.

  del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

Advertisements

SQL Server Reporting Services – Url Parameters in SharePoint Integrated Mode

SQL Server Reporting Services 2005 and 2008 added a new SharePoint Integration mode, which allows reports to be stored and managed in a SharePoint 2007 portal.  Data connection, reports, security, report subscriptions and notifications are all managed in SharePoint 2007. 

Advantages Of SharePoint Integrated Mode

In SharePoint Integration mode, reports can be rendered using Reporting Services web parts.  These web parts can be added to pages throughout your site.  You can link report web parts to filter web parts, to provide parameters to one or more reports on a page, which allows you to create Reporting Services dashboards.  You can even mix and match them with Excel Services charts, spreadsheets, and pivot tables on a single dashboard page.  Powerful stuff!!

RSViewerPage and the Report Viewer Web Part

When you configure Reporting Services to run in SharePoint Integration Mode, reports are displayed using the /_layouts/ReportServer/RSViewerPage.aspx page by default.  This page provides an a SharePoint look and feel, and integrated navigation back to your SharePoint site.  It leverages the Report Viewer Web Part to display the report.  The Report Viewer Web Part has some very nice features:

  • An integrated SharePoint look/feel
  • Cookie crumb navigation back to your site, MySite, and MyLinks
  • A collapsible parameter panel to the right of the report
  • A nice toolbar with zoom and page navigation
  • An Actions menu on the toolbar, allowing users to:
    • Export to various formats
    • Customize the report using Report Builder
    • Create report subscriptions

SSRSReportViewerWebPart

HTML Viewer in Native Mode and SharePoint Integrated Mode

When you configure Reporting Service to run in Native Mode, reports are displayed using an HTML Viewer interface that is built into Reporting Services.  The HTML Viewer provides all of the basic functionality users required to view, navigate, and print reports. It also provides many Url parameter options to control report parameters and rendering rendering – options that are not available when viewing reports using the RSViewerPage page and the Report Viewer Web Part.

Yes, You Can Use the HTML Viewer in SharePoint Integrated Mode
When you configure Reporting Services to run in SharePoint Integrated Mode, it’s your choice.  You can use either the RSViewerPage or the HTMLViewer to view your reports.  The look and feel of the HTML Viewer isn’t as nice as the RSViewerPage interface, but it’s equally functional, and it provides a lot of Url options that can’t be duplicated using the RSViewerPage.

SSRSHTMLtViewer

In the HTML Viewer, the report parameter options are displayed at the top of the page (the Report Viewer Web Part shows them on the right-hand side of the report).  You can use the HTML Viewer user interface to view the report, change parameters, collapse parameters, zoom, print, export, etc.  When you use the HTML Viewer to view your report, you lose the SharePoint UI look and feel, cookie crumb navigation, MySite and MyLinks integration, and Report Builder and Subscriptions integration.  You gain the Url options we’ll be discussing later in this post – options that are not supported by the RSViewerPage or the Report Viewer Web Part.

How To Display Reports Using the HTML Viewer Page

You still have the ability to call reports using the HTML Viewer page by providing a link to the ReportServer Virtual Directory with a full path to the rdl file, like so…

http://myrsserver/reportserver?http://portal/reports/reportslibrary/regional%20sales.rdl&rs:Command=Render

When you view your report in this manner, you are bypassing the SharePoint Integrated RSViewerPage.aspx page and the web part, and viewing the report using the native mode HTML Viewer.  The rs:Command=Render Url parameter instructs reporting services to display the report. 

Passing Report Parameters in the Url

When you provide a link to the HTML Viewer, you can use the parameter support in the HTML Viewer to control the display of the report.  Assume a report that accepts 3 parameters:

  • Region (integer)
  • StartDate (date)
  • EndDate

By adding Url parameters on the end of the HTML Viewer Url, you can easily pre-populate the report parameters for the user.

http://myrsserver/reportserver?http://portal/reports/sample%20reports/departmental%20sales.rdl&rs:Command=Render&region=2&startdate=9/16/2008&enddate=9/17/2008

As you can see, with this sort of report, you can provide a flexible report that can be used for multiple purposes.  You can easily swap out these links in code to insert the department and dates appropriate in the context of the application displaying the links.  Parameter values in the Url override default parameter values.

If all the parameters are supplied by Url or default parameter settings, the report is rendered automatically, without any further action required from the user.  Nice!!

Passing Report Rendering Instructions in the Url

Using the HTML Viewer, you can also control the rendering of the report.   You can set the rendering format (HTML, PDF, EXCEL, CSV, etc.), the zoom level, and a bookmark or default page displayed in the report. You can also toggle the appearance of toolbars, parameters, and the document map.  You can even provide default search criteria to display and modify the style sheet used to render the report, or set the report to display a specific page.

As an example, the following link will show the report at Whole Page zoom level, with parameters collapsed and document map displayed, with the last page displayed by default.

http://myrsserver/reportserver?http://portal/reports/sample%20reports/departmental%20sales.rdl&rs:Command=Render&rc:Zoom=Whole%20Page&rc:Parameters=collapsed&rc:DocMap=true&rc:Section=999

In the HTML Viewer, there are many options available that can be used to controlling report rendering.  This is a short list of the of the ones you’re likely to find useful:

  • rs:Format – Rendering modes you can pass are HTML3.2, HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, defaults to HTML4.0
  • rc:Zoom – Specified in percentages, supports Page%20Width and Whole%20Page, defaults to 100%
  • rc:Toolbar – True/False, used to show/hide the toolbar, defaults to true
  • rc:Parameters – True/False/Collapesed, used to show/hide/collapse the parameters in the toolbar, defaults to true
  • rc:DocMap – True/False, used to show/hide document map, defaults to true (not shown unless report has document map)
  • rc:Section – Specifies default page number to display, defaults to 1
  • rc:BookMarkID – Jumps to a specific bookmark in a report
  • rc:FindString – Provides search criteria to the report and finds the first instance of the string specified

Links For More Information

For more information on Url parameters available for the Reporting Services HTML Viewer and Report Viewer Web Part, check out these links:

  del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

SQL Server 2008 – How To Build and Deploy AdventureWorks OLAP Cubes

I’m trying to setup a simple OLAP demo, using MOSS 2007 Excel Services to display pivot tables and other spreadsheet data, connecting to SQL Server 2008 Analysis Services OLAP cubes.  In this case, I want to get it working with the AdventureWorksDW sample database.  I’m documenting the steps here to help others, and so that I can do it again without the pain!

To complete these steps, you will need SQL Server 2008 installed with the database engine, Analysis Services, FILESTREAM and Full Text Search services enabled.  You’ll also need the Visual Studio Business Intelligence project templates installed to build and deploy your cube using SQL Server Business Intelligence Development Studio (BIDS).

1. Install the AdventureWorksDW Sample Database

First, you’ll need to install the AdventureWorkdsDW sample database from CodePlex.  To install it, you’ll need FILESTREAM and Full Text Search enabled.  For more detailed instructions, see my blog post, SQL Server 2008 – Installing the AdventureWorks Sample Databases.

2. Install and Deploy the AdventureWorks Cubes

After you’ve installed the AdventureWorks DW database, you need to setup a SQL Server Business Intelligence Developer Studio (BIDS) project to create and deploy a cube.  You can find the sample BIDS project located at C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project.  There are 2 projects there, depending on your version of SQL Server you’re using.

  1. Open the sample Adventure Works.sln BI solution using Visual Studio
  2. Change the data connection options for the source database
    1. Expand the solution folders in the Solution Explorer and double-click the Adventure Works.ds data source to open the properties.  This is a data connection to the AdventureWorksDW database, which will serve as the source database for the cubes we’re creating
    2. Edit the connection string and set the connection properties.  I just changed localhost to server name of my database server, using the default Windows Authentication mode.
    3. Click the Test Connection button to test your connection
    4. Exit the configuration dialog to save your changes
  3. Change the data connection options for the Analysis Services database
    1. Select Project > Properties from the Visual Studio menu to open the project options dialog
    2. Select the Deployment options
    3. Change the server to the database server / instance here Analysis Services is installed.  This is where your cube will be created.
    4. By default, the database name is Adventure Works DW 2008.  This is the Analysis Services database that will be created to host the cubes.
    5. Click OK to exit the properties dialog and save your changes
  4. Build the project
    1. Select Build > Build Solution from the Visual Studio menu to build the solution and check for errors.
    2. Select Build > Deploy Solution from the Visual Studio menu to deploy the project.  This will create the cubes and other Bi database objects in the database. 

3. Browse the AdventureWorks Cubes

After your cube has been deployed, you can browse your cube data in the Visual Studio BIDS interface, or in SQL Server Management Studio:

Browse cube data in Visual Studio

  1. Select the AdventureWorks cube in the cubes folder of your BIDS project
  2. Right-click the cube and select Browse from the context menu
  3. Drag/drop measure and dimension data to play with your cube data

Browsing OLAP Cube Using Visual Studio

Browse cube data in SQL Server Management Studio

  1. Open SQL Server Management Studio (Start > Programs > MIcrosoft SQL Server 2008 > SQL Server Management Studio)
  2. Connect to the Analysis Services database
    1. Select File > Connect Object Explorer from the SQL Server Management Studio menu
    2. Select Analysis Services in the Server Type dropdown
    3. Enter the server name and authentication options and click Connect
    4. Expand the Analysis Services server in the object explorer and browse to the Adventure Works DW 2008 > Cubes
    5. Select the AdventureWorks cube, right-click, and select Browse from the context menu
    6. Drag/drop measure and dimension data to play with your cube data, as show below

Browsing OLAP Cube Using SSMS

  del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

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

 MOSS 2007 AdventureWorksDW 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!

AdventureWorksDW BDC Web Part Demo Page

  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\.

Restore Database Options

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

Enabling FILESTREAM feature

  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)

 SQL Server 2005 Surface Area Configuration

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

 SQL Server 2008 Configuration Manager

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

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!