18 Replies Latest reply: Jun 8, 2015 7:11 AM by Jeremias Trompeta RSS

    EDX  - SQL Server

      Hi

      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

          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

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

              • EDX  - SQL Server
                Erich Shiino

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

                  • EDX  - SQL Server

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

                      • EDX  - SQL Server
                        Erich Shiino

                        Thanks a lot!

                          • EDX  - SQL Server
                            Jerry Somsen

                            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,

                             

                            JS

                              • EDX  - SQL Server
                                Rob Wunderlich

                                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
                                    Jerry Somsen

                                    Thanks!  That is what we were hoping to hear!

                                     

                                    JS

                                      • EDX  - SQL Server
                                        Jerry Somsen

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

                                         

                                        JS

                                          • EDX  - SQL Server
                                            Henrik Matz

                                            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
                                                Jerry Somsen

                                                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
                                                
                                                

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                                                 

                              • EDX  - SQL Server
                                jerry.svensson

                                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
                                  Jerry Somsen

                                  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

                                    • Re: EDX  - SQL Server

                                      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