...i.e. without to drop and re-create the proxy table?

That's what I would like to do:

Define a set of proxy tables with connection variables so that a user can modify these variables and then access data from different remote databases, say for testing vs. production data. Any user would only need to access one remote database at any time but possibly User1 would need to access RemoteDb1 while User2 would need to access RemoteDb2 at the same time.

The type and schema of the remote databases and the credentials are identical, but server name and database name are different. (These are MS SQL Server 2014 databases.)

Of course I could use several sets of CREATE SERVER and CREATE EXISTING TABLE statements for all remote databases and then use views or the like to select from the desired data. However, I hope I can come up with just one set of proxy tables.

I tried to use the CREATE SERVER statement with a variable, and that seems to works fine. When different connections use different values for the according connection variable, they access different databases. Here's a short sample:

create variable varServerName varchar(255);
create variable varDbName varchar(255);

create server MS_VAR_TEST
class 'mssodbc'
using 'Driver=SQL Server Native Client 10.0;Server={varServerName};Database={varDbName};Trusted_Connection=yes;'
read only;

-- when using different values for varServerName and/or varDbName,
-- that will issue different results
forward to MS_VAR_TEST;
 select @@servername, db_name();
forward to;

However, if I try to use variables within the CREATE EXISTING TABLE statement, that does not seem to work:

create existing table dbo.MyProxy at 'MS_VAR_TEST.{varDbName}.dbo.MyTable';

Here, the variable seems to be calculated when the proxy table is created, so a later modification of the variable will not be effective (whereas the modification of the server component is effective.

So is that a limitation of proxy tables, or do I have missed a point?

asked 04 Mar '16, 12:02

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

Hm, possibly that's a clue:

The v16 docs on the CREATE EXISTING TABLE statement tell that

The string in the AT clause can also contain local or global variable names enclosed in braces ({variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, an AT clause that contains 'access;{@myfile};;a1' indicates that @myfile is a SQL variable and that the current contents of the @myfile variable should be substituted when the proxy table is created.

So the variable seems to be evaluated at creation time, not at access time which I were looking for. (BTW: The v16 docs correct the syntax for using variables within the location-string whereas v12 shows an "AT variable-name" clause variant which seems to be invalid.)

(04 Mar '16, 15:41) Volker Barth
Replies hidden

FWIW, in my actual case I could work around the problem by omitting the database name part within the location-string, such as:

create existing table dbo.MyProxy at 'MS_VAR_TEST..dbo.MyTable';

Well, this requires that there is only one database with the named schema and table within that server, otherwise the CREATE statement fails. However, if it could be created, it even seems to work against remote servers containing several databases with the named schema and table - it seems the database part is taken from the CREATE SERVER variable as desired.

(04 Mar '16, 15:50) Volker Barth

That conclusion has been proved wrong, see my answer.

(07 Mar '16, 04:24) Volker Barth

After some more testing I finally got it to work, and apparently the variables in the proxy table's location-string do get evaluated at runtime, so the following does work as desired (contrary to what I had stated in the question):

create existing table dbo.MyProxy at 'MS_VAR_TEST.{varDbName}.dbo.MyTable';

In my tests, the relevant piece was to the following:

When switching the remote database within a connection by modifying the according connection-level variables, it seems necessary to explicitly drop the remote connection (via ALTER SERVER ... CONNECTION CLOSE CURRENT in v12), otherwise the currently open remote connection seems to be cached and remains open although the variables have been changed, and so the remote connection tries to access a table in a database (specified by the new variable's values) which does not exist on that remote server (still specified by the former variables's values)...

permanent link

answered 04 Mar '16, 17:37

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

edited 07 Mar '16, 04:22

Does this article help?

permanent link

answered 04 Mar '16, 16:06

Breck%20Carter's gravatar image

Breck Carter
27.0k424582831
accept rate: 21%

edited 06 Mar '16, 15:15

A worthy hint, as usual! (And I feel somewhat dumb that I searched within the forum but ignored the great blog...)

I guess the particular problem (related to different databases under one MS SQL server) isn't covered in your sample as SQL Anywhere as a remote database does not support that "database name" part of the location-string. However, I guess I got a further hint by being reminded of the sp_forward_to_remote_server system procedure... - possibly I could do without proxy tables at all. We'll see.

So, thanks again, Breck!

(04 Mar '16, 16:39) 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:

×62
×60
×26

question asked: 04 Mar '16, 12:02

question was seen: 301 times

last updated: 07 Mar '16, 04:24