Problem Attaching the MDXStepByStep database in SQL Server
By: Ghanesh Prasad | Updated: 2016-01-20 | Comments (1) | Related: > Analysis Services Development
I want to attach the MDXStepByStep sample database to learn the MDX language using the Microsoft SQL Server 2008 MDX Step By Step book, but I am unable to attach the MDXStepByStep sample database using the given T-SQL attach database script. How can I attach the MDXStepByStep sample database without the T-SQL Script?
The Microsoft SQL Server 2008 MDX Step By Step book uses the MDXStepByStep sample database to build the Step-by-Step and Chapter 3 cubes. These two cubes are used to demonstrate MDX language examples. In this tip I will demonstrate how we can attach the MDXStepByStep database without using the T-SQL script provided with the book.
The MDXStepByStep database, Analysis Services Solution and MDX scripts can be extracted from the 9780735626188_OCC.exe file which can be downloaded from here. When we execute the 9780735626188_OCC.exe file, it extracts all the files under the Microsoft Press folder onto your C: drive, you can refer to the below image as a point of reference.
Let's see what error we get when we try to attach the MDXStepByStep database using the attach_db.sql script. We get the below error message after executing the attach_db.sql script.
Please follow the below steps to attach the MDXStepByStep database without using the attach_db.sql script.
Step 1 - Copy MDF File
Copy the MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to the SQL Server data path which is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA” for my installation. As you can see from the below image the MDXStepByStep.mdf file has been copied to the Data folder. Please note your Data folder path may be different based on your MSSQLSERVER installation.
Step 2 - Login to SQL Server
Login to your SQL Server with your appropriate credentials, make sure your login has permission to create a database.
Step 3 - Attach Database in SQL Server Management Studio
After successfully logging into SQL Server, please explore the Databases list, as you can see from the below image the MDXStepByStep database does not exist in the databases list.
To attach the database right click on the Databases and click on the Attach option, as shown below.
Step 4 - Select Database File to Attach in SSMS
Once you click on the attach button, a new attach databases window will open. You can refer to the below image. Click on the add button to add the MDXStepByStep.mdf file.
Step 5 - Browse for MDF File
Once you clicked on Add button, a new window will open which will display all available mdf files. Please select the MDXStepByStep.mdf file and click on the OK button, you can refer to the below image
Step 6 - Remove LDF File in SSMS
We have added the MDXStepByStep.mdf file, but the MDXStepByStep.ldf file is missing for this database because we didn’t copy a MDXStepByStep.ldf file to “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA” so we need to remove the missing log file entry. Select MDXStepByStep_log.ldf file entry as shown in below image and click on the Remove button and then click on the OK button.
Step 7 - Verify Attached Database
Congratulations you have successfully attached the MDXStepByStep database. You can verify this by exploring the Databases list in SQL Server Management Studio as shown below.
- Go ahead and attach the MDXStepByStep sample database and get ready to learn MDX languages.
- MDXStepByStep database file can be downloaded from here.
- Check out all of the SQL Server Business Intelligence Tips and Tricks.
- Check out how to learn MDX with these tips:
- A Quick Way to Start Learning SQL Server MDX
- Comparison of Queries Written in T-SQL and SQL Server MDX
- SQL Server Analysis Services (SSAS) Tutorial
- SQL Server Analysis Services Rank and Row Number Ordering
- SQL Server Analysis Services MDX Average Calculation
- Using MDX to Calculate Both Values and Percentages for Analysis Services
- Check out my all tips here.
Last Updated: 2016-01-20
About the author
View all my tips