Component Integration Services is unaware of indexes that are added or dropped outside Component Integration Services. Verify that the indexes used by Component Integration Services are the same as the indexes used on the remote server. Use sp_help to see the indexes used by Component Integration Services. Use the appropriate command on your remote server to verify the indexes used by the remote server.
If the indexes are not the same, the steps to correct the problem differ, depending on whether create existing table or create table was used to define the object.
To determine which method was used to define the object, run:
sp_help object_name
If the object was defined via the create existing table command, the following message is returned in the result set:
Object created with 'existing' option
If this message is not displayed, the object was defined via create table.
If create existing table was used to create the object:
Use drop table in Component Integration Services.
Re-create the table in Component Integration Services using create existing table. This will update the indexes to match the indexes on the remote table.
If create table was used to create the object:
Use drop index to drop the index from the remote table.
Re-create the index in Component Integration Services using create index. This creates the index in Component Integration Services and the remote server.
If create table was used to define the object, an alternative method is to turn on trace flag 11208. This trace flag prevents create index from transmitting to the remote server. To use trace flag 11208, follow these steps:
Turn on trace flag 11208:
dbcc traceon(11208)
Create the index using create index.
Turn off trace flag 11208:
dbcc traceoff(11208)
