In order to compare dev and prod databases, I'd like to join SYSOBJECTS and SYSCOLUMNS tables of two servers. However the documentation says 'you cannot create proxies of system tables'. Is there a way to do what I want without creating proxy tables? I'm relatively new to Sybase ASA, so all suggestions/directions are welcome.

Note: a related but not duplicate question

Edit: The server version is 9. I thought I added that, I feel like there's a problem with revisions but I'll post that as another thread.

asked 17 May '11, 09:59

henginy's gravatar image

henginy
406151827
accept rate: 0%

edited 18 May '11, 05:21

2

The documentation is incorrect or unclear. The migration scripts will not migrate system tables (for obvious reasons) but as Volker points out, you can definitely create proxy tables to system tables. Can you please give a link as to where in the documentation you read about not being able to create proxy tables to system tables. I will then have the documentation team update that particular section.

(17 May '11, 10:33) Karim Khamis
Replies hidden
1

Thanks for the answer. Here's the link:

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/pdf/dbugen9.pdf

And the content (p.615):

Creating proxy tables (Sybase Central) You can create a proxy table using either Sybase Central or a CREATE EXISTING TABLE statement. You cannot create proxy tables for system tables.

(18 May '11, 04:36) henginy

Ah, and it's still documented that way with the current 12.0.1 release - cf. the DCX page...

Though this may be documented correctly:

"CREATE TABLE AT ... " won't work with system tables, as they will already exist and cannot be created on the remote side. Only "CREATE EXISTING TABLE" does work. But still misunderstandable, methinks:)

(18 May '11, 05:27) Volker Barth
Comment Text Removed
Comment Text Removed

Okay, this documentation is actually correct. You can create proxy tables to system tables in general using SQL similar to the one that Volker provided below, but, Sybase Central explicitly weeds out system tables when it presents you with a list of remote tables. So yes, you can create proxy tables to system tables by explicitly using the CREATE EXISTING TABLE statement, but you cannot create proxy tables to system tables using Sybase Central. This section of the documentation deals with creating proxy tables using Sybase Central. I will ask the documentation team to clarify that the "no system tables" restriction applies to Sybase Central only and not to the CREATE EXISTING TABLE statement.

(18 May '11, 07:43) Karim Khamis

ha ha... saying 'you cannot create proxies of system tables' is the same as saying 'Foxhound does not exist' :)

(18 May '11, 07:53) Breck Carter
Replies hidden

So you've not used Sybase Central to "click'n'build" Foxhound:)

Breck a "script kid"?

Me, too.

(18 May '11, 08:03) Volker Barth
showing 2 of 6 show all flat view

It surely is possible to access to system catalog of a different SA database through proxy tables. Note, however, as you are relating to already existing tables/views, you need to use the CREATE EXISTING TABLE syntax.

The following shows how to access the systable system view of the SA12 demo database:

create server SVR_SA_DEMO class 'saodbc' using 'SQL Anywhere 12 Demo';
create externlogin "DBA" to SVR_SA_DEMO remote login "DBA" identified by 'sql';
create existing table DEMO_SysTable at 'SVR_SA_DEMO..sys.systable';
select * from DEMO_SysTable order by table_name;

For obvious reasons, the following will fail with missing permissions:

update DEMO_SysTable set table_name = 'test';

The following will show all tables/view contained in the current, but not in the demo database:

select table_name from sys.systable
except
select table_name from DEMO_SysTable
order by 1
permanent link

answered 17 May '11, 10:22

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 17 May '11, 10:41

Thanks, it works!

(18 May '11, 04:37) henginy
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:

×70
×32

question asked: 17 May '11, 09:59

question was seen: 3,062 times

last updated: 18 May '11, 08:05