error when attaching DB

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Asset5'. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

I am just starting with a book sql server 2005 stored proc programming in tsql & .net and cant get the db to attach, any help pls

I find most publishers of SQL Server books are not careful but Microsoft is changing with their SQL Server books by posting mile long errata for a very respected writer. Their documentation however is the best in the business and their samples AdventureWorks is good size database easy to attach and play with. Try the link below for the samples it also comes with reporting services sample. Install it then go to Managment Studio right click and attach it. Download X86 case insensitive. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||thanks allot, but... all the sprocs in this book are related to this asset5 db. can anyone help me with this error?|||

I already did could not find a solution so the questions is did you put the MDF and LDF in the location below before trying to attach it in Management Studio? Try deleting every thing and starting new put the MDF and LDF in the location below and go to Managment Studio and attach.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

|||it was in the right place, when i try to install adventure works i get the same error. I havent used this install in 1yr, could it be an issue with needed service pack?|||I have installed AdventureWorks with service packs and without all without problems, I am using the developer edition. I remember getting problems with AdventureWorks install in with install.sql in 2000 but I looked in that location and found MDF and LDF created without a database so I deleted both files and the install was completed.

Error When Attaching Database

Hello Everyone,

I am very new to SQL and just completed an install of SQL 2005 Express. After install, I downloaded the Adventure Works database and attempted to attach through both the Object Browser (right-click 'Databases'>'Attach') and by running the script that is located on the SQL download page where I got the Adventure Works db.

Here's the error that I get:

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

I have attempted to go through the help files, but unfortunately the above message is too cryptic for me and I have no idea what it's telling me.

Can someone help?

Thanks!!

YOu can either use the attach method OR create the database with the scripts. There is no need to execute both, as they will end up both with a working Adventureworks database. I always prefer to execute the script as I am able to have a hand on the scripts first :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thank you for the help, Jens.

For what it's worth, I mentioned in my post that I tried both methods of attaching the database because neither of them worked. However, I have discovered why they didn't work:

When I went the Visual Studio page earlier this evening, I chose to install the SQL Server Express Tools thinking that this would give me the total installation including the SQL database engine. Apparently I was wrong. When choosing this option, what I got was the Management Studio and the Business Intelligence Development Studio. I was further led into thinking that I had everything I needed because I currently have the trial edition of SQL Server 2000 Enterprise Edition installed (the books I use in my SQL class are written in ANSI 92 so we only use 2000)...i'm sure you can imagine what happened after opening Management Studio for the first time - I get a connection to my SQL 2000 server complete with all of the databases I had installed over there. VERY CONFUSING if I do say so myself.

What I ultimately had to do was go back to the Visual Studio Express site and download the SQL 2005 Express engine and then install a new named instance. After I did this, I was able to then download the Adventure Works database and attach it successfully.

Not bad for a newbie, eh?!

I have always found SQL confusing which is why I saved it for last in my studies. This is my opinion here so take it for what it's worth - but if Microsoft wants more hobbyists and folks who are curious about IT to download SQL Express, then they need to better clarify and explain the difference between the various download options and the absolute importance of installing the SQL Express engine first. I can just imagine some people out there getting very frustrated and giving up completely.

I hope this helps some folks.

Thanks!!

|||

Hi Dear,

I got the same problem while attaching my database. Actully I received mdf and ldf file which was created in sql 2005. But I was not aware of that. Reading your post help me.

Thanks a lot.

Error When Attaching Adventure Works Database

Hello Everyone,

I am very new to SQL and just completed an install of SQL 2005 Express. After install, I downloaded the Adventure Works database and attempted to attach through both the Object Browser (right-click 'Databases'>'Attach') and by running the script that is located on the SQL download page where I got the Adventure Works db.

Here's the error that I get:

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

I have attempted to go through the help files, but unfortunately the above message is too cryptic for me and I have no idea what it's telling me.

Can someone help?

Thanks!!

YOu can either use the attach method OR create the database with the scripts. There is no need to execute both, as they will end up both with a working Adventureworks database. I always prefer to execute the script as I am able to have a hand on the scripts first :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thank you for the help, Jens.

For what it's worth, I mentioned in my post that I tried both methods of attaching the database because neither of them worked. However, I have discovered why they didn't work:

