Skip to Content
0

How to create a proxy table for a remote procedure in Sybase ASE

Feb 15, 2017 at 04:21 PM

441

avatar image

I am currently trying to create a proxy table for a stored procedure and am unable to get it working.

The stored procedure is:

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, 
     Name ASC
end

the proxy table declaration is:

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'

but when running:

SELECT * FROM crm_top_client_contact_rpc 

I get an error:

Column count mismatch in remote object 'crm_top_client_contact_rpc';
(defined: 5 found 4).
Sybase error code=11213


and when specifying a parameter thus:

SELECT * FROM crm_top_client_contact_rpc WHERE _client=20127910 

I get the error:

Invalid column name '_client'
Sybase error code=207

I have confirmed that the datatypes between the Stored Procedure and the proxy table are a match. I can't see anything that doesn't match the examples in the Sybase ASE online documentation. What have I missed?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Mark A Parsons Feb 16, 2017 at 09:22 AM
1

I re-ran my SQL script against a 16.0 SP02 PL04 dataserver; even went so far as to create a database named 'rtm'; all objects reside in the 'rtm' database.

Attached is the isql/-e results from running my SQL script.

proxy-test.txt

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

One item of concern ... in your example stored proc call you're using invalid syntax.

In my SQL script I ran the stored proc call twice ... once with your invalid, error-generating syntax ... once with valid syntax:

exec crm_top_client_contact(20127910)

Msg 102, Level 15, State 181:
Server 'ASE101', Line 4:
Incorrect syntax near '20127910'.

exec crm_top_client_contact 20127910

-- proc executes as expected and returns a single row

The reason I bring this up ... if you didn't generate an error when submitting the invalid syntax then I'm guessing you're running your tests with something other than 'isql'. If this is the case, what happens if you run your proc/proxy queries via the 'isql' command line tool? [Want to rule out any problems that could be related to the client application.]


proxy-test.txt (18.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Feb 15, 2017 at 05:06 PM
0

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)
Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Feb 15, 2017 at 09:53 PM
0

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)

Share
10 |10000 characters needed characters left characters exceeded
Dave Boulden Feb 15, 2017 at 09:23 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dave Boulden Feb 15, 2017 at 11:03 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Dave Boulden Feb 16, 2017 at 01:24 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Glad I could help, and apologies for taking so long to get to the root of the issue ... I work almost solely from the command line (via isql) primarily so I don't run into this kind of GUI-related crap, hence I tend to forget to ask upfront about the client/application in use ... will have to add that to my standard list of annoying questions eh! :-)

1