using sql server 2005 synonyms for table causes dynamic query generation errors

Question

Hi,

I have a situation where by i have a catalog on Server A, and a same named Catalog on Server B. I'm using synonyms on Server B so that some tables are actually just going through to server A.

Such that, calling select * from ServerB.Table, is actually returning Data from ServerA.Table transparently. This seems to work fine when i'm doing selects, and also works fine if i hand tsql script inserts, however, when i use the ADO.NET adapter it gives me an error saying " Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." Which typically means there is no primary key information being made available when the data is returned. Is there any way around this? (With me preferably being able to continue to use the synonym, as it is not really an option to exclude its use).

I know that there is an "ID" column that is always the primary key, and if necessary i can hard code the primary key information back into the ado.net adapter / datatable if required.

Thanks!

Answer 1

You are using SQLCommandBuilder to obtain the SQL for data modification operations.

Since no metadata are returned by the remote table, the command builder cannot generate the appropriate SQL for updating or deleting from the table because it does not know which field to use as a PK.

You should provide your own UpdateCommand and DeleteCommand for your data adapter.

See this link for the code.