Partager via

Using SQL Server 2005 Web Services with InfoPath

  • Article

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

Comments

  • Anonymous
    December 20, 2006
    I found this excellent tip in the Infopath team blog , but it is useful not only for Infopath developers...

  • Anonymous
    June 19, 2007
    The comment has been removed

  • Anonymous
    August 13, 2007
    Great way to filter very large data sets into a manageable ddlb as a secondary data source...keeps me out of VS...and I don't have to write all that script in IP!!! :)

  • Anonymous
    August 15, 2007
    Problem...how do you represent the namespace in infopath (jscript) to read the fields in a repeating table that come from this web service? I can display the web service data no problem...just can't get the syntax right to read the fields?????

  • Anonymous
    September 19, 2007
    Hello there I really  hope the author of this article is still around because this was great information :).  However, I am in desperate need of some help/clarification. K, can we use this approach with InfoPath Forms Services?  In browser enabled Infopath documents is this still possible.  This part left me guessing: "Using SQL Server 2005 rowsets with browser-enabled form templates is currently not a supported scenario; we’ll keep you posted on possible workarounds." Do you mean you can't pass in the optional FORMAT parameter with the value of ROW_SET or do you mean you can't use it at all. The reason I am asking is I tried to implement this and it doesn't seem to work in Browser enabled forms.  If I edit the exact same document in InfoPath (either before being published to a forms library or after) it works great.  I did the proper administrative install/approval through Central Administration and I've tried using udc connection files in the local site collection - - nothing seems to work. So am I missing something?  Or is it just not possible.  I am struggling to get a definitive answer on this. Thanks in advance MK

  • Anonymous
    October 10, 2007
    InfoPathandYukon:TheDetails Inarecentpost,Itoucheduponthereasonswhyyoumightwantt...

  • Anonymous
    August 19, 2008
    Hi folks, I think the problem is the structure of 'sqlresultstream' complextype, generated by sql server. When sql server responses to a ws request (through an endpoint), always gets back a node with this type. This complextype contains some other types. One of these types is SqlRowset. By default, an 'sqlresultstream' can contain unbounded number of 'SqlRowset', defined by generated wsdl. I think Infopath could accept only one 'SqlRowset', as result of an sql query (maybe it identifies 'SqlRowset' as DataSet?). Based on my experience, when Infopath Forms Server calls a ws like this, it ignores the response, whatever it is. The rich client could process the response. Possible solution: Use the SELECT in your stored proc you would like to call thorugh an endpoint with 'FOR XML' clause, if you can. In this case, the response node will contain an instance of 'SqlXml' type(also part of sqlresultstream complextype), instead of 'SqlRowset' type. Form Server can correctly process responses contain 'SqlXml' types. Laszlo

  • Anonymous
    August 19, 2008
    Laszlo, any more information on this possible solution? My form doesn't like the return format of SqlXML? Have you confirmed that this works? Thanks if you can add anything to this great informatinon!

  • Anonymous
    August 26, 2008
    Yes, I can confirm that this way of calling a ws through an endpoint worked in my case. What does it mean exactly that your form 'doesn't like the return format of sqlxml'? Laszlo

  • Anonymous
    December 01, 2008
    @toroklaszlo Do you have an example for an sql statement with the "for XML" clause? My InfoPath Form doesn't like my statement: "SELECT Name FROM dbo.NameTable FOR XML AUTO My EndPont returns no DataFields :/   ...  just a group Thanky in advance!! Daniil

  • Anonymous
    December 02, 2008
    OK, I solved the Problem: Procedur: Select ... FOR XML AUTO, ELEMENT Endpoint: WEBMETHOD ...   (      FORMAT = ALL_RESULTS .... ) The "problem" now is, that I get the whole information from the endpoint, like SQLRowCount, SQLMessage, SQLTranscription ... . But I need just the DataFields :/ Has anybody an idea how to do this? Thanks, Daniil