What might prevent a MS SQL 2008 R2 remote server to list any table's primary keys?

[VolkerBarth]

I'm using a 12.0.1.3817 database as a proxy to a MS SQL 2008 R2 database. When using CREATE EXISTING TABLE, the proxy tables are basically created as desired.

However, they miss the primary key information of the according columns, though the MSQ SQL tables surely have declared PKs columns, and these are correctly listed as PRIMARY KEY constraints with sp_help under MS SQL.

I guess this might correspond to the fact that sp_remote_primary_keys does not list any primary key for those tables, either. - In contrast, when I use remote servers to SQL Anywhere databases, their PKs are correctly shown.

Question: Is that a knwon limitation when using the 'mssodbc' remote server class?

If have tested ODBC connections with the same results both with

• the classic SQL Server driver (SQLSVR32.DLL 2000.85.1132.00)
• the SQL Server Native Client 10.0 driver (SQLNCLI10.DLL 2009.100.1617.00)

The DBMS is "Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)".

---

Just to add: CREATE EXISTING TABLE does create an unique index for any PK. - I guess that's because MS SQL Server does so, too, and obviously these index information is imported.

---

According console log snippet by using CIS_OPTION = 7:

Executing SQLTables(MS_TEST, dbo, MyMsTable)
Found table 'MyMsTable', owned by 'dbo', qualified by 'MS_TEST'
Executing SQLColumns(MS_TEST, dbo, MyMsTable)
Executing SQLStatistics(MS_TEST, dbo, MyMsTable)
CREATE EXISTING TABLE "T_MyMsTable" ( ... ) AT 'SVR_MS_TEST.MS_TEST.dbo.MyMsTable'
LOCAL ONLY
Executing SQLStatistics(MS_TEST, dbo, MyMsTable)
CREATE UNIQUE INDEX "PK_MyMsTable" ON ...

May it be there is a call to the ODBC API SQLPrimaryKeys() missing here?

[Former Member · Accepted solution]

I will open a bug report and see if we can glean the primary key information for an MS SQL Server (and probably MS Access as well) a different way. The standard approach is to use SQLPrimaryKeys() but it looks like the MSS ODBC driver may not be returning that information correctly. It should be noted that primary key and index information for a proxy table is for information purposes only. The SA server does not make use of any of that information when the underlying table is a proxy table.

[VolkerBarth · Accepted solution]

Ah, interesting to know:

So even declaring an index on a proxy table does not influence the way the queries are re-written before they are sent to the remote server?

If so, and if primary key declarations for proxy tables do not matter at all, then I would think that my underlying question is rather moot...

[VolkerBarth · Accepted solution]

...And if so, I would then dare to ask if the topic "updated capabilities" (see my older FAQ) could get more attraction than the PK topic...

[Former Member · Accepted solution]

Yes, I know the "updated capabilities" topic has been around for a while and we are working on getting that information put together and updated, but it is taking longer than expected.

Just to complete the thought regarding primary key and index information on proxy table, you might ask "if the information is not being used by SA, then why pull that information over at all?" The answer is that while the SA optimizer does not utilize that information, the migration scripts do use that information to try and create the same set of primary keys and indexes on the migrated base table.

[jack_schueler]

I gave this a whirl and it works for me using either driver and SQL Anywhere 16.

I am using 64-bit SQL Server 2008 version 10.0.1600.22.

