Running Activex Script as a SQL Job

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  March 31, 2004 at 1:18 am

  #160791

  Hello Gurus,

  I want to know the steps to execute an activex script through a job which will be called from a trigger.

  I am having 2 tables ie.,

  tblMsg

  ------

  Msgid int,

  Studid int,

  Msgtype int,

  Msgsub varchar(100)

  tblMsgDtl

  ---------

  MsgDtlid int,

  Msgid  int,

  Sendto  varchar(100),

  Content  text,

  Status  int

  It is a sort of SMS messaging where the "Msgtype" = 2. The relation between these two tables is "Msgid" (one to many relation).

  Now my requirement is to know how the avtivex script when run as a job which will be called in a trigger will execute. I have written a trigger "after insert" on the second table ie., tblMsgDtl as follows:

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

  Create trigger SMS on dbo.tblMsgDtl

  after insert

  as

   exec msdb..sp_start_job 'SMSActivex'

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

  and the activex script "SMSActivex" is as follows:

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

  Dim smsgateway,Cn,rsUsers,strSQL, strMsgIds

  smsgateway = ""  -- gateway address

  Set Cn = CreateObject("ADODB.Connection")

  Cn.Open "Provider=sqloledb;Data Source=<Servername>;Initial Catalog=<Database name>;User Id=<Username>;Password=<Password>;"

  Set rsUsers= CreateObject("ADODB.RecordSet")

  strSQL = " "  -- selecting the records where the "Msgtype" = 2 and "Status" = 0

  rsUsers.Open strSQL, Cn, 3, 1

  If rsUsers.RecordCount > 0 Then

   Dim arMsgIds()

   ReDim arMsgIds(rsUsers.RecordCount-1)

   Dim strUsername, strPwd, strPriority, strData, strCellPhone   

   strUsername = " " -- username to connect to the gateway

   strPwd  = " "  -- password

   strPriority = "normal"

   strData=rsUsers("Body")

   Dim xmlDOMDocument

   Set xmlDOMDocument=CreateObject("MSXML2.DOMDocument")

   Set rootElement=xmlDOMDocument.createElement("itouchCMA")

    xmlDOMDocument.appendChild rootElement

   Set headerNode=xmlDOMDocument.createElement("auth")

    rootElement.appendChild headerNode

   Set user=xmlDOMDocument.createElement("name")

    user.Text=strUsername

    headerNode.appendChild user

   Set password=xmlDOMDocument.createElement("password")

    password.Text=strPwd

    headerNode.appendChild password

   Set headerNode1=xmlDOMDocument.createElement("message-submit")

    rootElement.appendChild headerNode1

   Set priority=xmlDOMDocument.createElement("priority")

    priority.Text="normal"

    headerNode1.appendChild priority

   Set validity=xmlDOMDocument.createElement("validity")

    validity.Text="7"

    headerNode1.appendChild validity

   Set contenttype=xmlDOMDocument.createElement("content-type")

    contenttype.Text = "text"

    headerNode1.appendChild contenttype

   Set message=xmlDOMDocument.createElement("message")

    headerNode1.appendChild message

   Set Data=xmlDOMDocument.createElement("data")

    Data.Text=strData

    message.appendChild Data

   Do Until rsUsers.EOF

    Set cellphone=xmlDOMDocument.createElement("cellphone")

    cellphone.Text=rsUsers("RECIPIENT")

    message.appendChild cellphone

    arMsgIds(rsUsers.AbsolutePosition-1)=rsUsers("MSGDTLID")

   rsUsers.MoveNext

   Loop

   strMsgIds=Join(arMSgIds, ",")

   Dim xmlRequest

   xmlRequest="<?xml version=""1.0"" encoding=""UTF-8""?>" & xmlDOMDocument.xml

   Set oHTTP=CreateObject("Microsoft.XMLHTTP")

    oHTTP.open "POST", smsgateway , False

    oHTTP.Send xmlRequest

   Dim xmlDOMDocumentr

   Set xmlDOMDocumentr = CreateObject("MSXML2.DOMDocument")

    xmlDOMDocumentr.Load (oHTTP.responseText)

   If Instr(1, oHTTP.responseText, "<user-message id=""snd-003"">") > 0 Then

   Cn.Execute " "  -- updating the status to 1 when it is successful.

   Else

   Cn.Execute " "  -- updating the status to 2 when it is unsuccessful.

   End If

  End If

  rsUsers.close

  Set rsUsers=Nothing

  Cn.close

  Set Cn=Nothing

  Set xmlDOMDocument=Nothing

  Set oHTTP=Nothing

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

  If the activex script is executed individually, then the process is fine. If it is executed from a trigger by calling the job to start, then the following error message is being fired as follows:

  SQLServerAgent Error: Request to run job SMSActivex (from User sa) refused because the job is already running from a request by User sa.

  When i check it up in the enterprise manager->Server->Management->SQL Server Agent->Jobs->SMSActivex

  the status is being shown as executing step 1

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

  My System Configuration:

  Operating System : Microsoft Windows 2000 Advanced Server (SP 4)

  SQL Server: SQL Server 2000 Enterprise Edition (SP 3)

   

  
  Lucky

• K. Brian Kelley

  SSC Guru

  Points: 114642

  More actions

  March 31, 2004 at 8:10 am

  #501293

  The problem isn't that you're trying to execute the ActiveX script multiple times. The problem is your trigger is calling a SQL Agent job and only one instance of a job can run at a time. With a trigger you have the potential to fire off a request to start the job multiple times almost simultaneously and that's probably where you are running into trouble.

  K. Brian Kelley
  @kbriankelley

• Benny Thomas

  SSC Journeyman

  Points: 85

  More actions

  April 3, 2004 at 4:33 am

  #501695

   Hi Brian ,

  Need ur help pls.

  I m trying to find out a way to load XML from a file structure to SQLServer, and also need a functionality to create xml based on data in SQLServer.

   

  There might be about 100 files to load and created daily

  Do you think this can be accomplished by using stored procedures, or do you prefer doing it in .NET.

  Regards

  Benny