Post reply

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

  • 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.

     

  • September 4, 2007 at 8:23 am

    #731223

    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]

  • September 4, 2007 at 8:30 am

    #731226

    Thanks Markus! I'll check it out.

  • September 4, 2007 at 8:34 am

    #731228

    Markus,

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

    Art

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

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