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?
6 Answers
-
Best AnswerFeb 16, 2017 at 09:22 AM
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.
--------------
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.]
Add comment
-
Feb 15, 2017 at 05:06 PM
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)
Add comment
-
Former MemberFeb 15, 2017 at 09:23 PMHi 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)?
Add comment
-
Feb 15, 2017 at 09:53 PM
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)
Add comment
-
Former MemberFeb 15, 2017 at 11:03 PM
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 secondsProxy 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 endASE 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
Add comment
-
Former MemberFeb 16, 2017 at 01:24 PM
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.
Add comment
Add comment