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:
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
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:
Calling that function with the default table sysfiles1 for its 4th column returns 520:
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:
Note: When I do use a stored procedure within SQL Anyhwere instead, this does not work (and seems to meet your test results):
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.