Why can’t you natively access Web services in
SQL Server 2005? You can access Web services in a CLR procedure and call them
in SQL Server 2005. If the XML HTTP supports 2-way Web-service access, why doesn’t
Microsoft provide the same 2-way communication for SQL Server 2005? I suspect
Microsoft left this feature out as a security precaution, but if I need to get
around this problem, I can get the XML and form the Web services connection
on my own by using a managed procedure. If I need to implement Web services
without using a managed procedure, what’s the best approach?
Microsoft had several reasons for its design philosophy. First, the HTTP Endpoint
support you mention is aimed at providing a simple way for an application to
pass data in and out of SQL Server without requiring that the application server
install the full client networking stack. The HTTP Endpoint infrastructure doesn’t
perform as well as the native client, so this technology doesn’t replace
all middleware.
Second, SQL Server is designed to be a repository of data and provide fast,
programmatic access to that data;it isn’t designed to be a full application
server, which is why SQL Server doesn’t provide language extensions to
access Web services. Where it has provided extensions (e.g., BULK INSERT), they
are designed to speed data loading.
Third, each Web service is unique in terms of inputs and outputs. No “OLEDB
for Web Services”exists, and given the potential for complex return structures
from Web services, providing a generic API that maps object to relational would
be difficult.
Finally, SQL Server 2005 provides full support for accessing Web services from
SQLCLR programs, which can return data as XML documents mapped to the XML data
type or as relational result sets (as single values or as table-valued functions).
So you have the flexibility to build code that accesses any Web service you
want. Several resources provide code examples. To get started, refer to the
SQL Server DVDs, Microsoft Community blogs (http://www.microsoft.com/communities/blogs/PortalHome.mspx),
and Microsoft articles such as “Using Native XML Web Services in SQL
Server 2005”(http://msdn2.microsoft.com/enus/library/ms191274.aspx)
and “Overview of Native XML Web Services for Microsoft SQL Server 2005”
(http://msdn.microsoft.com/data/default.aspx?pull=/library/enus/dnsql90/html/sql2005websvc.asp).
—Patrick
Conlan
Platform Program Manager
Microsoft Project Team