Hello all,
I'm trying to create a web service that merely exposes a table value function. I have a dummy function that looks like this (in a DB called ReportingDB):
USE [ReportingDB];
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO
My web service definition looks like this:
DROP ENDPOINT sql;
go
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go
Everything succeeds, however, the problem is that the TestFN function is not visible in the generated WSDL (http://mypc:90/sql?wsdl). If I do the same with a stored procedure instead of a function, then it works as expected, so it seems that I'm missing something or that this is a bug of SQL Server 2005.
Could somebody please shed some light on this issue?
Regards,
Ianier
Hi imj,
I think this comes from the type of function that you used. Your TestFN is a Table-valued function. I tried it with a Scalar-valued function, and it worked, the method is noted in the wsdl.
Here's my sample code:
DROP function [dbo].[TestFN];
GO
CREATE FUNCTION TestFN (@.DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal=1
RETURN(@.retVal)
END
GO
DROP ENDPOINT sql;
go
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql/test',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc.domain.com'
)
FOR SOAP (
WEBMETHOD
'http://tempuri.org/'.'TestFN'
(NAME = 'ReportingDB.dbo.TestFN'),
BATCHES = ENABLED,
WSDL = DEFAULT
)
go
/*GRANT CONNECT ON ENDPOINT::sql TO [xxx\yyy]*/
Best regards,
Marcus
|||Thanks Marcus,
Then it may be a SQL Server bug, since the CREATE ENDPOINT command succeeds. Should I get no answer here, I will open a bug report about this.
The reason why I would like to use table-value functions instead of stored procedures is that, since the metadata for table-value functions results is known in advance, I was expecting to see a typed dataset (or array) in the WSDL. That would let me use my function from, say, InfoPath.
Regards,
Ianier
|||Hi again,
FYI, I sent a bug report: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?FeedbackId=9e1e9b79-079d-40eb-b3f8-441432a26cbc
Nevertheless, it's hard for me to believe that such a serious issue escaped Microsoft's testing, so I'm still hoping that I'm missing something obvious.
Regards,
Ianier
|||SQL Server's SOAP does not support table valued functions unfortunately. This is why it's not working. I found this out by talking to one of the testers. I cannot find where this is mentioned in the docs yet.|||Hello,
Thanks for the info.
That would be a major disapointment. This limitation is not documented and CREATE ENDPOINT reports no error, so this must be either a bug or an "undocumented odd behavior", which is the same.
IMO, not allowing table-value functions in a SOAP endpoint makes the WS feature extremely limited, if useful at all.
I'll come back to this group as soon as I get some feedback on my bug report.
Regards,
Ianier
|||
The SQL 2005 Books Online documentation does have a statement stating that Table Value Functions are not supported in Native XML Web Services. Under the "Guidelines and Limitations in Native XML Web Services" topic (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8907005f-7f9b-4f53-80b8-f42729caf86b.htm), there's a bullet point saying:
Table valued user-defined functions are not supported.|||Thank you Jimmy,
In that case CREATE ENDPOINT should not succeed. This is definitely a bug.
Regards,
Ianier
|||
Hi Ianier,
Thanks for the feedback.
SQL 2005 actually allows the "CREATE ENDPOINT" query to be executed before any of the webmethods are created.
ie.
CREATE ENDPOINT sql
STATE = STARTED
AS HTTP(
PATH = '/sql',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
CLEAR_PORT = 90,
SITE = 'mypc'
)
FOR SOAP (
WEBMETHOD 'TestFN'
(name='ReportingDB.dbo.TestFN', SCHEMA=STANDARD),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'ReportingDB',
NAMESPACE = 'http://tempUri.org/'
);
go
CREATE FUNCTION [dbo].[TestFN] ()
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
RETURN
END;
GO
In this scenario is would not be possible for the CREATE ENDPOINT query to verify what the webmethod is. You can argue that this should be a bug as well, but the current behavior provides a more flexible system. Suppose we were strict about requiring the webmethods to be created before the CREATE ENDPOINT query, what would happen if after the stored procedure or function was created, someone went and ALTER it or even DELETE it? Should the different endpoints to magically re-verify that the new version of the webmethod is still valid or even there?
The current mechanism allows SQL to verify at execution/run time that the methods are valid and the user has the proper permissions.
Hope that addresses some of your concerns.
Jimmy Wu [MSFT]
|||
Hi Jimmy,
Thanks for the information.
Common sense tells me that if the function exists and is not of a supported type (i.e. a table-value one) then CREATE ENDPOINT should fail. After all, CREATE ENDPOINT fails, for example, when trying to bind a WEBMETHOD to a view because views are not supported.
If someone ALTERs or DELETEs the function afterwards in a way that is not compatible with the endpoint, either of the following should happen:
Option 1: give an error explaining that the WEBMETHOD should be modified first (i.e. there's a dependency).
Option 2: delete the WEBMETHOD automatically and give a warning to the user.
Regards,
Ianier
