The error messages would tend to suggest that you've got a mismatch in the columns defined for the proxy table and the input-parameters/output-columns defined for the stored proc. ("Duh, Mark!" ?)

While you mention that you've verified the columns/parameters (and the example code you've provided is correct - see my results, below), I'm wondering if you've got multiple/different copies of the proxy table and/or proc floating around your systems. In other words:

- Is 'loopback' pointing where you think it's pointing?

- Does the proxy's remote object definition match exactly (db name, owner, proc name) with the version of the proc you think you're running?

I'd suggest you double check the database name where the proc and proxy table are defined, verify you're accessing the proxy table from the correct database, verify the owner and proc names (does 'sp_help' show only one copy/version), and verify the column/parameter list for your proxy table and proc.

Also, if you run the proc (as referenced by the proxy table definition) directly (eg, exec rtm.dbo.crm_top_client_contact), do you get the desired result set? (4 columns? 5 columns?)

-----------------

My ASE version: Adaptive Server Enterprise/15.7/EBF 26393 SMP SP138/P/Solaris AMD64/OS 5.10/ase157sp138x/4002/64-bit/FBO/Mon Aug 29 21:14:14 2016

I created the table 'testx.dbo.contact' with the same columns referenced in your stored proc; then I added a couple rows for client in (1,2).

I created the stored proc 'testx.dbo.crm_top_client_contact' with the same code you've provided. [NOTE: I'm running a case sensitive dataserver so replaced 'Name' with 'name'.]

In the same dataserver I created the proxy table 'tempdb.dbo.crm_top_client_contact_rpc' with the same exact code you've provided.

I verified that 'loopback' references @@servername; I also verified that 'exec loopback...sp_who' generates the same results as running 'sp_who'.

I verified the stored proc parameter definitions (output cropped for readability):

use testx
go
sp_help crm_top_client_contact
go
 Name                   Owner Object_type
 ---------------------- ----- ----------------
 crm_top_client_contact dbo   stored procedure

 Parameter_name Type Length Prec Scale Param_order Mode
 -------------- ---- ------ ---- ----- ----------- ----
 @client        int       4 NULL  NULL           1 in

I also verified the proxy table's column definitions (output cropped for readability):

use testx
go
sp_help crm_top_client_contact_rpc
go
 Name                     Owner   Object
 ------------------------ ------- ---------
 crm_top_client_contact_rpc  dbo  user table 

 Column_name Type    Length Prec Scale Nulls
 ----------- ------- ------ ---- ----- -----
 ctname      varchar     76 NULL  NULL     0
 ctphone     varchar     16 NULL  NULL     0
 ctemail     varchar     80 NULL  NULL     0
 ctclient    int          4 NULL  NULL     0
 _client     int          4 NULL  NULL     1

Object is Remote/External
-------------------------
loopback.testx.dbo.crm_top_client_contact

When I run the following queries against the proxy table I get the desired results (output compacted for readability):

SELECT * FROM crm_top_client_contact_rpc
go
 ctname          ctphone       ctemail     ctclient _client
 --------------- ------------- ----------- -------- -------
(0 rows affected)

SELECT * FROM crm_top_client_contact_rpc WHERE _client= 1
go
 ctname          ctphone      ctemail     ctclient _client
 --------------- ------------ ----------- -------- -------
 bob bob roberts 123-456-7890 me@emai.com        1       1
(1 row affected)

