EDX - SQL Server

[#1]

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

[#2]

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

[#3]

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

[#4]

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.

[Anonymous]

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

[erichshiino]

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

[#7]

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

[erichshiino]

Thanks a lot!

[Anonymous]

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

[rwunderlich]

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