• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

proxy tables

Hi,
I know that we can have one proxy table mapped to one stored procedure on a remote server. But let's say there are multiple proxy tables that we would like to map to that one stored procedure. Is there a way to do that?
Thanks
4learning
0
4Learning
Asked:
4Learning
  • 2
  • 2
1 Solution
 
mrjoltcolaCommented:
I've never tried, but from what I know of how proxy tables work on Sybase, you should be able to create any number of proxy tables to the same source table. Just try the syntax you'd normally use, with 2 different names for the proxy tables.

create existing table proxytab1
   (name varchar(10))
external procedure at 'wherever'
go

create existing table proxytab2
   (name varchar(10))
external procedure at 'wherever'
go


Does that not work for you?
0
 
4LearningAuthor Commented:
The problem is the result set returned by the stored procedure MUST match the columns in the local (proxy) tables. And since there are  different tables with different column names of which we want to create proxy tables, we cannot match it in the stored procedure.


Eg
remote server-
original_table1
      col1 varchar(10)
original table2
      newid int

create stored procedure sp1
as
select col1 from original_table1

local server-
create existing table proxy_table1
(
col1 varchar(10)
)
external procedure at "remoteserver.dbname..sp1"

create existing table proxy_table2
(
newid int
)
external procedure at "remoteserver.dbname..sp1"

In the example above, the design of the stored procedure is incorrect, since it is returning only one varchar column, whereas we are trying to map two tables to it. How will we modify the stored procedure so that it can return columns for both the proxy tables on the remote server?
0
 
mrjoltcolaCommented:
It sounds like the wrong way to approach it. The structure needs to match the sp.

I would suggest just create one proxy table, then create 2 different local views against it.
0
 
4LearningAuthor Commented:
yes, I think that works. just that the views on that one proxy table are getting a little complicated since the data in now not normalised when we modify the sp.
I may try to think of another approach, but as far as the question goes, your suggestion is thesolution.
Thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now