No particular reason for using tempdb other than a) testx was the only user database on this dataserver not participating in some sort of replication (ie, didn't want hassles with data/procs getting replicated elsewhere) and b) just chose to put the proxy in a different database from the proc (sure, proxy could be in the same db with proc).

If I create the proxy in the my testx database I get the same results as mentioned in my previous post.

--------------

I can't tell from your last post if you've manually traced your proxy's definition so fwiw ...

Is your proxy in the same database with the proc? If not, is the proxy in the same dataserver?

Is there just the one version of the proxy? (Does sp_help crm_top_client_contact_rpc show just one record in the database where the proxy's defined?) [Want to rule out the possibility that there's more than 1 version of the proxy; for example, one copy owned by 'dbo' and another copy owned by 'daveb'.]

What's the output from running sp_help crm_top_client_contact_rpc in the database where the proxy is defined?

If the proxy's object is 'rtm.dbo.crm_top_client_contact', what is the source text for this proc? (use rtm ; sp_helptext 'dbo.crm_top_client_contact',null,null,showsql)

And while we're at it, what's your ASE version? (select @@version)

Hi Mark, thank you for taking a look at this. The target Stored Procedure returns 4 columns when executed directly:

exec crm_top_client_contact(20127910)

ctname,ctphone,ctemail,ctclient
'Ian Craig','08451202734','icr@email.com',20127910

Is there a reason you created the proxy table in "tempdb" rather than "testx" (or "rtm" in my dataserver)?

The contact table, stored procedure and proxy table are all in the same database "rtm".

Everything is owned by user "dbo", There are only 2 other users and neither own any objects.

Proxy table:

Name                       Owner Object_type Object_status        Create_date         
-------------------------- ----- ----------- -------------------- ------------------- 
crm_top_client_contact_rpc dbo   user table  keep first text page Feb 15 2017 10:39PM 

Column_name  Type    Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity   
------------ ------- ------ ---- ----- ----- -------------- ------------ --------- ---------------- ---------------------- ---------- 
    ctname   varchar     76 NULL  NULL     0              0 (NULL)       (NULL)    (NULL)           (NULL)                          0 
    ctphone  varchar     16 NULL  NULL     0              0 (NULL)       (NULL)    (NULL)           (NULL)                          0 
    ctemail  varchar     80 NULL  NULL     0              0 (NULL)       (NULL)    (NULL)           (NULL)                          0 
    ctclient int          4 NULL  NULL     0              0 (NULL)       (NULL)    (NULL)           (NULL)                          0 
    _client  int          4 NULL  NULL     1              0 (NULL)       (NULL)    (NULL)           (NULL)                          0 
 
Object is Remote/External
-------------------------
loopback.rtm.dbo.crm_top_client_contact
 
Object created with 'existing' option
 
Object does not have any indexes.
No defined keys for this object.

name                       type       partition_type partitions partition_keys 
-------------------------- ---------- -------------- ---------- -------------- 
crm_top_client_contact_rpc base table roundrobin              1 (NULL)         
 

partition_name                        partition_id compression_level pages row_count segment create_date         
------------------------------------- ------------ ----------------- ----- --------- ------- ------------------- 
crm_top_client_contact_rpc_1446449346   1446449346 none                  0         0 default Feb 15 2017 10:39PM 
 

Partition_Conditions 
-------------------- 
(NULL)               
 

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)                                                                        Ratio(Min/Avg)                                                                        
----------- ----------- ----------- ------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- 
0           0           0           0.0                                                                                   0.0                                                                                   
Table LOB compression level 0
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.
 

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts  
------------ -------------- ---------- ----------------- ------------ ----------- 
0            0              0          0                 0            0           

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg cached_index_root_page 
------------------------- --------------------- ------------------- ---------------------- 
0                         0                     0                   0                      
Execution time: 0.19 seconds

Proxy table rtm.dbo.crm_top_client_contact_rpc DDL:

create existing table crm_top_client_contact_rpc (
     ctname                      varchar(76)                            ,
     ctphone                     varchar(16)                            ,
     ctemail                     varchar(80)                            ,
     ctclient                    int                                    ,
     _client                     int                                  null   
) on 'default'
  external procedure
  at 'loopback.rtm.dbo.crm_top_client_contact'

Stored procedure rtm.dbo.crm_top_client_contact DDL:

create procedure dbo.crm_top_client_contact
 @client int = NULL
as
begin
     SELECT TOP 1
         contact.firstname + ' ' + contact.name AS ctname, 
         contact.phoneusercode AS ctphone, 
         contact.emailaddress AS ctemail, 
         contact.client AS ctclient
     FROM contact
     WHERE contact.client=@client AND contact.active=1
     ORDER BY
       contact.primarycont DESC, 
       ctname ASC
end

ASE version is:

Adaptive Server Enterprise/16.0 SP02 PL02/EBF 25319 SMP/P/X64/Windows Server/ase160sp02plx/0/64-bit/FBO/Sun Nov 22 05:16:54 2015

Well that seems to have put the cat amongst the pigeons!

I was using the Interactive SQL tool that comes with Sybase Central.

I recreated the stored procedure and the proxy-table via isql in a command prompt session and now it works. Even if I select columns from the proxy table in Interactive SQL, that now works also. It would seem there is a problem with creating proxy tables via the Interactive SQL app.

1> select * from crm_top_client_contact_rpc where _client=20127910
2> go
 ctname         ctphone         ctemail        ctclient    _client
 -------------- --------------- -------------- ----------- ----------
 Adrian Pike    01714534945     api@email.com  20127910    20127910
(1 row affected)

Many, many thanks for sticking with this one, Mark, I'm not sure I would have found the problem without your lengthy troubleshooting.