mcrmg
asked on
~~HELP UGENT!!!~~~ Table can not be opened
Hi,
I have a SP, I am sure it was written incorrectly, it ran forever. So, I cancelled it. Now I can not even open the table. When I trry to open a table, all the headers are gone, (in design view still show the column names and data tytpes....), and gives me msg saying,
[microsoft][odbc sql server driver][dbnetlib] connectionread(wrapperread ())
For those tables that sp did not touch remain fine.
Please help...........
I have a SP, I am sure it was written incorrectly, it ran forever. So, I cancelled it. Now I can not even open the table. When I trry to open a table, all the headers are gone, (in design view still show the column names and data tytpes....), and gives me msg saying,
[microsoft][odbc sql server driver][dbnetlib] connectionread(wrapperread
For those tables that sp did not touch remain fine.
Please help...........
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I assume there didnt used to be 0 rows ?
ASKER
You are correct. After runnign the sp, I did not check how many rows in the table, because it was running to long, so I delete mytable, but it also ran forever........
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes, I can....no bk....... :(
how many rows does sp_spaceused mytable report
"so I delete mytable" did you mean here that you deleted the data out of the table?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it says 7 rows. Yes, I delete the content of that table.
ASKER
after SELECT * FROM Mytable where 1=0
only shows headers, no data
only shows headers, no data
ok, first of all before we get into trying to recover, it is worth doing a quick backup, just incase.
what indexes you you have on the table? these should be shown in sp_help Mytable
ASKER
Only have two in this table (There are about 10 tables FK to this table)
one is IX and PK
one is IX and PK
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SELECT * FROM loaninfo_static
>only shows headers
same with
SELECT * FROM MyTable WITH(INDEX=IX_myIndexName)
>only shows headers
same with
SELECT * FROM MyTable WITH(INDEX=IX_myIndexName)
Are the 7 rows essential ? can you manually reenter them by memory ?
ASKER
No, I cannot, because that sp takes the values from other table and insert them into the dest table.......... :(
so basically the data can be regenerated in this table using your sp?
ASKER
yes, do I need to rerun the sp?
I have two sps, one runs ok, the other one which messed with my tables. The later one is modified from the first one.
I have two sps, one runs ok, the other one which messed with my tables. The later one is modified from the first one.
I take it that the table itself isnt generated by the sp, but if we can recreate an empty table wisth the same schema then it'll be ok ?
ASKER
I created those tables, in order to recreate those tables, do I have to do it manaually?
It sounds as though what ever has happened the content of the table is corrupt, but the schema is in tact
You could try to script the table from QA. if this generates the script ok then the easiest thing is to drop the table and execute the script to recreate it.
create an empty table based on this one by
SELECT * INTO NewTableName FROM MyTable WHERE 1=0
But you wil then need to drop your original and specify any indexes , constraints ans relationships on the new one manually.
You could try to script the table from QA. if this generates the script ok then the easiest thing is to drop the table and execute the script to recreate it.
create an empty table based on this one by
SELECT * INTO NewTableName FROM MyTable WHERE 1=0
But you wil then need to drop your original and specify any indexes , constraints ans relationships on the new one manually.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did run
DBCC DBREINDEX (yourtable)
then run select *from ..
It is working now....why is that?
DBCC DBREINDEX (yourtable)
then run select *from ..
It is working now....why is that?
it is rebuild all indexes
let's say
index match data location
let's say
index match data location
ASKER
There are 0 rows in 510 pages for object 'LoanInfo_Static'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.