Linked server access to a table fails but accessing other tables work ok.

Question

Linked server access to a table fails but accessing other tables work ok.

Art Sennholtz

Old Hand

Points: 355
More actions
September 4, 2007 at 7:31 am

#197682

Here's the script: SELECT TOP 10 * from server1.db1.dbo.tbl1

Here's the scenario:
Server1 SQL2000 SP4
       db1
              tbl1
              tbl2
       db2

Server2 SQL2000 SP4
       db3

When I run this script from Server1.db2 server it runs fine. When I run this script from linked server Server2.db3 I get the following:

Statistics for INDEX 'idx_DemAddress'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2727, Level 16, State 47, Line 1
Cannot find index 'hind_c_1110295015_8A_1A'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2727, Level 16, State 47, Line 1
Cannot find index 'hind_c_1110295015_8A'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2727, Level 16, State 47, Line 1
Cannot find index 'hind_c_1110295015_1A_18A'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2727, Level 16, State 47, Line 1
Cannot find index 'hind_c_1110295015_1A'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2727, Level 16, State 47, Line 1
Cannot find index 'hind_c_1110295015_18A'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Also, I can access any of the other tables in db1 from Server2.db3. It is just this one table.

I dropped and recreated the index but that didn't work. I dropped the index and ran the query and I got exactly the same message except the index name on the 'Statistics' line changed.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The indexes with a name like "hind_" are hypothetical indexes left over by the index tuning wizard. There is a KB article about it http://support.microsoft.com/kb/q293177/, but the problem should be fixed since SP1.

Anyhow, removing all indexes with such a name should solve your problem.

Markus

[font="Verdana"]Markus Bohse[/font]

Art Sennholtz Old Hand Points: 355 More actions · Answer 2

Art Sennholtz

Old Hand

Points: 355

September 4, 2007 at 8:30 am

#731226

Thanks Markus! I'll check it out.

Art Sennholtz Old Hand Points: 355 More actions · Answer 3

Markus,

you're a life saver! I ran the script and it worked like a charm. Thanks again!

Art