Freigeben über

Adventures in Installing the SQL Server AdventureWorks Sample Database

  • Artikel

I was thinking of writing an article about Microsoft SQL Server T-SQL Basics; however, in order to do the examples and demos I wanted to use a database that’s readily available on the Internet, so I decided to use the common AdventureWorks sample databaseavailable on Codeplex,  After downloading the database, it should have been an easy install. I’ve installed it hundreds of times with no issues, but this time it didn’t work!  Here’s my journey to figuring out a solution, hoping it will help some of you caught in similar circumstances.

After I agreed to the EULA, the installation abruptly stopped and I didn’t pay attention to error message. Maybe I should have, but then again if I did I wouldn’t have written this post. Anyhow, without trying to figure it out I decided to create the database manually by using the SQLCMD utility.

The AdventureWorks2008R2_SR1.exe file is self-extracting compressed file. I extracted the full contents of this file and got the following:

Contents of AdventureWorks2008R2_SR1.exe

I didn’t know the AdventureWorks database gets created from scratch every time. I remember in the SQL 2005 days it was just MDF/LDF files. If you go into any of these folders you’ll see a myriad of files, including CSV, PNG, Visio Diagrams, etc..

Different files in the AdventureWorks2008R2_SR1.exe archive.

The instawdb.sql file caught my eye. If you open it talks about using SQLCMD to create the database. So without any further delay I opened the command prompt and executed the following command from the directory where I had all these files extracted:.

sqlcmd -iinstawdb.sql -v SqlSamplesSourceDataPath="C:\Work\Tools\Sample Databases\AdventureWorks2008R2_SR1\" SqlSamplesDatabasePath="C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"

It didn’t work. An error came back stating that Filestream was disabled. Okay, no problem, open up SQL Server Configuration Manager and it should be easy fix.  But then I got another error:

SQL Server Configuration Manager Error

I have run into this error (i.e. 0×80041010) before when I had multiple versions of SQL Installed. However, this was not the case here.  Searching on Internet, it quickly became apparent it is fairly common issue and I ran into two useful articles: one by Eric Charran and another on Microsoft Connect.

I tried the fix outlined in Eric’s article but I couldn’t find the MOF file he referenced. Digging into his post and few follow up links from there, I discovered that since I am running Windows 7 Pro x64-bit, my file is in the C:\Program Files (x86)\Microsoft SQL Server\100\Shared\ directory. So I tried executing the command Eric outlined:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

It failed again, giving me WMI error 0×80041003 , which simply means “Current user does not have permissions to perform the action.” Seeing this, I decided to run same command using “Run As Administrator” on the command prompt and presto, it worked with no issues.

Now I retried SQL Server Configuration Manager and it worked! So thanks, Eric Smile.  I made my change for the Filestream, came back to the command prompt for the Sample Database, executed the command and everything worked like a charm.

So it got me wondering why my setup failed to begin with. I ran the setup again to try and read the error message (FINALLY); but it worked this time so I didn’t see the error. It seems the Sample Database Installer is trying to read what services are installed on your computer and server so it knows where to install the sample databases when you select these. Duh! Oh well, it was still nice tracking it down and getting it working so I can actually work on my original article.

One lesson (re)Learned: Read error messages more carefully!

Also avalaible on OpsVault.com and WordPress Blog.