Exam 70-433 Atach AdventureWorks fails

Question

Hello.  I have just spent a day and a half uninstalled SQL 2008, installing SQL 2008R2 and trying to attach the three samples databases.  Eventually got AWDW2008 and Northwind but the AdventureWorks2008 refuses to attach because the Documents folder is being read as a file and the path therefore fails.

I have tried it with and without the documents folder and it fails whatever I do.  I've downloaded the sample code from the CD and the MS site, both versions fail.

I would appreciate some suggestions, please.

Mick

Answer 1

AdventureWorks2008 (and AdventureWorks2008R2) require that both full-text search and filestream be installed and enabled. Verifying that these prerequisites have been met would be your first step.  Please see this link  Database Prerequisites  for instructions on how to do that.   Also take a look at the  Database Installer Help topic if you continue to have problems.

Also, you might consider trying to install AdventureWorks2008R2 (same schema as AW2008) from Codeplex because the .exe file has more error checking and will tell you if you have not met the prerequisites for installing the sample database.  You can find that here: http://msftdbprodsamples.codeplex.com/releases/view/45907

If you continue to have problems, please report back with your OS version,  version of SQL Server 2008R2 (e.g. Developer or Express) and the exact error message that you are getting.

Kind Regards,

Gail

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Thanks Gail.

I have checked that the two features and installed and running - on both servers (I am trying this on two machines).

First pop-up says there has been an error and read the hyperlink message.  That message - in full is below:

===================================

Attach database failed for Server 'TELECHART\SQL2008'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
   at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)

===================================

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2008'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
Server Name: TELECHART\SQL2008
Error Number: 5120
Severity: 16
State: 105
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

 

 

As you can see, the problem is that the system is interpreting the documents folder as a file and failing there.

There seem to several problems with this new version, particularly around permissions and logons.  Is there somewhere where I can register and report/discuss these problems?  Or is this the only forum?

 

Mick

Answer 3

>>There seem to several problems with this new version, particularly around permissions and logons.  Is there somewhere where I can register and report/discuss these problems?  Or is this the only forum?

You can report your problem using the Connect site.  This creates a bug (defect) in the product team's database, which will be reviewed and assessed.

 

Regards,

Gail

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Thanks again Gail.

I've left amessage at the Connect site.

Has no-one else come across this problem?  Or is it such an obvious fix that I'm just showing my ignorance?

 

Mick

Answer 5

You are not alone.

Answer 6

So, having established that we are not alone - what is the fix. I've seen exactly the same problem trying to use the Db provided with MS training material for 70-433. The sample db provided with SQL Server 2008 R2 has some significant differences, which renders the MS Training Kit pretty useless unless there is a way of attaching this database.

Any more suggestions, or do we have to abandon this training material?

 

Answer 7

I had the same problem following the 70-433 instructions on 2008R2 Enterprise, the other 2 MDF's attached just fine, even with the Documents folder copied this one just fails with the "Unable to open the physical file..." message.  There does seem to be a way around this though.  There is an installable AdventureWorks2008 RTM at http://msftdbprodsamples.codeplex.com/releases/view/45907.  (If that link changes, the file is named "AdventureWorks2008R2_RTM.exe".)  This one ran just fine and the database is now installed.  It creates a Documents2008R2 in the DATA folder, and MDF file size is almost exactly the same, so it looks like this is what we need.

 

Answer 8

I had similar problem when installing Adventure Works 2008R2

I have used the AdventureWorks2008R2_RTM.exe from http://msftdbprodsamples.codeplex.com/releases/view/45907

All other databases were installed and attached successfully apart from Adventure Works 2008R2 (note: it is called AdventureWorks OLTP 2008R2 on the screen!)

I’ve tried to install it few times unsuccessfully with the following error message:

“Error: Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Documents2008R2". Operating system error -2147024891: "0x80070005(failed to retrieve text for this error. Reason: 1815)". Installation Terminated”

These are the steps which worked for me:

·         On the Start menu, point to All Programs , point to Microsoft SQL Server 2008 R2 , point to Configuration Tools , and then click SQL Server Configuration Manager .

·         In the list of services, right-click SQL Server Services , and then click Open .

·         In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server (e.g. MSSQLSERVER)

·         Right-click the instance and then click Properties .

·         Change the SQL Server (MSSQLSERVER) service so that it is logged in as Local System (while you are trying to install the AdventureWorks databases)

Then follow the installation instructions from the official website:

http://msftdbprodsamples.codeplex.com/wikipage?title=Installing%20SQL%20Server%202008R2%20Databases

Good luck!

P.S. One possible reason for this specific error could be found here: http://connect.microsoft.com/SQLServer/feedback/details/126562/misleading-error-message-while-attaching-the-file

Answer 9

I found a very easy way to avoid the "Suspect" issue for adventure works.  I was a bit annoyed by it but then I decided to read the error message.

It is complaining about the file placeholder.txt being corrupted.  Simply delete that file and reattach.  Problem solved!

70-433\Databases\AdventureWorks2008\Documents\$FSLOG\placeholder.txt

Answer 10

Hello, I changed the user running the service to "Local System" as described below and that seems to have solved my problem as well.  Originally I had the service running as the networkservice account.  Joe

 

ref:wavedrop

Answer 11

I have the same problem with an error reported like this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'MRASYSTEM\SQL2008'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Unable to open the physical file "D:\Storage\DB\2008R2\MSSQL10_50.SQL2008\MSSQL\DATA\Documents2008R2". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
A file activation error occurred. The physical file name 'D:\Storage\DB\2008R2\MSSQL10_50.SQL2008\MSSQL\DATA\Documents2008R2' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2008R2'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 12

I have the same problem with an error reported like this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'MRASYSTEM\SQL2008'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Unable to open the physical file "D:\Storage\DB\2008R2\MSSQL10_50.SQL2008\MSSQL\DATA\Documents2008R2". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
A file activation error occurred. The physical file name 'D:\Storage\DB\2008R2\MSSQL10_50.SQL2008\MSSQL\DATA\Documents2008R2' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2008R2'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5120)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

This is a permissions error releating to the operating system. Please give the SQL Server service account full rights to D:\Storage\DB\2008R2\MSSQL10_50.SQL2008\MSSQL\DATA\Documents2008R2.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 13

It Worked for me too. :)