Using SQL Server 2005 Web Services with InfoPath

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.

Note that a few rules apply to the settings that you put in the CREATE ENDPOINT statement that will make it so your Web Service plays nice with the InfoPath Rich Client.  That is, you are probably better off keeping the FORMAT = ROWSETS_ONLY setting as it appears above.  If not, then your web service will return a good deal more information about the query itself than just the rowset data you want, but it will still work for you.  Additionally, setting BATCHES = DISABLED will disable the sqlbatch webmethod from being automatically available on the web service, a powerful webmethod that you should probably keep disabled if you don’t intend to use it.

At that point, you should be able to design a form template against the web service like any other.  Using SQL Server 2005 rowsets with browser-enabled form templates is currently not a supported scenario; we’ll keep you posted on possible workarounds.

Also note that when you are designing a main web service against one of these web services that returns row data, InfoPath will warn you in the Data Connection wizard that the web service may return multiple datasets, and that’s an InfoPath no-no.  Under most circumstances, only one dataset will be returned (a dataset can still have multiple tables) so you can usually safely thank InfoPath for letting you know, and continue on.

Travis Rhodes
Software Design Engineer in Test