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. |
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?
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
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:
Oki, now I get it. somehow hasn't come up in me to look at that.
Thanks for the input
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
Well, now it's not clear if the real problem is solved - when you use a stored function, does the desired result show up?