September 4, 2007 at 7:31 am
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.
March 15, 2008 at 2:42 pm
Did you find the answer to this issue? I am currently experiencing the problem as stated here? Anyone?
Joseph
March 15, 2008 at 7:38 pm
Are you sure the linked server user has appropriate permissions on the table in question?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2008 at 7:57 pm
Yes, the linked-server user has appropriate permissions. Also when I dropped all the indexes on the table the query worked. On re-creating the indexes the error resurfaced.
Joseph
March 15, 2008 at 8:50 pm
Are the indexes on the same file as the table? What is your physical setup? Is the server clustered?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2008 at 9:10 pm
Yes the index are on the same physical file as the tables. This is not a clustered environment. It's Windows 2000 and SQL Server 2000 SP4.
Joseph
March 25, 2008 at 12:07 pm
I was able to resolve this issue by deleting all the indexes with hind_ prefix in the sysindexes table in the affected database.
Joseph
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply