Hi Guys

Anyone experienced this error. The message is:

The command script does not destroy all the objects that it creates

I'm trying to set up a scheduled job (VB) to send out follow up mails.

Have searched on the error, but I'm not really satisfied with the answers (mainly 'It's a bug in SQL Server')

Could someone check out my script?

Const adCmdStoredProc = &H0004

Const objConn = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=xxxx;User ID=xxxx; Password=xxxx"

Main

Function Main()

    Dim objCmd
    Dim objRS

    Set objCmd = CreateObject("ADODB.Command")

    objCmd.ActiveConnection = objConn
    objCmd.CommandType = adCmdStoredProc
    objCmd.CommandText = "PrSendFollowUp"

    Set objRS = objCmd.Execute

    If Not objRS.EOF Then

        Do Until objRS.EOF

            Email = objRS("Email")
            ReferralID = objRS("ReferralID")

            SendMail(Email)
            UpdateStatus(ReferralID)

            objRS.MoveNext
        Loop

    Else

        Exit Function

    End IF

    objRS.Close

    Set objRS = Nothing

    Set objCmd = Nothing

End Function

Sub SendMail(Email)

    Dim objMail

    Set objMail = CreateObject("CDONTS.NewMail")

    objMail.From = """Joe Ingle""<[email protected]>"
    objMail.To = ""&Email&""
    objMail.Subject = "Referral Service - Follow Up"
    objMail.BodyFormat = 0
    objMail.MailFormat = 0
    objMail.Body = "Test"
    objMail.Send


    Set objMail = Nothing

End Sub

Sub UpdateStatus(ReferralID)

    Dim objCmd1

    Set objCmd1 = CreateObject("ADODB.Command")
    objCmd1.ActiveConnection = objConn
    objCmd1.CommandType = adCmdStoredProc
    objCmd1.CommandText = "PrSendFollowUpUpdateStatus"

    objCmd1.Parameters.Append .CreateParameter("@ReferralID", 202, adParamInput, 8, ReferralID)

    objCmd1.Execute

    Set objCmd1 = Nothing

End Sub

Cheers

Joe

Joe, try losing the if..End if clause, it's unnecessary here.

HTH

There are two secrets to success in this world:
1. Never tell everything you know

Chris

I originally wrote the script without the If Else etc. but same error. That's why I put it in, completeness and all that.

Thanks for your time.

joe

ActiveX scripts in a SQL Job appear to have a major bug in that they report Error: 14277 whenever the string "createobject(" appears more than once anywhere in the script. It does not matter whether the string is just part of a character expression such as: sTemp = "..... createobject( ...."; whether it appears in a comment or whether it is used to actually create an object. Any combination of the above that puts "createobject(" in the script more than once will cause the 14277 error to appear when you try to close the Job modifier.

There is a trick that I have found to overcome this. That is to create any and all objects in a single common sub routine. Even in this sub routine, you have to trick the system into thinking that you have just destroyed the object that you are trying to create.

The sub routine is cobj. It takes the variable that will become the object and a string that defines the activex control. The "set ... = Nothing" that appears after the "Exit Sub" is the trick that makes the system think that the object is destroyed within the scope of cobj. Note: be sure to destroy the object in the scope where the object variable was defined.

Here is code sample that sends an email using ASPMAIL, which contains data from a SQL query.

MailMe readSQL(1006), "mymail @ mail.com"

Sub cobj(newobj, ax)
   Set newobj = createobject(ax)
   exit sub
   Set newobj = Nothing
End Sub

sub MailMe (sMsg, sAddress)
   dim Mailer, vRet
   if instr(sAddress,"@")<1 then exit sub

   cobj Mailer, "SMTPsvg.Mailer"

   Mailer.FromName = "ASP_Debug"
   Mailer.FromAddress = sAddress
   Mailer.RemoteHost = "127.0.0.1"
   Mailer.AddRecipient "", sAddress
   Mailer.Subject = "Debug ActiveX Script - 14277 Error"
   Mailer.BodyText = sMsg
   Mailer.SendMail
   Set Mailer=Nothing
end sub

Function readSQL(ndx)
   Dim SQL, sConn, oRst
   readSQL = "No Record"
   SQL = "SELECT Note FROM NoteTable WHERE [ID]=" & CStr(ndx)
   sConn = "Provider=SQLOLEDB.1;Initial Catalog=xx;Data Source=zz"

   cobj oRst, "ADODB.Recordset"

   oRst.Open SQL, sConn
   If oRst.State = 1 Then
      readSQL = oRst(0)
      oRst.Close
   End If
   Set oRst = Nothing
End Function