Wednesday, March 21, 2012

Getting sample databases in Management Studio

How to get sample database AdventureWorks and AdventureWorksDW
deployed in Management Studio. I selected to install sample database
entire feature on my local hard drive and I can see it did installed all
the .mdf,.ldf,.dim,.cube and the rest in
C:\Program Files\Microsoft SQL Server\90\Tools\Samples\

How can I get the database in Management studio.
I tried to use Attach db and it didn't worked, gave me following error

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Any help would be appreciated.

Here is a link to a msi to install the database

http://www.microsoft.com/downloads/info.aspx?na=46&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=8D5BDE68-4991-432A-9D4B-0562DC49992E&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f4%2fb%2fc%2f4bc19fa4-5849-46a6-bebb-c22493d219e8%2fAdventureWorksDB.msi&oRef=http%3a%2f%2flab.msdn.microsoft.com%2fexpress%2fsql%2fdefault.aspx|||I installed using the above link but I am unable to see the AdventureWorks db
in the Management studio under database tree.

After the installation do I need to configure something?
|||hi Ken,

This is link to Adventure Works DB. Do you have link to Adventure Works DW msi?|||Sorry I cant find a link.

Here is some better instructions on how to install the adventure works db
http://download.microsoft.com/download/2/a/a/2aaa56bc-a173-4263-af15-e9421a14d5f8/SQLServerExpressSamplesOverview.htm|||If the sample databases were installed correctly, the .mdf and .ldf files for both AdventureWorks and AdventureWorksDW would be located in this folder
<drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data not the folder you reference in your post. And you shouldn't have to attach the databases; the installation process performs the attach operation by default. However, it sounds like that didn't happen, so as an alternative you can re-create the databases by using the installation scripts.

The install script (instawdb.sql) for AdventureWorks should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP
and the install script (instawdwdb.sql) for AdventureWorksDW should be located in
Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Data Warehouse.

You can run these scripts to re-create the sample databases. Please read the Books Online topic "Reinstalling Sample Databases from Script " for detailed instructions on how to do this. You must modify one statement in both scripts to point to the correct location of the .csv (raw data) files. This is explained in the topic.

Regards,
|||

In the vs2005 documentation there are excellent walkthroughs for asp.net 2.0. Someone overlooked that only AdventureWorks comes with it. Several of the walkthroughs want Northwind and Pubs.

I deleted those when I uninstalled sqlserver 2000. Where can I download them and how would I install them in sqlserver 2005?

Thanks in advance.

dennist685

|||You can download them here

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en|||As Ken points out, these databases are available for download. The readme that is included in the download provides instructions for installing the databases. The instructions assume you have SQL Server 2000, but they are essentially the same for SQL Server 2005. You have two choices: attach the databases or run the installation scripts.

Regards,|||gaile,

I'm having the same problem as the original poster (same error). The .msi file installs but the database is not attached. Does it make a difference that I am using the Demo version of SQL 2005? The folder you mention does not exist (Program Files\Microsoft SQL Server\90\Tools\Samples <- there is no samples folder).|||

I need some more information please.
Which database are you trying to install?

If it's AdventureWorks or AdventureWorksDW, did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?

If you downloaded the msi, you need to manually attach the database. The process for doing this is described in the readme file that is included in the download.

What version of SQL Server 2005 do you have installed?
Thanks,

|||"Which database are you trying to install?"

Both AdventureWorks and AdventureWorksDW.

"...did you follow the instructions in the Books Online topic "Running Setup to Install AdventureWorks Sample Databases and Samples ", or did you download the msi from the web?"

I downloaded the msi. Thanks for the suggestion though. I looked up that topic in Books Online and it seems I'm getting a bit further... however, once I try to execute the script as per the instructions, I get tons of errors. Here's a few of the early errors:

Oh, and version of SQL is SQL 2005 Enterprise I believe (the demo only comes in one version). http://www.microsoft.com/sql/downloads/default.mspx


*** Dropping Database
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.

*** Creating Database
Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.master_files'.

Msg 102, Level 15, State 6, Line 11
Incorrect syntax near 'CHECKSUM'.

Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.

|||once I try to execute the script as per the instructions, I get tons of errors.

What script are you running? There's nothing in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" about running a script. Look for the section "To install the sample databases and samples after setup" in the topic and see if you can get those steps to work.

Regards,|||

Here's how far I get following the "To install the sample databases and samples after setup"

4. From Feature Selection, expand the Books Online and Samples node.

There is no "Books Online and Samples" node. There is a "Client Components" and a "Documentation, Samples, and Sample Databases" node. I select the latter.

5. Select Samples.

There is no "Samples", but there is a "Sample Databases" node. I select it.

6. Expand Databases and then select the sample databases to be installed. Click Next.

At this point there is 2 nodes, "Adventureworks Sample OLTP" and "AdventureworksDW Sample Warehouse". Beneath the latter there is another node "Adventureworks Sample OLAP". All nodes are already solid White, as in they are already installed to my computer due to previous efforts.

7. To install and attach the sample databases, from Sample Databases Setup, select Attach sample databases and click Next.

I don't get this option. After clicking next, the wizard goes into it's install routine.

The database files are created and stored in the folder n:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.

I do now have an AdventureWorksDW database in this folder, and it is attached. However, the tutorial refers to an AdventureWorks (not DW) database, and I in-fact do not have any of the tables refered to in the tutorial, such as the first one I come across - Production.Product.

8. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases and click Next.

As Above, I do not have this option.

8. Complete the steps in the wizard.

9. To complete the installation of the samples, after Setup, perform one of the following steps:

10. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.

11. Alternatively, using Windows Explorer, navigate to n:\Program Files\Microsoft SQL Server\90\Tools\Samples\ and double-click SqlServerSamples.msi to launch the installer.

Tried these steps too, the SqlServerSamples.msi (also on the Program menu) is there and it runs, it just doesn't DO anything apparently!

It appears to me that the help files contained in "Books Online" is not concurrent with the present release of SQL Server 2005. The tutorial databases are not possible to install under the current instructions.

|||Thanks for your patience on this. I really appreciate it. Your information tells me that (for some unknown reason) setup thinks the sample databases have been installed, and it's ignoring the request to install them. We've seen this before and have opened a bug with setup, but it's been hard to reproduce.

Let's look at a couple of alternatives. In your original post, you said you ran the download, but the databases didn't attach. The download does not automatically attach the files, but this should mean that you actually have the .mdf and .ldf files available for attaching.

Please look in the <drive:>\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder for the files AdventureWorks_data.mdf and/or AdventureWorksDW_Data.mdf.

If you have these files, then you just need to attach the databases. You can do this from SQL Server Management Studio, or by running the following statements.

exec 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';

exec sp_attach_db @.dbname=N'AdventureWorksDW', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_log.ldf';

If you do not have these files, then you can download the sample databases again.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

If you do the download again, please follow the instructions for removing previous installations of the sample databases described in http://download.microsoft.com/download/d/8/6/d865cf0c-c44b-401b-b426-b3bf5c628112/SQLServerDatabasesAndSamplesOverview.htm. I know this seems unnecessary, but it won't hurt your system and could help.

Again, thanks for your perseverance.

Regards,

No comments:

Post a Comment