Data Connections

Re: Large data connection help needed

shaun.nieves — Tue, 22 May 2007 17:27:24 GMT

Thanks for the reply! I'll take a look at the accelerator today.

I checked the IE settings and moved it from trusted to intranet but it didn't work. As far as the SQL connection is concerned. Could it be a port issue? Or is it even possible to connect to a SQL server remotely? When I test the form from within the network, it works fine however when I open the form from outside the network, I get the error. Please let me know if what I'm trying to do is even feasible. Thanks again!

Re: Large data connection help needed

Clay Fox — Mon, 21 May 2007 19:24:55 GMT

Hey Shaun.

The error here would be pointing to your permissions on SQL. If it works fine local but not remote and this error occurs when connecting that data connection SQL is denying your request. You maybe want to try running the query via manager when connected remotely to debug this. It could also be your Internet Explorer settings and how you treat the site that the form is being loaded from you should have it added to your intranet sites. This can often break connectivity in InfoPath dataconnections.

As far as performance, with these large lists and nesting you are going to put a strain on InfoPath. It is loading over 2500 items just to populate these drop downs. I would look at both how you organize your form as well as wether you can limit some of these lists or only load them manually when needed. You may need to break up your from into more views or more forms if performance is important. Webservices would help as well since you could maybe perform some queried lookups instead of loading the whole list.

I am not sure if you have evaluated the Database Accelerator download but it would give you some webservice examples or it may be a quick and easy way of getting the solution you need without having to build everything yourself.

Qdabra Database Accelerator Suite Version 2.0 - Developer

Large data connection help needed

shaun.nieves — Fri, 18 May 2007 01:47:43 GMT

I have a scheduling form where the user will enter in 3 items. The first dropdown has around 140 items. The second around 1800 and the third, 650. The lists are located in SharePoint. At first I created data connections however the customer wanted to see a concatenation of the client code and client name. Since infopath can not read a calculated sharepoint column, I created a SQL data connection. (See below) It pulls what I need however I'm having 2 problems with it.

It works internally however it does not work if I am accessing the form from outside of the network. I get an error stating the rule could not be run. All other data connection work just fine.
Secondly, these dropdowns are in a repeating row so as subsequent rows are added, the form slows to a crawl.

I've read that this can be accomplished using a web service however I'm not that well versed with that right now so I was hoping someone has an idea of what I'm doing wrong or if I'm heading in the wrong direction. If a web service is the only way then so be it but I'd like to use the SQL commands. Any help is greatly appreciated!

SELECT     Lists.tp_Title AS LIST, UserData.nvarchar2 AS CCODE, UserData.nvarchar8 AS NAME, UserData.nvarchar9 AS PHONE,
                      UserData.nvarchar2 + N', ' + UserData.nvarchar3 AS CONCAT
FROM         UserData INNER JOIN
                      Lists ON UserData.tp_ListId = Lists.tp_ID
WHERE     (UserData.tp_ListId = '{4C576139-9BF5-40E7-BC8C-320891FFB2F1}') AND (UserData.bit1 = 1)
UNION
SELECT     Lists.tp_Title AS LIST, UserData.nvarchar2 AS CCODE, UserData.nvarchar8 AS NAME, UserData.nvarchar9 AS PHONE,
                      UserData.nvarchar2 + N', ' + UserData.nvarchar3 AS CONCAT
FROM         UserData INNER JOIN
                      Lists ON UserData.tp_ListId = Lists.tp_ID
WHERE     (UserData.tp_ListId = '{8DB2783F-B5C1-4E71-9B86-C752A3AAADB1}') AND (UserData.bit2 = 1)
ORDER BY Lists.tp_Title, UserData.nvarchar2

Here is the SQL error I receive