Having successfully connected to an MS Access database via ODBC, I am now trying to make the process of making proxy tables even faster.

How do you query a remote server (any ODBC remote server already defined at this point) for a list of tables? I want to use the list to run a list of statements against to create proxy tables?

I used the Migrate server commands successfully for the most part, but they run into trouble if you have any tables with the same name. Also, I may be getting different versions of the same file and it would be useful to increment them somehow, probably using something attached to the tablename such as S1TableName, S2TableName etc. or TableNameS1, TableNameS2 etc. for different sources of the same table.

I tried looking at the transaction log to see what SQL is run when Sybase Central pulls the list, but it looks like that is coded within the program because there were no applicable statements in the transaction log.

I tried this statement:

FORWARD TO Testing { SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0};

and I get Record(s) cannot be read; no read permission on 'MSysObjects'.

Google tells me that for that error message you need to open Access and change the security on that table, but the whole point of the exercise is to be able to convert a file using the remote db without having to open it in Access. The permissions sure aren't keeping Sybase Central from getting a table list because that's what it shows when you try to use the wizard to create a proxy table: a list of available tables.

I appreciate all the help.

asked 26 Feb '13, 21:30

Siger%20Matt's gravatar image

Siger Matt
3.2k516998
accept rate: 13%

edited 15 Mar '13, 20:39

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


Try using sp_remote_tables( ... ) - see the docs for the parameters (e.g. first parameter is the proxy server name)


FWIW the transaction log only records the changes made to the database. The SQL that Sybase Central uses to query the database will not appear in the transaction log because these SQL queries do not update the database. In the future you could consider using Request Logging by using the -zr and -zo server command line switches to see what SQL statements are being sent to the server from any client - see the docs for more information.

permanent link

answered 26 Feb '13, 22:19

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

edited 26 Feb '13, 22:24

Excellent. Thanks Mark!

(26 Feb '13, 23:44) Siger Matt
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:

×126
×48
×3

question asked: 26 Feb '13, 21:30

question was seen: 1,346 times

last updated: 15 Mar '13, 20:39