cancel
Showing results for 
Search instead for 
Did you mean: 

Can I modify the remote table a proxy table is linked to at runtime?

VolkerBarth
Contributor
2,920

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

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor
0 Kudos

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

Answers (1)

Answers (1)

Breck_Carter
Participant

Does this article help?

VolkerBarth
Contributor
0 Kudos

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!