topic Re: QlikView / QMC to extract data from SP2013 with authentication in QlikView

QlikView / QMC to extract data from SP2013 with authentication

Tue, 26 Jan 2016 10:33:58 GMT

HI,

I am facing difficulties when I schedule a task in the QMC.

I need to schedule a SharePoint list data extract, where the connection URL contains a service account and password.

The service account is part of the connection URL and can log into the SharePoint server and has the Read permission to pull the data.

In the QV server the manual reload working as expected.

In QMC the reload fails. Based on the QVW log: access denied.

Can somebody help and confirm if it is required to allow the service account assigned to the QMC to allow to logon to the SharePoint server as well?

Thanks in advance!

Bye

Rob

Re: QlikView / QMC to extract data from SP2013 with authentication

markodonovan — Tue, 26 Jan 2016 13:11:33 GMT

Hi Robert,

In the past I have had to query Sharepoint lists in Office 365.

I would execute the powershell script from within the loadscript as follows:

EXECUTE C:\Windows\System32\WindowsPowerShell\v1.0\powershell -File $(powershellcmd);

I have attached a powershell script I used to do this in case the credentials code helps.

Probably a bit different for SP2013 though.

Thanks

Mark

http://techstuffybooks.com

======================================

$webUrl = "https://xxxx.sharepoint.com"

$username = "MYUSERNAME"

$plainpassword = "MYPASSWORD"

$password = "MYPASSWORD" | ConvertTo-SecureString -AsPlainText -Force

$sourceListName = "hireman_documents"

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)

$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

Re: QlikView / QMC to extract data from SP2013 with authentication

amit_saini — Tue, 26 Jan 2016 13:24:53 GMT

Hi Robert,

Yes for loading Sharepoint data , service account should be configured.

Thanks,

Re: QlikView / QMC to extract data from SP2013 with authentication

Tue, 26 Jan 2016 14:33:27 GMT

HI AS,

Thanks for the quick reply.

The QV service account has been updated to include the relevant server name in the 'Allow logon to' parameter. Unfortunately the problem is still the same - reload working in QV application manually, but fails in the Management Console, with 'access is denied' error.

Re: QlikView / QMC to extract data from SP2013 with authentication

Wed, 27 Jan 2016 09:15:03 GMT

@HI Mark,

thank you for sharing the script. I think it would be an option for me as a workaround.

My primary objective is to fix the current RPC connection URL (if needed) or figure out why the reload fails in Management Console only and works in QlikView server when I reload manually.

Here is a sample script

Process:

LOAD ows_LinkTitle, ows_ID, ows_SVProcessDefinitionName, ows_SVProcessDuration, ows_SVProcessId, ows_SVProcessStatus, ows_SVSite, ows_SVUTCCompletionDate, ows_SVUTCCreationDate, ows_SVUTCDueDate, ows_SVUTCProcessDefinitionCreationDate, ows_SVWasForced, ows_Modified

FROM [http://seviceaccountID:password@sharepointsite/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={ListGUID}&View={ViewGUID}] (XmlSimple, Table is [xml/data/row])

WHERE ows_Modified >= (calculated date);

STORE Process INTO ..\..\QVDs\T1\Process.qvd;

DROP TABLE Process;

Re: QlikView / QMC to extract data from SP2013 with authentication

Wed, 03 Feb 2016 06:52:52 GMT

How to resolve this?

Re: QlikView / QMC to extract data from SP2013 with authentication

Thu, 04 Feb 2016 07:23:15 GMT

Hi Mo,

still no solution.

I use a workaround: I created a job in the server's task scheduler to reload the QVW daily.

I use this action: start a program

-program: "D:\Program Files\QlikView\Qv.exe"

-arguments: /r D:\Qlikview\Sandbox\APP_test\T1_DataExtracts\T1_Test_ExtractSpList.qvw

In a test environment it is more or less working, but in a production environment this workaround should not be used.

Re: QlikView / QMC to extract data from SP2013 with authentication

Fri, 05 Feb 2016 01:44:50 GMT

Hi Robert,

Thanks for your suggestion.

For now I create a task schedule to refresh the excel which is export from sharepoint list.

Then QS to read the excel daily.

Re: QlikView / QMC to extract data from SP2013 with authentication

Fri, 19 Feb 2016 15:15:26 GMT

QMC reload is something that I have had trouble with for months now . . . Qlikview couldn't assist with it either barring I pay their consultants an arm and a leg to look at my problem. My suggestion is to look at a command-line reload and schedule the .bat file to run through your windows task scheduler. Worked like a charm for me. Here is the best article that I found on the community for it:

Reload and save using command line

QlikView Maven: QlikView Command Line and Automation

Taylor