MS SQL Server 2005 -- Cant not attach to AdventureWorks Database

I have MS SQL Server 2005 installed and when trying to connect to AdventureWorks DB

Downloaded AdventureWorksDB.msi file and when trying to attach recieve this error when running script ?

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.

Then tried to attach the DB and get this

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'JKIMBLE1'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

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

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

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

BUTTONS:

OK

Is there a difference between MS SQL Server 2005 and SQL Server 2005 Express Edition

I am having the same problem. I've done some research, trying to fix this.

This thing is just curious because a coworker successfully installed the AdventureWorks DB and our computers are almost identical, HW and SW.

Anyone?
|||

Are you a server admin on the box where you are trying to attach the database? Or a local admin on the box?

There is no difference in the file format between the various editions of SQL Server 2005. This sounds like a rights problem to me.

|||Fortunately I solved my problem. The thing with me was that I had MSSQL Server 2000 installed on my machine. And when I opened the SQL Server Management Studio I used to connect to the instance of 2000 and not 2005. (e.g. LOCALHOST, instead of LOCALHOST\SQLEXPRESS)

Such a stupid thing, but I never read about it anywhere else.

I guess my problem was that there's no compatibility from 2005 to 2000.

Hope this works for you as well JKimble.
|||Thanks for the info...!! I had exact same issue - I downloaded AdventureWorks database and got the same error when I tried to attach to LOCALHOST but it worked Ok when I changed to LOCALHOST\SQLEXPRESS... NB: the same occurs for AdventureWorksDW database...|||

SteveSatriani wrote:

Thanks for the info...!! I had exact same issue - I downloaded AdventureWorks database and got the same error when I tried to attach to LOCALHOST but it worked Ok when I changed to LOCALHOST\SQLEXPRESS... NB: the same occurs for AdventureWorksDW database...

Two things AdventureWorksDW DB will not work with Express because there is no BI management studio and I know it was not created to work with 2000. But the OLTP AdventureWorks DB comes in both 2000 and 2005. Hope this helps.

MS SQL Server 2005 -- Cant not attach to AdventureWorks Database

I have MS SQL Server 2005 installed and when trying to connect to AdventureWorks DB

Downloaded AdventureWorksDB.msi file and when trying to attach recieve this error when running script ?

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.

Then tried to attach the DB and get this

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'JKIMBLE1'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

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

Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

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

BUTTONS:

OK

Is there a difference between MS SQL Server 2005 and SQL Server 2005 Express Edition

I am having the same problem. I've done some research, trying to fix this.

This thing is just curious because a coworker successfully installed the AdventureWorks DB and our computers are almost identical, HW and SW.

Anyone?
|||

Are you a server admin on the box where you are trying to attach the database? Or a local admin on the box?

There is no difference in the file format between the various editions of SQL Server 2005. This sounds like a rights problem to me.

|||Fortunately I solved my problem. The thing with me was that I had MSSQL Server 2000 installed on my machine. And when I opened the SQL Server Management Studio I used to connect to the instance of 2000 and not 2005. (e.g. LOCALHOST, instead of LOCALHOST\SQLEXPRESS)

Such a stupid thing, but I never read about it anywhere else.

I guess my problem was that there's no compatibility from 2005 to 2000.

Hope this works for you as well JKimble.
|||Thanks for the info...!! I had exact same issue - I downloaded AdventureWorks database and got the same error when I tried to attach to LOCALHOST but it worked Ok when I changed to LOCALHOST\SQLEXPRESS... NB: the same occurs for AdventureWorksDW database...|||

SteveSatriani wrote:

Thanks for the info...!! I had exact same issue - I downloaded AdventureWorks database and got the same error when I tried to attach to LOCALHOST but it worked Ok when I changed to LOCALHOST\SQLEXPRESS... NB: the same occurs for AdventureWorksDW database...

