I'm using a 126.96.36.19917 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 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:
May it be there is a call to the ODBC API SQLPrimaryKeys() missing here?
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.
answered 27 Mar '13, 09:27
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.
answered 26 Mar '13, 08:59
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'
answered 27 Mar '13, 17:51