When I went the Visual Studio page earlier this evening, I chose to install the SQL Server Express Tools thinking that this would give me the total installation including the SQL database engine. Apparently I was wrong. When choosing this option, what I got was the Management Studio and the Business Intelligence Development Studio. I was further led into thinking that I had everything I needed because I currently have the trial edition of SQL Server 2000 Enterprise Edition installed (the books I use in my SQL class are written in ANSI 92 so we only use 2000)...i'm sure you can imagine what happened after opening Management Studio for the first time - I get a connection to my SQL 2000 server complete with all of the databases I had installed over there. VERY CONFUSING if I do say so myself.

What I ultimately had to do was go back to the Visual Studio Express site and download the SQL 2005 Express engine and then install a new named instance. After I did this, I was able to then download the Adventure Works database and attach it successfully.

Not bad for a newbie, eh?!

I have always found SQL confusing which is why I saved it for last in my studies. This is my opinion here so take it for what it's worth - but if Microsoft wants more hobbyists and folks who are curious about IT to download SQL Express, then they need to better clarify and explain the difference between the various download options and the absolute importance of installing the SQL Express engine first. I can just imagine some people out there getting very frustrated and giving up completely.

I hope this helps some folks.

Thanks!!

|||

Hi Dear,

I got the same problem while attaching my database. Actully I received mdf and ldf file which was created in sql 2005. But I was not aware of that. Reading your post help me.

Thanks a lot.

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth Goh
BTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh
|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh
|||Or get Microsoft Technical Support on the phone.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth GohBTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh|||The error pretty much speak for itself. My guess is that you have to try to
export the data to a
clean database, using some combination of script objects (unless you have DD
L files to re-create
them) and for the data SELECT INTO., INSERT SELECT, BCP etc.
I would bring in an expert if above doesn't feel comfortable to you...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<gohtiamtjai@.gmail.com> wrote in message
news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...[vbcol=seagreen]
> Hi,
> More updates after several attempts (with separate SQL server 2000
> SP4).
> 01. I move the MDF and LDF files to another folders. I have two MDF
> and two LDF files for one database.
> 02. I attach the same but blank backup database to my SQL server with
> a new database name.
> 03. Back it up.
> 04. Restore the backup with the original database name in the initial
> folder where the corrupted files were.
> 05. Detach the new restored (but empty) database.
> 06. Move the new created MDF and LDF files to another folder.
> 07. Copy the original corrupted MDF and LDF files back to the original
> folder.
> 08. Attach the database. Got "Suspect" message.
> 09. Update "sysdatabases" to set the database into Emergency Mode.
> 10. Attach the database back. I got Emergency Mode.
> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
> "REPAIR_ALLOW_DATA_LOSS".
> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
> it tells me to have Single-User mode when trying to run "DBCC
> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
> database 'MyDB' because the database is in bypass recovery mode."
> I am stuck here. Could anyone here help, please?
> I may have incorrectly listed down the sequence from point 8 to 11 but
> my current status is at point 13.
>
> Regards,
> Garreth Goh
>
> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:|||Or get Microsoft Technical Support on the phone.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>

Error Violation of Primary Key While Attaching Database

