Installing Adventureworks database in SQL Server 2005

It was hard to track down a useful reference for installing the AdventureWorks database - this one worked (almost). Just had to use regular SQL database, not sqlexpress, but that was easy to find by refreshing the database list.

Just in case the page disappears, copying here.

http://msdn.microsoft.com/en-us/library/aa992075(VS.80).aspx


Walkthrough: Installing the AdventureWorks Database

This walkthrough shows how to install the AdventureWorks sample database for SQL Server 2005, which is a prerequisite for other walkthroughs provided in the ReportViewer documentation. AdventureWorks is not automatically installed with SQL Server or SQL Server Express, but can be downloaded from a Microsoft Web site. You can use the instructions provided in this walkthrough to download, install, and configure a connection to the AdventureWorks database.

The version of AdventureWorks that is distributed with SQL Server 2005 is different from earlier versions AdventureWorks. If you already have a previous version of AdventureWorks, you should install the most recent version so that the queries in other ReportViewer walkthroughs work as expected.

To learn more about using the AdventureWorks sample database, you can search for "AdventureWorks Sample OLTP Database" in SQL Server 2005 Books Online. Books Online is available in most editions of Visual Studio 2005. You can view it on MSDN at www.microsoft.com. You can also download the "AdventureWorks OLTP Database Diagram" from http://www.microsoft.com/downloads/.

Prerequisites
--------------------------------------------------------------------------------

You must have a SQL Server instance installed for which you have permissions. If you installed SQL Server 2005 Express, the default instance name is (local)\sqlexpress. If you upgraded an MSDE instance to SQL Server 2005 Express, see the SQL Server Books Online topic "Upgrading MSDE to SQL Server Express".

The following steps describe how to download, save, attach, and configure the AdventureWorks database.

To download adventureworksdb.msi
--------------------------------------------------------------------------------

1.Navigate to this download Microsoft Web site. Double-click AdventureWorksDB.msi.

2.Click Run and complete the installation wizard. AdventureWorks_Data.mdf and AdventureWorks_Log.ldf are copied to your SQL Server data directory. For SQL Server 2005 Express, the default is c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\.

To confirm your SQL Server data directory
--------------------------------------------------------------------------------

1.Open a Visual Studio 2005 command window. From the Start menu, click All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt.

2.Open a connection to a local SQL Server Express instance by typing or copying the following command into the command line:

CopySQLCMD -S (local)\sqlexpress -E
3.Press Enter.

4.At the SQLCMD prompt, type or copy the following commands.

Copyselect physical_name from sys.database_files where name = 'master'
go
This query returns the location of the file master.mdf, confirming the SQL Server data directory where you will place the AdventureWorks database files.

To attach AdventureWorks to your SQL Server
--------------------------------------------------------------------------------

1.At the SQLCMD 1> prompt, call the sp_attach_db stored procedure to attach the AdventureWorks database in the data directory determined in the previous step. The line below uses the default location for SQL Server 2005 Express. Edit the text as necessary to use the data directory confirmed in the previous steps. Type or copy and paste the following statement into the command window (the statement must not have line breaks).

Copyexec sp_attach_db @dbname=N'AdventureWorks', @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'
2.Type go.

3.Press ENTER. You may see a series of messages about converting and upgrading the database.

4.Type exit to close the command window.

To verify Adventureworks is attached
--------------------------------------------------------------------------------

1.Open Visual Studio 2005. You do not need to open a project.

2.From the View menu, choose Server Explorer.

3.In the Server Explorer window, right-click on Data Connections and choose Add Connection. The Add Connection dialog box opens.

4.Confirm Data source is set to Microsoft SQL Server (SqlClient).

5.In the Server name textbox, enter (local)\sqlexpress.

6.In Log on to the server, choose your method of authentication.

7.In Select or enter a database name, expand the list of databases.

8.Select AdventureWorks.

9.Click the Test Connection button to confirm that you have connected to AdventureWorks successfully.

Note
With a successful connection, you can right-click the data connection icon for AdventureWorks and select New Query to invoke a Query Expression window if you want to try out queries against this data source.


To configure AdventureWorks to work with Web server controls
--------------------------------------------------------------------------------

1.Open a Visual Studio 2005 command window to run SQLCMD. You will use SQLCMD to grant access to the database and ASP.NET account.

On Windows Server 2003, the ASP.NET process used by the Web server control runs under NT AUTHORITY\NETWORK SERVICE. On other Windows operating systems, the process runs under MACHINENAME\ASPNET.

If you installed SQL Server Express 2005, type or copy the following SQLCMD below to grant login authority to the service. Otherwise, you must specify the SQL Server instance you want to use in place of (local)\sqlexpress.

The following example works for Windows Server 2003 and must have no line breaks.

Copysqlcmd -E -S (local)\sqlexpress -Q "sp_grantlogin N'NT AUTHORITY\NETWORK SERVICE'"
2.To grant access to AdventureWorks for this service, use the following command with no line breaks.

Copysqlcmd -E -S (local)\sqlexpress -d AdventureWorks -Q "sp_grantdbaccess N'NT AUTHORITY\NETWORK SERVICE'"
3.To add this service to the owner role account for AdventureWorks, use the following command with no line breaks.

Copysqlcmd -E -S (local)\sqlexpress -d AdventureWorks -Q "sp_addrolemember 'db_owner', N'NT AUTHORITY\NETWORK SERVICE'"
4.Close the command window.

5.You can confirm the configuration by successfully completing Walkthrough: Using a Database Data Source with the ReportViewer Web Server Control in Local Processing Mode.