SQL Database Integration

Re: Multiple SQL 2005 DB into InforPath 2007

sql_dan — Wed, 02 Dec 2009 09:21:23 GMT

Hi Patrick, Thanks for your time to explain all this. We have ordered a SharePoint Server licence so i'll have a play with InfoPath and that to see how it all fits together. Thanks again for your help Patrick. Dan

Re: Multiple SQL 2005 DB into InforPath 2007

Patrick Halstead — Wed, 02 Dec 2009 04:15:34 GMT

Hi Dan,

Well, you could certainly set up 3 database queries as actions in a rule that fires when you click a button. The problem is that the data must be pulled down to the form and then filtered so it could take a lot of time if you have large databases. There is no way in InfoPath to pass a query filter in dynamically from a field when you execute the database query. To do programmatic filtering on the server, you need to a) use a Web Service like Qdabra's qWSSP or b) write some code that calls a SPROC or something. Of course, you could also prepare a SQL view and hardcode the filter and that would prevent downloading all the data to the form, but what most people really need is dynamic filtering on the server and InfoPath doesn't have that out-of-the-box. You can filter the SQL query in the database connection wizard but it must use a hardcoded value. Injecting a dynamic value can be done with code that constructs the TSQL query string and passes it to the data connection.

Best practice is to use a Web Service and pass the filtering field parameter to it. The Web Service executes the query and returns the results to InfoPath. That will make your query very fast and reduce the amount of data coming down over the wire.

So, to summarize, sure you can do it from InfoPath but there are limitations around filtering. Best practice is to use a Web Service to provide dynamic filtering and optimal performance.

Hope this was helpful.

Multiple SQL 2005 DB into InforPath 2007

sql_dan — Mon, 30 Nov 2009 15:10:55 GMT

Hi there, 1st of all I am fairly new to InfoPath and am learning as I go so please be patient with any 'silly' questions!

I have done a search for this on many forums and google and not found anything but if it has been covered then I appologise for the duplication and welcome the forwarding link.

I manage a number of SQL Servers across 3 sites and we are in the process of moving them onto a central server so that they can be managed within one session of SQL Server.

I began playing with InfoPath 2007 last week and managed to connect it to the database and run queries.

My question however relates to wheter InfoPath is powerful enough to connect to more than 1 SQL connection to perform a query over multiple databases. Please bear in mind that the architecture of all databases over the 3 sites are identical just with different information contained within the tables.

If this is not possible through a single query would this be possible in another way?? (Even if additional programmes are required etc)

If this is not possible (1 query to 3 SQL DB's) then can InfoPath handle 3 SQL DB connections with one on a seperate view?

Any help would be great.
Thanks
Dan