March 31, 2004 at 1:28 am
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
April 5, 2004 at 8:00 am
This was removed by the editor as SPAM
April 3, 2012 at 9:59 am
Hi! How many records you entered? Perhaps the problem is how often is called the job.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply