InfoPath Dev

I have a form that I created that has a data connection to a SQL database and I am using that connection to perform a data validation and to fill in fields when a user types in a value that matches a column in the SQL view. It seems to work okay, but somewhat slow using the InfoPath client but when I use it in SharePoint Form Services I am getting errors. I checked the SharePoint trace logs and discovered that I needed to increase the "Data Connection Response Size" so I increased it to 10 MB. After increasing the response size I am now getting a Form Services error "This session has exceeded the amount of allowable resources".

It looks like InfoPath is returning all the rows in this view which is about 43,000 rows to perform my data validation and population of my fields when the user types in a correct value. Why does InfoPath need to pull all of these rows? What are my options, if any, to get around this issue? Thanks.

Unfortunatley this is how secondary data connections are handled in InfoPath, all data is loaded and then filtered in the form.

I am surprised that the performance was tolerable even in the client.

But their is a solution.

You need a webservice which can query SQL and just return the record or set of records you want, and this can be dynamic for each query.

You can purchase the webservice from www.qdabra.com. Here is documentation for it. How to: User Oriented Infopath example for using the Database Enumeration Service

In my case above, can you create a web service to query a SQL 2000 database?

Yes, you can. There are not maybe as many tools but it is possible.

The Qdabra webservices work with SQL 2000.

You can also do this in managed code in the form, but then you have the cost of developing and managing the code.

The is why I like the database accelerator package, it is generic and reusable, and can be used without any code by any form developer.