Creating SQL array 1

[KALASHNICOV]

Hi all,

i have been using thread329-1266538 to help me but i seem to have hit a wall.

I have written a client log on script, that starts a particular group of computers in the AM. This then writes to a table in SQL when a PC starts.

My next stage is to use data from a view i have created, that will email the systems dept a list of any machines that dont start up..

Im stuck!!!!!!!

Set CON = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
c = 0


CON.Mode = adModeReadWrite
CON.Open "DRIVER={SQL Server};@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
strsql = "SELECT name, mac from computer_not_turnon"
RS.Open strsql, CON

TakeKindArray = RS.GetRows(, , Array("name", "mac"))
RS.Close


Dim iRowLoop, iColLoop
For iRowLoop = 0 to UBound(TakeKindArray, 2)
For iColLoop = 0 to UBound(TakeKindArray, 1)
Response.Write(TakeKindArray(iColLoop, iRowLoop))

SQLStmt.CommandType = 1
Set con.ActiveConnection = cn
RS.Open strsql
TakeKindArray = RS.GetRows(, , Array("name", "mac"))
RS.Close


MsgBox "done"


Do Until rs.EOF
RS.Movenext
Loop
"

Function SendEmail(personfrom, personto, subject, body)
Dim objemail
Set objMessage = CreateObject("CDO.Message")
objMessage.From = personfrom
objMessage.To = personto
objMessage.Subject = Subject
objMessage.Textbody = body
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "ip"
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function

Do Until rs.EOF

MsgBox name + Mac

RS.Movenext
Loop

 

[theniteowl]

What part is not working? Just when it sends the email or prior?
Have you tested the email portion in a script of it's own to make sure it is working?

Are you sure that CDO is installed on working on the system running the script? And are you using CDONTS or CDOSYS as they have some differences in how you use them.

At my age I still learn something new every day, but I forget two others.

 

[KALASHNICOV]

i would say my script skills are more luck than judgement! i seem to muddle though.

seems to be a problem with the array, im not sure if it is passing variables correctly.. to them send the emails but at present the script does not get much further than line 39 - just before the email function.

the CDO is def installed and working.

as for "And are you using CDONTS or CDOSYS as they have some differences in how you use them" i havnt a clue how to check this? sorry, i'm a novice squire.

 

[PHV]

You have 2 For loops without any Next !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KALASHNICOV]

Thanks,

I'm so close I can taste it. I have tested all segments of this query. The email function, and the array works independently. The trusty message box proves it... I just can’t seem to pass the data from my array into the email... so close I can smell it.. any help would be great!

here is the new script:

Dim cn, rst, c, strsql, Comp()
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
c = 0
ReDim Preserve Comp(0)
cn.Mode = adModeReadWrite
cn.Open "DRIVER={SQL Server};@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
strsql = "SELECT name, mac from computer_not_turnon"
rst.Open strsql, cn
rst.MoveFirst
Do Until rst.EOF
'MsgBox rst("Name")
ReDim Preserve Comp(c)
Comp(c) = rst("Name") & " - " & rst("mac")
c = c + 1
rst.MoveNext
Loop
rst.Close
cn.Close
'outstr = "Job Machines that have not turned on "


For i = 0 to UBound(Comp)

OutStr = outstr & chr(13) & Comp(i) & chr(13)


Next
'MsgBox outstr - this returns the computer name, and mac address

email "administrator@","m-ellis@",objnetwork.ComputerName + ".","Job Machine successfully Started" - ????? here is where i am struggling ?????????

Function email(personfrom, personto, subject,body)
Set objMessage = CreateObject("CDO.Message")
objMessage.From = personfrom
objMessage.To = personto
objMessage.Subject = Subject
objMessage.Textbody = body
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "ip"
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function

 

[PHV]

Something like this ?
email "administrator@","m-ellis@",objnetwork.ComputerName & ".","Job Machines that have not turned on " & OutStr

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KALASHNICOV]

PHV you are a legend... can't believe i missed that! solved!

 

[KALASHNICOV]

one more quick question, i would like to be able to truncate the SQL table once the script is complete, is this possibe with VBS? and if so any pointers?

 

[PHV]

What is your meaning of truncating a SQL table ?
I suspect you have to use an ADODB.Command object or the Execute method of your ADODB.Connection object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KALASHNICOV]

Once the data is recored, and the email sent. I would like to remove all records, so that the following day when there is new record sets the data will remain accurate.

Thanks PHV

 

[PHV]

...
rst.Close
cn.Execute "DELETE FROM computer_not_turnon"
cn.Close
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KALASHNICOV]

PHV, cheers all working!