Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

How can I pass parameters to a remote MS SQL Server procedure? I can't seem to get it work.

Probably missing something, but I can't find what.

Remote procedure in Sybase:

CREATE PROCEDURE usr.TF2_DetermineNetworkAreaId(in in_transportTypeId int,in in_CountryCode varchar(3),in in_ZipCode varchar(10)) at 'Quote2;quote;dbo;DetermineNetworkAreaId';

Procedure in SQL Server:

    CREATE FUNCTION dbo.DetermineNetworkAreaId
    (   @TransportTypeId int,
        @CountryCode nvarchar(3),
        @ZipCode nvarchar(10))
    RETURNS int
      declare @RegionId int
      set @RegionId = (select 
                          top 1 RegionId
                          dbo.Region join
                          dbo.RegionAreas on RegionAreas.RegionId = Region.Id join
                          dbo.Country on Country.Id = RegionAreas.CountryId
                          Region.TransportTypeId = @TransportTypeId and
                          (RegionAreas.Exclude = 0 and ((Country.Code = @CountryCode and RegionAreas.ZipCode = substring(@ZipCode,0,LEN(RegionAreas.ZipCode))) or
                                                        (Country.Code = @CountryCode and RegionAreas.ZipCode = ''))
                          ) and 
                          not exists(select 1 
                                     from dbo.RegionAreas RA join
                                        dbo.Country C on RA.countryId = C.Id
                                     where RegionId = RA.RegionId and 
                                           Exclude = 1 and ((C.Code = @CountryCode and RA.ZipCode = substring(@ZipCode,0,LEN(RA.ZipCode))) or
                                                            (C.Code = @CountryCode and RA.ZipCode = ''))
                        order by Country.Code desc, RegionAreas.ZipCode desc, RegionId desc)
      return @RegionId                    

When calling "select "USR"."TF2_DetermineNetworkAreaId"(204,'NLD','')" in I-SQL I get a result 0
When calling the same procedure in SQL Server the result is 40



asked 31 Mar '14, 10:49

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 20%

edited 31 Mar '14, 10:53

Mark%20Culp's gravatar image

Mark Culp


Wouldn't it be more appropriate to declare that as a function in SQL Anywhere, too - you only use IN parameters and get a return value...

BTW: What SQL Anywhere version do you use?

(31 Mar '14, 10:54) Volker Barth

SQL Anywhere version 12.0.1 build 3976.

I can't get the procedure this way working in Sybase with proxy tables to the SQL server database because the exists() produces an error. The error I receive from SQL Server is:

Connection is busy with results for another hstmt

(31 Mar '14, 11:25) Frank Vestjens
Replies hidden

Sorry, my suggestion was not to port the SQL code to SQL Anywhere (via using proxy tables) but to declare the external procedure as an external function instead, such as:

CREATE FUNCTION usr.TF2_DetermineNetworkAreaId(
  in_transportTypeId int, in_CountryCode varchar(3), in_ZipCode varchar(10))
  AT 'Quote2;quote;dbo;DetermineNetworkAreaId';
(31 Mar '14, 11:31) Volker Barth

Oki, now I get it. somehow hasn't come up in me to look at that.

Thanks for the input

(31 Mar '14, 11:35) Frank Vestjens

The stored procedure was created with the wizard and that always create a stored procedure instead of a function. That's probably why I didn't notice this difference.

Thanks again

(01 Apr '14, 02:51) Frank Vestjens

Well, now it's not clear if the real problem is solved - when you use a stored function, does the desired result show up?

(01 Apr '14, 03:50) Volker Barth
showing 3 of 6 show all flat view

Here's a simple sample with a MS SQL server function that just returns the column length of a specified column of a specified table (without any error handling...) - tested with MS SQL 2008 R2:

create function dbo.FCTN_SysColumnLength(@table_name sysname, @colid int) returns int
  declare @length int
  set @length =
    (select length from syscolumns SC inner join sysobjects SO on =
        where = @table_name and SC.colid = @colid)
  return @length

Calling that function with the default table sysfiles1 for its 4th column returns 520:

select dbo.FCTN_SysColumnLength('sysfiles1', 4)

In SQL Anywhere, I declared a server class MSSODBC (with the appropriate DSN and credentials) and simply added a stored function that relates to the MSS function:

create server MSS_Test class 'MSSODBC' using 'DSN=...';

create function FCTN_SysColumnLength(@table_name sysname, @colid int) returns int
   at 'MSS_Test...FCTN_SysColumnLength';

select FCTN_SysColumnLength('sysfiles1', 4) -- returns 520 as well

Note: When I do use a stored procedure within SQL Anyhwere instead, this does not work (and seems to meet your test results):

-- Note: Parameters must be declared as "in" as inout would be the default
create procedure STP_SysColumnLength(in @table_name sysname, in @colid int)
at 'MSS_Test...FCTN_SysColumnLength';
-- use a variable to accept and display the procedure's return value
   declare nLength int;
   nLength = call STP_SysColumnLength('sysfiles1', 4);
   select nLength;

This returns 0 instead of 520.

Conclusion: It seems necessary - and logically appropriate - to use a stored function both within MS SQL Server and SQL Anywhere.

permanent link

answered 01 Apr '14, 04:26

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 01 Apr '14, 04:30

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 31 Mar '14, 10:49

question was seen: 4,553 times

last updated: 01 Apr '14, 04:30