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?

asked 26 Mar '13, 06:00

Volker%20Barth's gravatar image

Volker Barth
30.6k305456662
accept rate: 32%

edited 26 Mar '13, 06:08


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.

permanent link

answered 27 Mar '13, 09:27

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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...

(27 Mar '13, 10:15) Volker Barth
Replies hidden

...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...

(27 Mar '13, 10:20) Volker Barth
1

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.

(27 Mar '13, 13:02) Karim Khamis

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.

permanent link

answered 26 Mar '13, 08:59

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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?

(26 Mar '13, 11:34) Volker Barth
Replies hidden

@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...)

(26 Mar '13, 11:36) Volker Barth

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

(27 Mar '13, 09:24) Karim Khamis

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

(28 Mar '13, 04:34) Volker Barth

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'
permanent link

answered 27 Mar '13, 17:51

JBSchueler's gravatar image

JBSchueler
2.0k2837
accept rate: 15%

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.

(27 Mar '13, 18:39) Volker Barth
Replies hidden

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.

(28 Mar '13, 04:29) Volker Barth

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...

(28 Mar '13, 04:39) Volker Barth

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.

(28 Mar '13, 08:37) Karim Khamis

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...

(28 Mar '13, 09:02) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×63
×46
×28

question asked: 26 Mar '13, 06:00

question was seen: 3,167 times

last updated: 28 Mar '13, 09:02