The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 31 Mar '14, 10:49

Frank's gravatar image

Frank
1204512
accept rate: 0%

edited 31 Mar '14, 10:53

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262

1

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
Replies hidden
1

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';
(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

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

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
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.

permanent link

answered 01 Apr '14, 04:26

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×60
×46
×18

question asked: 31 Mar '14, 10:49

question was seen: 1,420 times

last updated: 01 Apr '14, 04:30