Two things AdventureWorksDW DB will not work with Express because there is no BI management studio and I know it was not created to work with 2000. But the OLTP AdventureWorks DB comes in both 2000 and 2005. Hope this helps.

MS SQL Error 823 I/O Error

Hi there,

I am reinstall my OS (Win2k Server) and try to attach back my database to SQL Server (MS SQL 2000).

After selecting MDF and LDF file. It come out with all those message below in my event viwer. And attach database action is fail.

I had check with Microsoft and download the latest Server Pack 3, but still nothing is change. I am panic here.

Can any one help me to solve this problem.

Your help is very much appreciated.

Thank you.

Dave

Message copy from Event Viwer.

17052 :
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Log.LDF' may be incorrect.

Error: 823, Severity: 24, State: 6
I/O error (torn page) detected during read at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ultra_Log.LDF'.

17207 :
udopen: Operating system error 5(error not found) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF.

17204 :
FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF for virtual device number (VDN) 1.

17052 :
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\ultra_Data.MDF' may be incorrect.Judging from the error messages, are you certain you have the files in the correct path? Just trying to eliminate the obvious, here.|||Originally posted by MCrowley
Judging from the error messages, are you certain you have the files in the correct path? Just trying to eliminate the obvious, here.

Well....i just copy back the file to C:\Program Files\Microsoft SQL Server\MSSQL\Data

so....i just using the utility in the Enterprise Manager call Attach Database...is something like sp_attach_db but in windows graphic mode...

even i am using sp_attach_db command in command prompt, same mssg will appear...

here by i submit my database file...the log file and the data file...
please help to solve it...
i have no idea about all this...

thank you so much

Dave|||Check this link, it may be of relevance to your situation.

Resore database with corrupted log file (http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=uXRytePVCHA.2452%40tkmsftngp09&rnum=4)|||Hey...Thank a lot...
even process in bertween a lot of error reported but it still work. Now i can access to my database now...

Thanks. Very appreciated..

We started out with only the MDF file. The log file had been corrupted and lost due to disk failure and we found out we weren't backing up the
databases, only the file groups so we had a month of semi-useless backups.

Definitions:
<databasename> - this should be replaced, in any query detailed in the steps
below, with the database being recovered.
<database path> - this should be replaced with the path to the SQL data
files.
<database logfile> - this should be replaced with the filename of the SQL
database log file minus the extenstion.

1. In SQL Enterprise Manager, create a new database with the same name as
the old one. Make it the same data file size or larger as the old MDF.

2. Stop SQL Server.

3. Delete the new MDF file and rename the old one back.

4. Start SQL Server. The database should now show up tagged Suspect in
Enterprise Manager.

5. In SQL Query Analyser:
use master
go
sp_configure 'allow', 1
go
reconfigure with override
go
update sysdatabases set status=32768 where name='<databasename>'
go
dbcc rebuild_log('<databasename>','<database path>\<database
logfile>1.ldf')
go

NOTE: the second to last command is not a typo. The last variable of
<databselogfile>1.ldf is correct. If your database was named CONTACTS this
would give you CONTACTS_LOG1.ldf. Don't forget the '1' at the end.

This should result in a message similar to, "Transaction log successfully
rebuilt - transactional consistency lost."

6. In SQL Query Analyzer:
dbcc checkdb('<databasename>')

This may result in a number of errors. If so, go to step 7. If there are no
errors, the process may be complete. Check step 9.

7. In SQL Query Analyzer:
dbcc checkdb('<databasename>',repair_allow_data_loss)

You may have to set the database to single user for this to work. It will
alert you to this. This can be done in Enterprise Manager under the
databases properties.

8. Repeat steps 9 and 10 until step 9 results in no errors.

9. The database may end up with a status of "dbo use only". To clear this,
execute the following in SQL Query Analyzer:
use master
go
exec sp_dboption '<databasename>', 'dbo use only', 'false'
go

You can also do this last step in Enterprise Manager under the databases
properties.