How to pass parameters to a remote procedure from Sybase to MS SQL Server

[fvestjens]

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
    AS
    BEGIN
      declare @RegionId int
      --
      set @RegionId = (select 
                          top 1 RegionId
                       from 
                          dbo.Region join
                          dbo.RegionAreas on RegionAreas.RegionId = Region.Id join
                          dbo.Country on Country.Id = RegionAreas.CountryId
                       where 
                          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                    
    END
    GO

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

Regards,

Frank

[VolkerBarth]

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?

[fvestjens]

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

[VolkerBarth]

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))
  RETURNS int
  AT 'Quote2;quote;dbo;DetermineNetworkAreaId';

[fvestjens]

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

Thanks for the input

[fvestjens]

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

[VolkerBarth]

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

[VolkerBarth]

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
as
begin
  declare @length int
  set @length =
    (select length from syscolumns SC inner join sysobjects SO on SC.id = SO.id
        where SO.name = @table_name and SC.colid = @colid)
  return @length
end


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
begin
   declare nLength int;
   nLength = call STP_SysColumnLength('sysfiles1', 4);
   select nLength;
end;


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.