Problem Retrieving Data from Remote Objects

When you receive error messages pertaining to mismatches in remote objects, the Component Integration Services object definition does not match the remote object definition.

This happens if:

The object definition was altered outside of Component Integration Services.
An index was added or dropped outside of Component Integration Services.

Object is Altered Outside Component Integration Services

Once an object is defined in Component Integration Services, alterations made to an object at the remote server are not made to the local proxy object definition. If an object is altered outside of Component Integration Services, 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:

You defined the object via create existing table, the following message is returned in the result set:
```
Object created with 'existing' option
```
This message is not displayed, the object was defined via create table.
You used create existing table to create the table in Component Integration Services:
1. Use drop table in Component Integration Services.
2. Create the table again in Component Integration Services using create existing table. This creates the table using the new version of the table on the remote server.
You created the table in Component Integration Services using create table, the remote object is dropped when you use drop table. To prevent this:
1. Rename the table on the remote server so the table is not deleted when you use drop table.
2. Create a table on the remote server using the original name.
3. Use drop table in Component Integration Services to drop the table in Component Integration Services and on the remote server.
4. Rename the saved table in step 1 with its original name on the remote server.
5. Create the table again in Component Integration Services using create existing table.

Warning! Do not use drop table in Component Integration Services before renaming the table on the remote server, or you will delete the table on the remote server.

A good rule to follow is to create the object on the remote server, and then execute create existing table to create the object in Component Integration Services. This enables you to correct mismatch problems with fewer steps and with no chance of deleting objects on the remote server.

Index Is Added or Dropped Outside Component Integration Services

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
```
The object was defined via the create existing table command, the following message is returned in the result set:
```
Object created with 'existing' option
```
This message is not displayed, the object was defined via create table.
The create existing table command was used to create the object:
1. Use drop table in Component Integration Services.
2. Re-create the table in Component Integration Services using create existing table. This updates the indexes to match the indexes on the remote table.
3. If create table was used to create the object:
  1. Use drop index to drop the index from the remote table.
  2. 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:
1. Turn on trace flag 11208:
```
dbcc traceon(11208) 
```
2. Create the index using create index.
3. Turn off trace flag 11208:
```
dbcc traceoff(11208)
```