Hello all,
I have been trying to attach a database but it fails because of
duplicate rows (Error 2627).
I searched in the Internet for removing duplicate records but it can
only work if I manage to attach the database first (correct me if I'm
wrong). So without having the database attached to the SQL server, I
will not manage to remove the duplicate rows. But when I tried to
attach, it checks for duplicate records automatically and fail to
attach.
Is there any workaround for my case?
Thanks in advance.
Regards,
Garreth GohBTW, it is SQL 2000 with SP3.
Regards,
Garreth Goh
On Dec 18, 10:02 am, gohtiamt...@.gmail.com wrote:
> Hello all,
> I have been trying to attach a database but it fails because of
> duplicate rows (Error 2627).
> I searched in the Internet for removing duplicate records but it can
> only work if I manage to attach the database first (correct me if I'm
> wrong). So without having the database attached to the SQL server, I
> will not manage to remove the duplicate rows. But when I tried to
> attach, it checks for duplicate records automatically and fail to
> attach.
> Is there any workaround for my case?
> Thanks in advance.
> Regards,
> Garreth Goh|||Hi,
More updates after several attempts (with separate SQL server 2000
SP4).
01. I move the MDF and LDF files to another folders. I have two MDF
and two LDF files for one database.
02. I attach the same but blank backup database to my SQL server with
a new database name.
03. Back it up.
04. Restore the backup with the original database name in the initial
folder where the corrupted files were.
05. Detach the new restored (but empty) database.
06. Move the new created MDF and LDF files to another folder.
07. Copy the original corrupted MDF and LDF files back to the original
folder.
08. Attach the database. Got "Suspect" message.
09. Update "sysdatabases" to set the database into Emergency Mode.
10. Attach the database back. I got Emergency Mode.
11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
"REPAIR_ALLOW_DATA_LOSS".
12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
it tells me to have Single-User mode when trying to run "DBCC
CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
database 'MyDB' because the database is in bypass recovery mode."
I am stuck here. Could anyone here help, please?
I may have incorrectly listed down the sequence from point 8 to 11 but
my current status is at point 13.
Regards,
Garreth Goh
On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
> BTW, it is SQL 2000 with SP3.
> Regards,
> Garreth Goh|||The error pretty much speak for itself. My guess is that you have to try to export the data to a
clean database, using some combination of script objects (unless you have DDL files to re-create
them) and for the data SELECT INTO., INSERT SELECT, BCP etc.
I would bring in an expert if above doesn't feel comfortable to you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<gohtiamtjai@.gmail.com> wrote in message
news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
> Hi,
> More updates after several attempts (with separate SQL server 2000
> SP4).
> 01. I move the MDF and LDF files to another folders. I have two MDF
> and two LDF files for one database.
> 02. I attach the same but blank backup database to my SQL server with
> a new database name.
> 03. Back it up.
> 04. Restore the backup with the original database name in the initial
> folder where the corrupted files were.
> 05. Detach the new restored (but empty) database.
> 06. Move the new created MDF and LDF files to another folder.
> 07. Copy the original corrupted MDF and LDF files back to the original
> folder.
> 08. Attach the database. Got "Suspect" message.
> 09. Update "sysdatabases" to set the database into Emergency Mode.
> 10. Attach the database back. I got Emergency Mode.
> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
> "REPAIR_ALLOW_DATA_LOSS".
> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
> it tells me to have Single-User mode when trying to run "DBCC
> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
> database 'MyDB' because the database is in bypass recovery mode."
> I am stuck here. Could anyone here help, please?
> I may have incorrectly listed down the sequence from point 8 to 11 but
> my current status is at point 13.
>
> Regards,
> Garreth Goh
>
> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh|||Or get Microsoft Technical Support on the phone.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:261F6FD2-B073-42F2-A218-B3B6AFE3750D@.microsoft.com...
> The error pretty much speak for itself. My guess is that you have to try
> to export the data to a clean database, using some combination of script
> objects (unless you have DDL files to re-create them) and for the data
> SELECT INTO., INSERT SELECT, BCP etc.
> I would bring in an expert if above doesn't feel comfortable to you...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <gohtiamtjai@.gmail.com> wrote in message
> news:078e33bc-1d03-4d24-a65b-96367d382dde@.a35g2000prf.googlegroups.com...
>> Hi,
>> More updates after several attempts (with separate SQL server 2000
>> SP4).
>> 01. I move the MDF and LDF files to another folders. I have two MDF
>> and two LDF files for one database.
>> 02. I attach the same but blank backup database to my SQL server with
>> a new database name.
>> 03. Back it up.
>> 04. Restore the backup with the original database name in the initial
>> folder where the corrupted files were.
>> 05. Detach the new restored (but empty) database.
>> 06. Move the new created MDF and LDF files to another folder.
>> 07. Copy the original corrupted MDF and LDF files back to the original
>> folder.
>> 08. Attach the database. Got "Suspect" message.
>> 09. Update "sysdatabases" to set the database into Emergency Mode.
>> 10. Attach the database back. I got Emergency Mode.
>> 11. Run "DBCC CHECKDB WITH NO_INFOMSGS" tells me to do
>> "REPAIR_ALLOW_DATA_LOSS".
>> 12. Stop SQL services and run in Command Prompt "SQLSERVR -c -m" cause
>> it tells me to have Single-User mode when trying to run "DBCC
>> CHECKDB ...REPAIR_ALLOW_DATA_LOSS".
>> 13. When I try to run "DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS" in
>> this Single-Mode, it tells me "Could not run BEGIN TRANSACTION in
>> database 'MyDB' because the database is in bypass recovery mode."
>> I am stuck here. Could anyone here help, please?
>> I may have incorrectly listed down the sequence from point 8 to 11 but
>> my current status is at point 13.
>>
>> Regards,
>> Garreth Goh
>>
>> On Dec 18, 10:58 am, gohtiamt...@.gmail.com wrote:
>> BTW, it is SQL 2000 with SP3.
>> Regards,
>> Garreth Goh
>