error attaching db...

Hello,

I tried to attach my database (SQL Server 2000 w/ SP3) at work after taking it home to work on it and it gave me an error.

I tried to attaching my backup one as one (which use to work) and now does not. I treid to attach other DB's and they are fine.

The error message displayed on the screen is:

----
Microsoft SQL-DMO (ODBC SQLState:HY000)

X Error 3264
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1004
----

The error message recorded in Event Viewer is:

------
Event ID: 17055

17066 :
SQL Server Assertion: File: <recbase.cpp>, line=1374
Failed Assertion = 'm_nVars > 0'.

------

Any ideas what could have gone wrong? or is it simply a matter of reinstalling SQL server.

thanks,

JPCheck this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH|||Originally posted by Satya
Check this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH

Hey satya,

thanks for the heads up. I don't understand why it should be a SP issue because both of them are the same and it use to work before. Last week, I did the same... detach the DB, worked on it at home and rettached it back at work... no problem.

Oh well, such is life ... full of disappointments. I'll reintall the SP and see what happens. Maybe I'll do a windows update as well cuz we never know if it does affect anything else.

I'll report back should I get the problem fixed.

thanks,

JP|||Originally posted by globeuser
Hey satya,

thanks for the heads up. I don't understand why it should be a SP issue because both of them are the same and it use to work before. Last week, I did the same... detach the DB, worked on it at home and rettached it back at work... no problem.

Oh well, such is life ... full of disappointments. I'll reintall the SP and see what happens. Maybe I'll do a windows update as well cuz we never know if it does affect anything else.

I'll report back should I get the problem fixed.

thanks,

JP

Nope.. same error result. did not work. I've reapplied SP3 and unfortunately I'm still getting this error.

One factor though... I ftp'd down the DB instead of making a copy on the CD but I've downloaded them a number of times and still the same error appears.

any other ideas anyone?

kinda desperate ;-) Guess I'll have to reinstall?

JP|||Originally posted by Satya
Check this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH

Yup... looks like a reinstall is the only option because I am now getting another error message:

--------

Microsoft SQL-DMO (ODBC SQLState:42000)

X Error 1813: Cound not opne new database 'MyProject'. CREATE DATABASE is aborterd.
SQlDumpExceptionHandler: Process 51 genrated fatal exception c0000005 EXCEPTION_ACCESS_violation
Server is terminating this process.

--------

any other ideas?

JP|||Last resort to contact MS PSS for any fix on this EAV issues.|||Originally posted by Satya
Last resort to contact MS PSS for any fix on this EAV issues.

I had a hunch that it might just be as simple s this but I remembered that when I tried to detach it, it took some time because there were stilll some connections to it and I "forced it" to disconnect. It should not take that long because I have a very fast machine.

Lucky for me that the database was still there and I reattached it again first thing before any connections or apps have access to it.

The detachment is now clean and was able to attach it now to another machine.

The lesson learned is that the detach process was dirty or forced and all I had to do was to do it again before any other applications has a lock on it.

JP|||Thats fine, but before you detach make sure to run health checks and DBCC against the database to make sure its lively.|||Originally posted by Satya
Thats fine, but before you detach make sure to run health checks and DBCC against the database to make sure its lively.

Sorry, but how do you do that? I noticed that when I tried to run one of my stored procedures or view the properties in the DB, i get an error displayed...

And when I tried to delete the stored procedure to recreate it won't allow me to or can't delete it!

------
Microsoft SQL-DMO (ODBC SQLState:HY000)

X Error 605: Attempt to to fetch logical page (1:0) in database 'MyProject' belongs to object 'ALLOCATION', not to object 'syscomments'.
------

I am running ColdFusion 5 and this was what was displayed.

------
ODBC Error Code = S1000 (General error)

[Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to fetch logical page (1:0) in database 'MyProject' belongs to object 'ALLOCATION', not to object 'syscomments'.
------

As I said, the SP works in my other machine and only when I move it over to this one that the error pops up.

ideas anyone?

JP|||This error occurs when SQL detcts any database corruption and to determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error.

For more information on CHECKDB refer to books online.

My suggestion is to keep database in DBO Use only until you resolve the corruption on the database, do not allow any other process to deal with until this gets fixed.

HTH|||Originally posted by Satya
This error occurs when SQL detcts any database corruption and to determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error.

For more information on CHECKDB refer to books online.

My suggestion is to keep database in DBO Use only until you resolve the corruption on the database, do not allow any other process to deal with until this gets fixed.

HTH

I tried correcting the db is single-user mode but when i tried to execute

dbcc checkdb ('MyProject', REPAIR_ALLOW_DATA_LOSS )

it notifies me that I should be in single-user mode which I already am and won't continue.!

perhaps I should just reinstall SQL... good thing I keep a script of table creation and sp procedures.

JP|||Make sure you haven't opened or accessed same DB in EM and QA.
Its not necessarily to have DB in singel-user mode when running DBCC CHECKDB, you can kill any other process which is involved to access during this execution.|||Originally posted by Satya
Make sure you haven't opened or accessed same DB in EM and QA.
Its not necessarily to have DB in singel-user mode when running DBCC CHECKDB, you can kill any other process which is involved to access during this execution.

I'm sure i haven't got any connections to it. I just finished installing it and still same errors... *sigh*|||I downloaded the same DB from another machine and to my surprise this was not corrupt at all. Perhaps its the target machine where I uploaded it to that caused the error. There are so many factors that I would not be surprised a simple thing such as these may be the cause in the first place.

thanks for the checkdb info.

JP|||If the problem persists from Database there is no use if SQL Server is reinstalled. Try to fix the corruption on the database level.

As you've scripts for the database, export important data using DTS to a text file and recreate the DB using scripts and import using DTS, it may work.

Meanwhile have a sneak thru SQL Error log for any information.

All the best.|||the good thing is that my main development machine is at my home office and the test box is here at work.

I'm not too concerned about the data for now because I'm still writing the program but I have tried to fix the corruption at the db level but quite honestly, the error log doesn't tell me anything more than what was displayed when I ran the cehckdb script anyway and even if I tried, i can't get it to fix it.

JP|||For the database corruption its suggested to use DBCC CHECKDB and check for event viewer also for any hardware issues or harddisk problems.|||I understand. I did mention it in my original post along with what the event viewer recorded.

anyway, when i have some time, I'll investigate further.

thanks,

JP