Using SQL Server 2005 Web Services with InfoPath

re: Using SQL Server 2005 Web Services with InfoPath

Daniil — Wed, 03 Dec 2008 12:45:33 GMT

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

re: Using SQL Server 2005 Web Services with InfoPath

Daniil — Tue, 02 Dec 2008 15:02:04 GMT

@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

re: Using SQL Server 2005 Web Services with InfoPath

toroklaszlo — Tue, 26 Aug 2008 16:35:46 GMT

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

re: Using SQL Server 2005 Web Services with InfoPath

JSB35 — Tue, 19 Aug 2008 23:23:12 GMT

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!

re: Using SQL Server 2005 Web Services with InfoPath

toroklaszlo — Tue, 19 Aug 2008 17:18:36 GMT

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

InfoPath and Yukon: The Details

Dragon-China — Thu, 11 Oct 2007 05:40:33 GMT

InfoPathandYukon:TheDetails Inarecentpost,Itoucheduponthereasonswhyyoumightwantt...

re: Using SQL Server 2005 Web Services with InfoPath

mkoslof — Wed, 19 Sep 2007 17:19:13 GMT

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

re: Using SQL Server 2005 Web Services with InfoPath

brandtsmith — Thu, 16 Aug 2007 00:02:15 GMT

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?????

re: Using SQL Server 2005 Web Services with InfoPath

brandtsmith — Mon, 13 Aug 2007 22:47:23 GMT

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!!! :)

re: Using SQL Server 2005 Web Services with InfoPath

uppershores — Wed, 20 Jun 2007 00:38:47 GMT

I successfully created a Web Service (SQL Server 2005) using the above technique and it works great populating a list box in the InfoPath preview mode. When I publish the form to SharePoint, only the first row populates the list box rather than the entire record set. I think the issue might be related to security but I do not know how to resolve this. Is this an InfoPath, SharePoint, or SQL Server issue, and where should I start?