Excellent tip - Using SQL Server 2005 Web Services with InfoPath but not only...
I found this excellent tip in the Infopath team blog, but it is useful not only for Infopath developers...
Here’s your problem: You want to use a stored procedure in a database to access your data through InfoPath, but you don’t really want to have to write script for every query to change the stored procedure’s query parameters. We’ve all been there.
Well, stop your coding, right now, because SQL Server 2005 allows you to create SOAP Web Service “endpoints” that act as a type of exposed stored procedure over HTTP. Among other cool things, this will allow you to have the parameters you need exposed as query parameters in InfoPath’s data source. It's easy to set up in SQL Server 2005 using the CREATE ENDPOINT T-SQL statement. For example, let's say there is a simple Stored Procedure called "getAge" that takes an integer value and returns every person in a table that has that age. The SQL statement to expose that Stored Procedure as a document literal SOAP Web Service could look like this:
CREATE ENDPOINT getSpecifiedAgeEndpoint
STATE = STARTED
AS HTTP
(
SITE = 'myserver',
PATH = '/getspecifiedagewebservice',
AUTHENTICATION = ( NTLM ),
PORTS = ( CLEAR )
)
FOR SOAP
(
WEBMETHOD 'GetRecordsWithSpecifiedAge'
(
NAME = 'AdventureWorks.dbo.getAge',
SCHEMA = DEFAULT,
FORMAT = ROWSETS_ONLY
),
WSDL = DEFAULT,
BATCHES = DISABLED,
DATABASE = 'AdventureWorks'
)
The web service will then be located at http://myserver/getspecifiedagewebservice?wsdl. Note that this web service will not be exposed through IIS; It’s all happening directly from SQL Server 2005. One method will be exposed at the URL called “GetRecordsWithSpecifiedAge”. For more information on getting the endpoint up and running correctly, see this MSDN article.
You can find the full article here.