Count / SysIndex

Hi,
I have a problem in that when I perform a "Select Count(*) from table1" I
receive a different value than "SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table1') AND indid < 2".
Is this to be expected? Can anyone help?
Thanks,
JoeJoe
The system sysindex table can contain let say it incorrect info about rows
count .
Run update statistcs and see if result is correct.
If you want to know how many rows your table has , then rin select count(*)
from table
Also look at mu example
CREATE TABLE TT
(
COL INT NULL
)
INSERT INTO TT VALUES (NULL)
INSERT INTO TT VALUES (1)
SELECT COUNT(COL) FROM TT
What do you think how many rows will return this statement?
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Sysindexes is only updated at checkpoint. Never trust systindexes as it is
an approximation since SQL 7.0.
This was done for performance reasons.
Cheers
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Joe Doherty" <joeydocherty2404@.hotmail.com> wrote in message
news:OPTPY9rzDHA.3116@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a problem in that when I perform a "Select Count(*) from table1" I
> receive a different value than "SELECT rows FROM sysindexes WHERE id => OBJECT_ID('table1') AND indid < 2".
> Is this to be expected? Can anyone help?
> Thanks,
> Joe
>|||Hi Joe,
run "DBCC UPDATEUSAGE ... WITH COUNT_ROWS" on the database.
This will report and corrects inaccuracies in the sysindexes table, which may
result in incorrect values returned by sysindexes table.
But it is always safer to use count(*) rather than leaning on sysindexes
table.
-- Vishal

could not find row in sysindexes for database

I am trying to attach a database that was created on another server. I believe the database was created using SQL Server Management Studio Express 2005. Now, I have successfully attached the database in my development environment, which is using SQL Server Management Studio Express 2005.

However, when I tried to attach the database onto our production server, which is SQL Server 2000 I received the error:

Error 602:
Could not find row in sysindexes for database ID 18, object ID 1, index ID 1. Run DBCC CheckTable on sysindexes.

In my development environment, I ran DBCC CheckDB on the source database and no errors are returned.

I aslo checked the compatibility level under the database properties and it shows: Sql Server 2000 (80)

So, this should not be a version incompatibility issue. What is causing the attach to fail on SQL Server 2000?

Thanks for any help.

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

|||

Are you trying to attach a SQL 2005 database to a SQL 2000 server? If so, that's the issue. Obviously you can upgrade (2000 --> 2005), but you can't downgrade.

-Damien

|||

CS4Ever:

What I know is:

If you have an .mdf file in SQL Server 2000, then you attached it to SQL Server 2005 ... no way you can use it (the .mdf file) again with SQL Server 2000.

What I think about is SQL Serve 2006 .mdf files can not be used with SQL Server 2000!!

Try Backup/Restore option or Copy Database Wizard.

Good luck.

Here is a link that support what I wrote and it marked as answered in MSDN forum: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=165696&SiteID=17

This link might be helpful for you as well: http://sqlserver-qa.net/blogs/tools/archive/2007/08/14/error-could-not-find-row-in-sysindexes-for-database-8966-823-and-602.aspx

Good luck.

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexes
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
Bob Sullentrup
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup
|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is from
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Could Not Find Row In Sysindexes

Folks,
I have an MDF and LDF file which I am attempting to attach as a SQL db. I
keep getting the error 602 that begins with "could not find row in sysindexe
s
for database ID 13, object id 1, index id 1".
I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I tried
to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
get this error?
--
Bob Sullentrup> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
Yes, that is the error you get when trying to attach a database that is from
a higher version of SQL Server.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||Hi,
Looks like your database is corrupted. Do you a good copy of database in
your server; else you may need to restore
from a good backup available.
For SQL 2000; Please execute DBCC CHECKDB atleast weekly once to chek if
there is any corruptions.
Thanks
Hari
"Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in message
news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
> Folks,
> I have an MDF and LDF file which I am attempting to attach as a SQL db. I
> keep getting the error 602 that begins with "could not find row in
> sysindexes
> for database ID 13, object id 1, index id 1".
> I think MDF and LDF files are SQL 2000, but I'm having my doubts. If I
> tried
> to load MDF and LDF files into 2000 that had been tweaked by 2005, would I
> get this error?
> --
> Bob Sullentrup|||And the answer is ...
I installed SQL Server 2005 and tried to load my mdf file. It worked
perfectly, so clearly I was trying to load into SQL Server 2000 something
that had the 2005 format.
Meanwhile, I scripted the objects and tweaked the script and loaded the
gemisch into SQL 2000. Then I exported the data from 2005 into 2000. Works
like a charm in SQL 2000 now.
Bob Sullentrup
"Gail Erickson [MS]" wrote:

> Yes, that is the error you get when trying to attach a database that is fr
om
> a higher version of SQL Server.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "Bob Sullentrup" <BobSullentrup@.discussions.microsoft.com> wrote in messag
e
> news:0BE96022-FD34-41CB-AFFE-18B3D3B2FEA7@.microsoft.com...
>
>

Corrupted sysindexes

After running the command dbcc checkdb, which is part of a weekly job the following returned
CHECKDB found 1 allocation errors and 957 consistency errors in table 'sysindexes' (object ID 2).
Any ideas on how to fixed this sysindexes table.
I've run dbcc updateusage, dbbc reindex on all tables, dbbc checkdb
Considering it is sysindexes, my guess you are in for a restore. If that isn't convenient, opening a case with
MS is probably a good idea (but chances are that they don't have better options either). General
recommendations for corrupt or suspect databases: http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen" <stephen.hoye@.tabq.com.au> wrote in message
news:89575553-91E2-459B-B6CD-0709E5C79463@.microsoft.com...
> After running the command dbcc checkdb, which is part of a weekly job the following returned
> CHECKDB found 1 allocation errors and 957 consistency errors in table 'sysindexes' (object ID 2).
> Any ideas on how to fixed this sysindexes table.
> I've run dbcc updateusage, dbbc reindex on all tables, dbbc checkdb
>
|||I'm going to create a DTS package and move the database over to anthor DB.
Then I'l rename them. We do have back up, but the problem was picked up after I long weekend.
There was no going back because a number of days had pasted.
Doing the above DTS method, there is no loss in data.

Corrupted sysindexes

After running the command dbcc checkdb, which is part of a weekly job the following returne
CHECKDB found 1 allocation errors and 957 consistency errors in table 'sysindexes' (object ID 2)
Any ideas on how to fixed this sysindexes table.
I've run dbcc updateusage, dbbc reindex on all tables, dbbc checkdConsidering it is sysindexes, my guess you are in for a restore. If that isn't convenient, opening a case with
MS is probably a good idea (but chances are that they don't have better options either). General
recommendations for corrupt or suspect databases: http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen" <stephen.hoye@.tabq.com.au> wrote in message
news:89575553-91E2-459B-B6CD-0709E5C79463@.microsoft.com...
> After running the command dbcc checkdb, which is part of a weekly job the following returned
> CHECKDB found 1 allocation errors and 957 consistency errors in table 'sysindexes' (object ID 2).
> Any ideas on how to fixed this sysindexes table.
> I've run dbcc updateusage, dbbc reindex on all tables, dbbc checkdb
>|||I'm going to create a DTS package and move the database over to anthor DB
Then I'l rename them. We do have back up, but the problem was picked up after I long weekend
There was no going back because a number of days had pasted
Doing the above DTS method, there is no loss in data.