SQL Server Performance

Help with ActiveX Script Task In DTS

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by walidha, May 12, 2005.

  1. walidha New Member

    Hello. I have the below code in an ActiveX Script Tas in a DTS package. The code works fine until the code "objU1.send objComm1". When I run the package it fails with the follwing error:<br /><br />Error Code: 0<br />Error Source= msxml3.dll<br />Error Description: The download of the specified resource has failed.<br /><br />Error on Line 71<br /><br />This is the code:<br />'**********************************************************************<br />' Visual Basic ActiveX Script<br />'************************************************************************<br /><br />Function Main()<br />Dim ParamUIP<br />Dim ParamMA<br />Dim ParamUSER<br />Dim ParamPASS<br />Dim ParamEXP<br />Dim ParamTVALUE<br />Dim ParamRN<br />Dim ParamIPUP<br />Dim ParamPAYM<br />Dim ParamCONF<br />Dim ParamPAY<br />Dim passparam<br /><br />Dim VisitorId<br /> Dim cn<br />Dim rs<br /><br />set cn = CreateObject("ADODB.Connection")<br />set rs = CreateObject("ADODB.Recordset")<br /><br /><br />VisitorId= 54 'Request.QueryString("VisitorId")<br /><br />cn.open "Provider=SQLOLEDB; Data Source=111.111.111.111; Initial Catalog=database1; User Id=username; Password=apssword;"<br /> rs.Open "select Username, Password, IPAddress, AccessTime from visitors where [ID] = " & VisitorId,cn<br /><br />if not rs.eof then<br />ParamUIP = rs("IPAddress")<br /> ParamMA = ""<br />ParamUSER = rs("Username")<br />ParamPASS = rs("Password")<br />ParamEXP = rs("AccessTime")<br /> ParamTVALUE = "HOURS"<br />ParamRN = "" <br />ParamIPUP = ""<br /> ParamPAYM = ""<br />ParamCONF = ""<br />ParamPAY = ""<br /><br />Dim objComm1<br />Dim USGIP<br />Dim objU1<br /><br />set objComm1 = CreateObject("Microsoft.XMLDOM")<br /><br /><br />objComm1.async = false<br /><br />passparam = "&lt;USG COMMAND='USER_ADD' MAC_ADDR='"<br />passparam = passparam & ParamMA & "'&gt;"<br />passparam = passparam & "&lt;USER_NAME&gt;" & ParamUSER & "&lt;/USER_NAME&gt;"<br />passparam = passparam & "&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />ASSWORD ENCRYPT=""TRUE""&gt;" & ParamPASS & "&lt;/PASSWORD&gt;"<br />passparam = passparam & "&lt;EXPIRY_TIME UNITS='" & ParamTVALUE & "'&gt;"& ParamEXP & "&lt;/EXPIRY_TIME&gt;"<br />passparam = passparam & "&lt;ROOM_NUMBER&gt;" & ParamRN & "&lt;/ROOM_NUMBER&gt;"<br />passparam = passparam & "&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />AYMENT_METHOD&gt;" & ParamPAYM & "&lt;/PAYMENT_METHOD&gt;"<br />passparam = passparam & "&lt;IP_TYPE&gt;" & ParamIPUP & "&lt;/IP_TYPE&gt;"<br />passparam = passparam & "&lt;CONFIRMATION&gt;" & ParamCONF & "&lt;/CONFIRMATION&gt;"<br />passparam = passparam & "&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />AYMENT&gt;" & ParamPAY & "&lt;/PAYMENT&gt;"<br />passparam = passparam & "&lt;/USG&gt;"<br /><br />objComm1.loadXML passparam<br /><br />USGIP = "http://" & ParamUIP & ":1111/usg/command.xml"<br /><br />set objU1 = CreateObject("Microsoft.XMLHTTP")<br />objU1.open "POST", USGIP, false<br />objU1.send objComm1 ' &lt;=== This is where the package fails!!!<br /><br />MsgBox(objU1.responseText)<br /><br />set objComm1 = nothing<br />set objU1 = nothing<br />set ParamUIP = nothing<br />set ParamMA = nothing<br />set ParamUSER = nothing<br />set ParamPASS = nothing<br />set ParamEXP = nothing<br />set ParamTVALUE = nothing<br />set ParamRN = nothing<br />set ParamIPUP = nothing<br />set ParamPAYM = nothing<br />set ParamCONF = nothing<br />set ParamPAY = nothing<br />end if<br /><br />rs.close<br />cn.close<br />set rs = nothing<br />set cn = nothing<br /><br />Main = DTSTaskExecResult_Success<br />End Function<br />'**************************************<br /><br />Please anyone has an idea on this?<br /><br />Thanks.
  2. Argyle New Member

    Can the sql server actually reach USGIP = "http://" & ParamUIP & ":1111/usg/command.xml"?

    Try it in a web browser first. Also verify that ParamUIP is not blank.

  3. walidha New Member

    It works just fine in a web browser and yes ParamUIP is not blank it gets filled in with all the value needed.
  4. Argyle New Member

    Can you check the web server logs if the request even reach the web server. If there is nothing in the logs there is something wrong in your send statment.

    Also execute the package on the actual server and not from your workstation for example. Since then msxml3.dll on the client might be used which is the workstation and not the server in that case.

    Might be some help here too:
    http://groups.google.se/groups?hl=s...resource%20has%20failed&hl=sv&lr=&sa=N&tab=wg

Share This Page