SQL for FormsViewer On-Premise Installation - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

SQL for FormsViewer On-Premise Installation

Last post 02-19-2018 10:25 AM by Jimmy. 16 replies.
Page 1 of 2 (17 items) 1 2 Next >
Sort Posts: Previous Next
  • 02-12-2018 11:12 PM

    SQL for FormsViewer On-Premise Installation

    In the document that explains the steps to install the FormsViewer app in an On-Premise environment I see requirement for instance of Microsoft SQL Server. As in our SharePoint environment we have separate SQL server can we use remote instance for this? And also one more question. We have 2 SharePoint servers. First server has Front-end with Distributed Cache role and second server has custom role that is Application, Search and Reporting Services. On which of these 2 servers do you recommend to install FormsViewer?
    Filed under:
  • 02-13-2018 08:45 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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,
    Patrick Halstead
    Project Manager at Qdabra
  • 02-13-2018 08:58 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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.
  • 02-14-2018 02:14 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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?
  • 02-14-2018 09:57 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    Hi Arthur, Yes, that warning is expected and it should not be a problem.
    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-14-2018 09:59 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    Thank you, Jimmy. What about QFSConnectionString? And what account permissions are needed on database?
    Filed under:
  • 02-14-2018 12:08 PM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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/"];".
    Filed under:
  • 02-14-2018 12:22 PM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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.
  • 02-14-2018 12:29 PM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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?"
    Filed under:
  • 02-15-2018 03:58 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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'. ".
    Filed under:
  • 02-15-2018 10:42 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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?
    Filed under:
  • 02-15-2018 02:40 PM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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?
  • 02-15-2018 09:33 PM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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.
    Filed under:
  • 02-18-2018 02:11 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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.

    Filed under: ,
  • 02-18-2018 06:09 AM In reply to

    Re: SQL for FormsViewer On-Premise Installation

    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.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
Page 1 of 2 (17 items) 1 2 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.