Qdabra FormsViewer

Re: SQL for FormsViewer On-Premise Installation

Jimmy — Mon, 19 Feb 2018 18:25:58 GMT

Hi Arthur,

It sounds like FormsViewer is successfully querying the database so I think your connection string is fine.

Since you are using Integrated Security, I believe that the only account that would need permissions is the FormsViewer site's app pool identity. It should have both read and write access to the DB.

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Sun, 18 Feb 2018 15:58:34 GMT

Yes Jimmy, my SQL instance was using case sensitive collation. I removed that instance and installed new one with case-insensitive.

Now all links are opening correctly. Thank you!

Can you also tell me about QFSConnectionString? Is this enough "Data Source=MYSQLSERVER\FORMSVIEWER;Initial Catalog=FormsViewer;Integrated Security=True". And what accounts should have permissions on SQL database?

Re: SQL for FormsViewer On-Premise Installation

Jimmy — Sun, 18 Feb 2018 14:09:18 GMT

Hello Arthur,

Does your FormsViewer database have a case-sensitive collation by any chance? That would explain why you are getting an error for this query.

The inconsistent capitalization of @userKey does appear to have been an oversight on our part, but FormsViewer's functionality is designed and utilized with the assumption that FormsViewer's database uses a case-insensitive collation.

So I would ask that you either change the database to use a case sensitive collation or, if it's easier, simply re-create the database with a case-insensitive collation.

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Sun, 18 Feb 2018 10:11:51 GMT

Actually I should add InitialCatalog=FormsViewer in QFSConnectionString. After filling "Data Source=MYSQLSERVER\FORMSVIEWER;Initial Catalog=FormsViewer;Integrated Security=True" FormsViewer page opened.

But now there is another problem. When I click "Manage Templates", "Manage Href Associations" or "Quick Launch Dashboard", the following is displayed:

Error.

An error occurred while processing your request.
Must declare the scalar variable "@UserKey".

With SQL Server Profiler I can see, that it's running the following query:

exec sp_executesql N'SELECT t.*, ISNULL((SELECT TOP 1 Opens FROM MonthlyTemplateAccess mta WHERE mta.TemplateId = t.TemplateId AND mta.Month = @Month AND mta.Year = @Year),0) AS MonthlyOpens FROM Templates t WHERE t.UserKey = @UserKey AND (t.Location IS NULL OR t.Location = @Location)',N'@userKey nvarchar(255),@Location nvarchar(255),@Month int,@Year int',@userKey=N'9b56d644-d124-4754-aee3-d313cdd87a88',@Location=N'https://portal.mydomain.com/',@Month=2,@Year=2018

And here is the problem. You specify t.UserKey = @UserKey, but then instead declare @userKey instead of @UserKey. I'm sure this is a bug, please, fix this.

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Fri, 16 Feb 2018 05:33:51 GMT

I have all listed files. No errors. I installed app successfully, but when try to open it I get error. Please see screenshot here: https://pasteboard.co/H7RmtaT.png. Can you help me with this Jim? I did everything according "FormsViewer On-Premise Installation and Configuration.docx", but no luck.

Re: SQL for FormsViewer On-Premise Installation

Jim Cantwell — Thu, 15 Feb 2018 22:40:54 GMT

You should find the following files in the zip download:
7z.dll
7z.exe
FormsViewer On-Premise Installation and Configuration.docx
FormsViewer.zip
FormsViewerApp.app
InstallFormsViewer.ps1
FormsViewerDB\FormsViewerDB.sql
FormsViewerDB\FormsViewerKeys.sql
FormsViewerDB\FormsViewerObjects.sql
FormsViewerDB\FormsViewerOnPremise.sql

Are you getting any errors in the deployment process?

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Thu, 15 Feb 2018 18:42:19 GMT

Patrick Halstead:
Hi Arthur, Thank you for your questions. I have asked one of our FormsViewer install engineers to respond to you as soon as possible. We appreciate your patience. Regards,

Hi Patrick, are you sure, that SharePoint On Prem Installation Package placed under "http://www.infopathdev.com/files/folders/formsviewer_v11/entry107794.aspx" is fully functional? I wanted to try it on our on premise environment, but still no luck. Maybe there are some missing files?

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Thu, 15 Feb 2018 11:58:33 GMT

If I specify "connectionString="Data Source=MYSQLSERVER\FORMSVIEWER;Integrated Security=True" and give Application pool account sysadmin permissions on SQL instance, then I get the following error, when opening FormsViewer App from SharePoint: "Invalid object name 'MonthlyLocationAccess'. ".

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Wed, 14 Feb 2018 20:29:04 GMT

Thank you, Jim. Can you also answer to this question? "For remote SQL instance is "Data Source=MYSQLSERVER\FORMSVIEWERINSTANCENAME" QFSConnectionString enough or other parameters like Database, Initial Catalog, Integrated Security, MultipleActiveResultSets, etc. are needed? And what account permissions are needed on database?"

Re: SQL for FormsViewer On-Premise Installation

Jim Cantwell — Wed, 14 Feb 2018 20:22:03 GMT

Configuring cross-domain web service calls is only required when you need the browser client to connect directly to the endpoint for authentication reasons. If you do not configure for cross-domain, the call will go through the FormsViewer proxy. The proxy can make calls to web services anonymously, or using credentials saved under "Manage Credentials" in Forms Viewer.

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Wed, 14 Feb 2018 20:08:25 GMT

And one more question. If siteUrl is portal.mydomain.com, remoteAppUrl is forms.mydomain.com and appStartPageUrl is https://forms.mydomain.com/FormsViewer/, and my SharePoint app domain is mydomainapps.com, do I need to complete "CROSS-DOMAIN CLIENT-SIDE WEB SERVICE CALLS" part of installation document? I have some misunderstanding about this part: "list = ["https://dbxl.yourdomain.com/", "https://yourservice.com/"];".

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Wed, 14 Feb 2018 17:59:11 GMT

Thank you, Jimmy. What about QFSConnectionString? And what account permissions are needed on database?

Re: SQL for FormsViewer On-Premise Installation

Jimmy — Wed, 14 Feb 2018 17:57:30 GMT

Hi Arthur, Yes, that warning is expected and it should not be a problem.

Re: SQL for FormsViewer On-Premise Installation

Arthur.Avanesyan — Wed, 14 Feb 2018 10:14:47 GMT

After running FormsViewerObjects query, I get the following warning: "Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK__HrefAsso__DFADD7660B138110' has maximum length of 1532 bytes. For some combination of large values, the insert/update operation will fail.". Should I take additional steps or it's OK? And for remote SQL instance is "Data Source=MYSQLSERVER\FORMSVIEWERINSTANCENAME" QFSConnectionString enough or other parameters like Database, Initial Catalog, Integrated Security, MultipleActiveResultSets, etc. are needed?

Re: SQL for FormsViewer On-Premise Installation

Jim Cantwell — Tue, 13 Feb 2018 16:58:35 GMT

Yes, you can use a remote SQL Server instance. The install location can be any server, it is more a server load question. FormsViewer is not very resource intensive, but if you SharePoint WFE is low on resources, you may want to put on a lower utilized server. It is sometimes easier to put on the WFE that already has routing configured where external traffic is involved.