Accessing Web Services in SQL Server 2005

Q: 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