topic EDX - SQL Server in QlikView

EDX - SQL Server

Mon, 26 Jan 2026 18:19:17 GMT

I am trying to get some help on EDX.. What I want to do is trigger a task in QV Management Console, once our overnight load sequency in SQL Server has finished. Can this be triggered as a command from SQL? And what do I nned to do?

Any help is appreciated.

Regards

EDX - SQL Server

Wed, 21 Apr 2010 15:57:39 GMT

Check this thread http://community.qlik.com/forums/p/18961/91298.aspx#91298 or use this vbs code from a SQL stored procedure

Dim url, doc, uid, pass
url = "http://myQVAppServer.MyDomain.edu:4720/qtxs.asmx"
doc = "MyTestDoc.qvw"
userID = "myuserid"
pass = "myPassword"

MsgBox ReloadEDX(url, doc, userID, pass)

Function ReloadEDX (dsURL, document, userID, password)
Dim requestKey, xmlhttp, requestData, httpResult
set xmlhttp = createobject("msxml2.xmlhttp.3.0")
xmlhttp.open "post", dsURL, false, userID, pass
requestData = "<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf
xmlhttp.send requestData
requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
requestData = "<Global method=""RequestEDX"" key=""" & requestKey & """><i_TaskIDOrTaskName>" & document & "</i_TaskIDOrTaskName><i_Password /><i_VariableName /><i_VariableValueList /></Global>" & vbCrLf
xmlhttp.open "post", dsURL, false, userID, pass xmlhttp.send requestData
httpResult = xmlhttp.responseXML.xml
If xmlhttp.responseXML.selectSingleNode("//TaskStartResult") Is Nothing Then ReloadEDX = httpResult
Else
If xmlhttp.responseXML.selectSingleNode("//TaskStartResult").text = "Success" Then ReloadEDX = "Reloading of " & document & " has been started"
Else
ReloadEDX = httpResult
End If
End If
End Function

EDX - SQL Server

Fri, 23 Apr 2010 14:14:47 GMT

I tried this code and it didn't work.

You have to change doc = "mytest.qvw" to the reload task name in Publisher.

Works on version 9 SR3

EDX - SQL Server

Mon, 26 Apr 2010 19:06:32 GMT

Thanks for the reply but some clever person here (not me). Has written an SSIS package to execute the task once over night loads are completed.

EDX - SQL Server

Anonymous — Mon, 26 Apr 2010 19:51:44 GMT

I have rewritten a bunch of code down to a simple EDX call. I am not a Visual Basi programmer, but made this work. I have a SSIS Package that only has a Script Task with the following code:


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
 Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
 Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
Public Sub Main()
 Dim requestKey, xmlhttp
 xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
 xmlhttp.open("post", "http://YourServerName:4720/qtxs.asmx", False)
 xmlhttp.send("<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf)
 requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
 xmlhttp.open("post", "http://YourServerName:4720/qtxs.asmx", False)
 xmlhttp.send("<Global method=""RequestEDX"" key=""" & requestKey & """><i_TaskIDOrTaskName>YourTaskName.qvw</i_TaskIDOrTaskName><i_Password /><i_VariableName /><i_VariableValueList /></Global>" & vbCrLf)
End Sub
End Class

Good Luck. The EDX call is not as easy as it once was!

Jerry

EDX - SQL Server

erichshiino — Thu, 28 Apr 2011 18:55:10 GMT

Do these codes apply to QVS 10? Do I need to use publisher?

EDX - SQL Server

Thu, 28 Apr 2011 20:49:46 GMT

Yes, this code apply to v10 too, you don't need Publisher, Regards.

EDX - SQL Server

erichshiino — Thu, 28 Apr 2011 20:59:31 GMT

Thanks a lot!

EDX - SQL Server

Anonymous — Fri, 07 Oct 2011 16:22:34 GMT

Has anyone used this code with QlikView Version 10 SR3? We are upgrading this weekend and are crossing our fingers that the Code still works!

Thanks,

EDX - SQL Server

rwunderlich — Fri, 07 Oct 2011 17:15:40 GMT

I can't speak to the exact code published above, but I can confirm that the same code that works for V9 works for V10SR3.

-Rob

http://robwunderlich.com

EDX - SQL Server

Anonymous — Fri, 07 Oct 2011 18:36:27 GMT

Thanks! That is what we were hoping to hear!

EDX - SQL Server

Anonymous — Mon, 17 Oct 2011 21:40:07 GMT

Just to verify, this code still works on QV10 SR3!

EDX - SQL Server

Anonymous — Wed, 09 Nov 2011 13:21:27 GMT

Thanks, i use the code from time to time!

I use it to reload the qlikview frontend from SSIS when i have an SQL datawarehouse.

I can see if the EDX has started succesfully, but i cannot see if the task's depending on the EDX also have completed succesfully.

Is it posible in the qvs-api to create a vba-code to se the status for a task?

Cause then i can send a message back to the SSIS that the qlikview reload has also finished succesfully.

If it is, please help me on this one with an example.

henrik

Re: EDX - SQL Server

Anonymous — Mon, 14 Nov 2011 13:51:00 GMT

Here is what I use, hopefully you can pull out what you need to fit your code!

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 Public Sub Main()
        Dim xmlhttp, TaskName, EDXStatus, EDXStartStatus, EDXStartTime, EDXEndTime
        TaskName = Trim(Dts.Variables("User::EDXTaskName").Value.ToString())
        EDXStatus = "Running"                   'Set Status for Initial run through do while  loop
        EDXStartTime = FormatDateTime(Now())    'Get the start time for the QVW
        EDXEndTime = EDXStartTime               'Initial set of the EndTime
        If TaskName <> "NonEDXTask" Then
            EDXStartStatus = StartEDX(TaskName)
            Do While EDXStatus = "Running" And EDXStartStatus = "Success"
                EDXStatus = GetEDXValue(TaskName, "Status")
                If EDXStatus = "Running" Then
                    Thread.Sleep(10000)   '600000 = 10 min
                End If
            Loop
            If EDXStatus = "Waiting" Then       'Only assign new end time if status is waiting.
                EDXEndTime = FormatDateTime(Now())
            ElseIf EDXStatus = "Failed" Then
                EDXStartStatus = EDXStatus
            End If
        Else
            EDXStartStatus = TaskName           'Assigns NonEDXTask to the Status
        End If  ' If Not NonEDXTask
        Dts.Variables("User::EDXStatus").Value = EDXStartStatus
        Dts.Variables("User::EDXBeginTime").Value = EDXStartTime
        Dts.Variables("User::EDXEndTime").Value = EDXEndTime
    End Sub
    Function StartEDX(ByVal TaskName)
        '  This Function will Start an EDX Task and return the status of the call.  
        Dim requestKey, xmlhttp
        xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf)
        requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""RequestEDX"" key=""" & requestKey & """><i_TaskIDOrTaskName>" & TaskName & "</i_TaskIDOrTaskName><i_Password /><i_VariableName /><i_VariableValueList /></Global>" & vbCrLf)
        StartEDX = xmlhttp.responseXML.selectSingleNode("//TaskStartResult").text
        Thread.Sleep(20000)   '600000 = 10 min  -- Needed here to give time for EDX to start
    End Function
    Function GetEDXValue(ByVal TaskName, ByVal StatusOrDate)
        Dim requestKey, xmlhttp
        xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf)
        requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
        xmlhttp.open("POST", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTaskStatus"" key=""" & requestKey & """><TaskNameOrId>" & TaskName & "</TaskNameOrId></Global>" & vbCrLf)
        If StatusOrDate = "Status" Then
            GetEDXValue = xmlhttp.responseXML.selectSingleNode("//Status").text
        ElseIf StatusOrDate = "Time" Then
            GetEDXValue = xmlhttp.responseXML.selectSingleNode("//LastExec").text
        End If
    End Function
End Class

Re: EDX - SQL Server

Anonymous — Mon, 14 Nov 2011 14:01:44 GMT

Thanks,

If I'm reading your script correctly, it's evaluate the success of the EDX task, not the document depending on the EDX task or am I wrong?

Starting the EDX takes about a second to complete, but the document to reload may take a lot longer, and i need to se if the document don't fail for some reason during this reload.

henrik

Re: EDX - SQL Server

Anonymous — Mon, 14 Nov 2011 14:10:25 GMT

Take a look at the loop. Basically I keep looping on the EDX task and checking to see if it is running. I only reload one qvw (not a chain) at a time from this script. When the EDX task finishes or errors out the the script will assign a success or failure depending on what is returned.

Re: EDX - SQL Server

Anonymous — Mon, 14 Nov 2011 14:13:41 GMT

Got it!

Thanks!

Re: EDX - SQL Server

Anonymous — Wed, 16 Nov 2011 07:57:04 GMT

Just to inform you all,

I used a modification of the scrip from jsomsen.

It works well.

henrik

Re: EDX - SQL Server

Mon, 08 Jun 2015 11:11:00 GMT

Hi all

I´m trying to execute the code below in a VB 2012 environment, previous to paste the code on QV document, but I always receive the same exception on line:

requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text

"NullReferenceException"

Please, help me!!!!

Best Regards

Jeremias