RDA with Linked Servers

I am developing a WM5 program (C#.NET) using SQL Server Mobile, using RDA to pull and push data from/to SQL2005.

The question/issue is this: Does RDA work with linked servers, more specifically, if the RDA table is an Oracle table accessed through a linked server? I have a linked server defined in SQL2005, using the .NET Oracle provider (MSDAORA) linked to Oracle 10g. When I try to perform an RDA PULL operation from Windows Mobile 5 (Smartphone emulator) I get the following error:

“The query cannot be tracked. There might not be a primary key, or the query might involve multiple tables. [ Query string = SELECT * FROM DATABASE_NAME..SCHEMA_NAME.TABLE_NAME ]”

I have used at least 3 different test tables in Oracle, trying different options. The error message above implies that the table may not have a primary key but it does (constraint and index) and I have tried a few options with this. The message also implies that multiple tables may be involved but this is not the case either. I have tried variations on the select statement including specifying the columns. I tried building the CREATE TABLE statements with the PK using SQLServer to generate the SQL DDL to see if SQLServer might build the table, constraint, index with options the way it wants to see them – same result.

I can run most any queries (select, insert, etc.) from SQLServer using the linked server and the queries work. The only problems involve RDA to the linked server.

I tried turning tracking off on RDA and was able to pull the entire table from the linked server. However, RDA requires that you can only push a tracked table (change tracking on). I tried to push a non-tracked table and it fails.

Make sure that the table you are referring to is under DBO schema. If it is under any other schema, then RDA might be encountering problem in finding the Primary Key for that table.

Let me know the details after your trials!

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

All of the objects in the SQL2005 database are in the dbo schema. However, the query from RDA pull references an Oracle table in a named schema, using 4-part name notation "SELECT * FROM DATABASE_NAME..SCHEMA_NAME.TABLE_NAME " per the SQL2005 distributed query guidelines. In this case, the credentials for the linked server Oracle access are for the owner of the named schema.

Thanks,

MattR-KS

|||

Hi Matt,

Just to narrow down the problem. What happens when you are pulling a table in a named schema from SQL Server than from oracle via linked SQL Server. What I mean is have the table in a name schema in SQL Server itself and do a RDA with that.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation