InfoPath Dev

Hey Everyone. I am creating a Database Form and am attempting to add a secondary data connection. I have a SQL query that timesout on the initial creation of the connection when I attempt to "Test connection" or hit "Next" after editing the SQL command.

Normally I would add a where clause into the query and limit its size to create the connection then I would convert it to a UDCX and remove the where clause. Then I could use the ModifySql command to  fire my query with the where clause when I needed the data. In this case I cannot use the UDC.

Is there a way to increase the timeout length so that I can create the data connection I require? 

 

 This is in IP 2010. sorry meant to put that in there. SQL 2008 r2 is the DB im pulling from.

When you initially create the data connection to SQL there should be a button in one of the dialogs that you can click to show the actual SQL IP is using. I'm wondering about adding a "top x" to the query - do you have an idea of the max number of rows your ModifySQLQuery command might return? You could probably limit the results enough to allow SQL to create the connection.

I don't recall the show ip option. I will recreate the connection and look for it closer.  About the "TOP(x)" I can attempt it, but I don't believe it will do me any favors as the data sets can vary greatly and since I am not filtering with my where til in the form it's still going to cycle through a project list. Which in this case really is the problem causing the timeout. I will attempt both and let you know how it goes.

Thanks for the quick response. 

 Still couldn't find a "Show IP" option and the TOP(x) isn't working either. I'm using a WITH statement to create multiple "temp tables" and with a WHERE clause it runs incredibly fast. Without it, it's querying the entire db. I just need to get the connection made then the ModifySql command will keep my performance great and smooth. 

By "IP" I meant InfoPath - the button itself shows the SQL InfoPath is using. What I'm trying to suggest is a way to get the form to let you set up the data connection - my understanding of your description was that the form is timing out when you try to create the data connection.

 But it sounds like you are already directly modifying the SQL when you create the query to include your "With" statement.

So - why not just add the where clause when you create the connection, then save the form as source files, and remove the where clause from the manifest (which is where your original SQL command will be found) so you can use ModifySQL from qRules? A little work-around-y, but it might do the trick...

 Let me see if I understand correctly. Keep my WHERE clause and make the Data connection. Then save the form and use it a xml source file for a data connection in a new form? When you say remove it from the manifest do you mean removing the WHERE clause from the xml source file?

I am suggesting that when you create the data connection in your InfoPath form template, you include the Where clause by clicking the Edit SQL button in the data connection dialog that generally shows the SQL data structure tree. Set the data connection to not run on load.

 Then save your form as source files (from the Publish backstage).

Then open manifest.xsf in a text editor and look for your "Where" clause - remove that from the SQL in manifest.xsf. Save your changes. Right click manifest.xsf and select Design. This opens the form in design mode. Use File > Save As to pack your form back up as an XSN.

Now, you should be able to use the qRules ModifySQL command to add a new, dynamic Where clause to your query as needed.

Does that make sense?

To directly answer your question about the timeout - I do not know of a way to adjust the time out for a data connection while walking through the data connection wizard. Once the connection has been created, the timeout can be adjusted via qRules or code.

 

It does, Thank you. I will give this a try and see what I can do.

Thanks again. 

 Well I could kiss you right now. Thanks so much Hilary. I think I can take it from here.

Excellent! glad it is all sorted. :)

Hey Hillary, remember when you said the "timeout can be adjusted via qrules"? How do I got about doing so along with my ModifySQL command?

 Well, on review of the user guide & the code, it appears I may have been lying to you. :)

So, now you get to help me decide the best approach for this. We are trying to get qRules 8.1 out the door (email me via the forum if you want to beta test a copy for me), and this would be a great time to add this.

The simplest approach would just be to add a timeout parameter to the ModifySqlQuery command, then you can just add /timeout=120 or whatever to your existing command. I should add that to ExecuteSqlQuery too.

That would be perfect! is there anyway that(timeout parameter) can be added to the beta version now and then I would gladly test for you. My only concern would be that I have the ability to roll back to a previous version or have you on hand(email phone call or otherwise) incase we run into some problems. This is currently in our clients production environment and the form cant be down for long.

 Just updating to add that you have this for beta testing now, and this will be included in qRules 8.1