CREATE TABLE alltypes
( 
  primary_key TIMESTAMP PRIMARY KEY,
  char_t CHAR,
  character_t CHARACTER(15),
  character_varying_t CHARACTER VARYING(1092),
  varchar_t VARCHAR(1092),
  decimal_t DECIMAL(30), 
  float_t FLOAT,
  int_t INT,
.
.
.
CREATE SERVER mysqlserver
CLASS 'MSSODBC'
USING 'DSN=MSSODBC';

CREATE EXISTING TABLE ms_alltypes
AT 'mysqlserver.master.dbo.alltypes';

select * from sp_remote_primary_keys( 'mysqlserver', 'alltypes', 'dbo', 'master' );

database,owner,table_name,column_name,key_seq,pk_name
'master','dbo','alltypes','primary_key',1,'PK__alltypes__2F603E9F3AA1AEB8'

[VolkerBarth]

Thanks for the sample! I used it with my configuration (i.e. same SA version, same MS SQL server and same ODBC driver and DSN) - only within a different SQL Anywhere 12 database.

And it also does show the correct PK... - so now I'm puzzled why I've had different results yesterday.... I guess I will have to re-do my tests. Note, I've done some several times (with different DBISQL/dbisqlc sessions) yesterday before I've raised the question, so I'm curious what might have been wrong.

[VolkerBarth]

Interesting observation:

When re-running my tests, I still got an empty result set. But I noticed that I've omitted the owner and schema name in the sp_remote_primary_keys() and simply supplied NULLs here.

When adding these, I get the desired result. (It seems to be sufficient to add the 'dbo' owner in my case, as the schema name itself is specified as default database for that MSSQL DSN.)

The irritating thing is that with sp_remote_columns(), I get the same result whether I specify owner and schema or not. So that seems to be handled differently between these procedures, though the docs seem to describe their parameters as somewhat identical.

---

However, even if sp_remote_primary_keys() now does list the PKs correctly, they are still not added to the definition of the proxy table, i.e. the following catalog query still doesn't show any PK column:

select * from syscolumn key join systable
where table_name = 'ms_alltypes' and pkey = 1;

Note that I've always specified both schema and owner name in the CREATE EXISTING TABLE remote location, just as in your sample.

---

Aside: Though the missing PK definition for the proxy table doesn't have any negative effect on query execution, as Karim has explained, so that's alright with me.

[VolkerBarth]

I should add that the local user is mapped to the remote user (i.e. MS SQL login) "sa" which is mapped to the user "dbo" within that MS SQL database and is the owner of its default schema, so omitting the owner name should work IMHO here...

[Former Member]

When creating the proxy table, add the catalog and owner information in the AT clause. That will probably get the correct key information for you. As for what should work and what should not, now that I have a better understanding of what is going wrong (thanks for the follow-up Jack), I can see that certain metadata calls for MS SQL Server (like SQLPrimaryKeys) require the catalog and owner and other catalog calls (like SQLColumns) "sometimes" manage without that information. I will therefore chalk it up the the underlying driver.

[VolkerBarth]

Note that I've always specified both schema and owner name in the CREATE EXISTING TABLE remote location, just as in your sample.

So within the AT clause, I've always filled all 4 parts, i.e. used AT 'SVR_MS.MyDatabase.dbo.MyTable', therefore that should not have been the problem...

[Former Member]

It is true that we do not log the SQLPrimaryKeys() call in the console log (we probably should), but the call is being made. I think the ODBC driver is just not returning that information. If you turn on ODBC tracing, I think you will see that the SQLPrimaryKeys() call gets made with no information coming back on the subsequent SQLFetch() call.

[VolkerBarth]

Hmm, so far my ODBC trace attempts have been useless - when using a fresh instance of dbisqlc or DBISQL to just issue a CREATE EXISTING TABLE statement, the trace just logs the activities of the ODBC DSN queries (issued by the connect dialog, AFAIK), the OMNI queries seem to be ignored - even if I turn on the system-wide tracing. (And as both tools itself don't use ODBC, I can understand that their own queries are not traced, either...

I have closed all remote connections beforehand. Do I need to restart the SA server, too, to trace its OMNI queries?

[VolkerBarth]

@Karim: Are you aware that these MS SQL drivers generally might not return PK information? (I do not think we have configured anything "special" on the MS side...)

[Former Member]

Yes, you do need to restart the server in most cases.

[VolkerBarth]

OK, I've done that now and can confirm that SQLPrimaryKeys() is called as desired.