dbaanalystman
MIS
We have a a vbscript that runs perfectly on a Windows Server 2003 machine. We recently moved to a Windows 2008 machine and we can't get it to run properly. We can connect to Active Directory, but I believe our Database connection is faulty. Has anything changed in the wscript or database connection string that would cause a problem??
Thanks.
Script is listed below:
DESCRIPTION: This script sends an email to the appropriate manager of the submitting matrix analyst.
' The script identifies budget transfers that have not bee approved by the manager and
' sends the manager an email each day the transfers have not been approved.
On Error Resume Next
' Setup Oracle connection
Set dbSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = dbSession.OpenDatabase("fcsdev","fcs/redpuppy1", 0)
'Get Matrix employee number
sqlstr = "select distinct(matrix_nbr)as empnum "_
& "from budget_xfers_mo "_
& "where FIN_MGMT_APPR <> 1 "_
& "or fin_mgmt_appr is null"
Wscript.Echo(sqlstr)
set oraData = OraDatabase.CreateDynaset(sqlstr,0)
if oraData.Recordcount > 0 then
adataArray = oraData.GetRows()
Wscript.Echo(adataArray(0,row))
For row = 0 to Ubound(adataArray,2)
Wscript.Echo(adataArray(0,0))
' Get Managers Empnm and Matrix name
errtype = ""
strSub = "uid=" & CLng(adataArray(0,row)) & ",ou=people,o=fcx,c=us"
ADsPath = "LDAP://directory.fcx.com:389/" & strSub
Set oObjSec = GetObject(ADsPath)
empnum = oObjSec.Get("uid")
realname = oObjSec.Get("givenName") & " " & oObjSec.Get("sn")
mgrenbr = parseMgr(oObjSec.Get("manager"))
Wscript.Echo(empnum & " " & mgrenbr)
' Get Managers email address
errtype = ""
strSub = "uid=" & CLng(mgrenbr) & ",ou=people,o=fcx,c=us"
ADsPath = "LDAP://directory.fcx.com:389/" & strSub
Wscript.Echo(strSub)
Set oObjSec = GetObject(ADsPath)
mgremailaddr = oObjSec.Get("mail")
'Wscript.Echo(mgremailaddr)
'Send email notification to finance manager
Set objEmail = CreateObject("CDO.Message")
objEmail.To = sample@email.com 'matemail
objEmail.Cc = "boss@email.com" 'matemail
objEmail.From = "server@email.com"
objEmail.Subject = "Budget Transfer submitted and ready for review/approval"
strbody = "<html><head><body>"
strbody = strbody & "A budget transfer has been submitted by " & realname & " for your approval.<br><br>"
strbody = strbody & "</body></head></html>"
objEmail.HTMLbody = strbody
objEmail.Send
set objEmail = nothing
'objEmail.To = sample@email.com 'matemail
Next
end if
' Close and destroy objects created by this script
'
oraData.close
OraDatabase.close
'
' Reset all Oracle objects
'
set oraData = nothing
set OraDatabase = nothing
set dbsession = nothing
function parseMgr(instr)
outstr = ""
start = 5
incr = 0
while mid(instr,start+incr,1) >= "0" and mid(instr,start+incr,1) <= "9"
outstr = outstr & mid(instr,start+incr,1)
incr = incr + 1
wend
parseMgr = outstr
end function
Thanks.
Script is listed below:
DESCRIPTION: This script sends an email to the appropriate manager of the submitting matrix analyst.
' The script identifies budget transfers that have not bee approved by the manager and
' sends the manager an email each day the transfers have not been approved.
On Error Resume Next
' Setup Oracle connection
Set dbSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = dbSession.OpenDatabase("fcsdev","fcs/redpuppy1", 0)
'Get Matrix employee number
sqlstr = "select distinct(matrix_nbr)as empnum "_
& "from budget_xfers_mo "_
& "where FIN_MGMT_APPR <> 1 "_
& "or fin_mgmt_appr is null"
Wscript.Echo(sqlstr)
set oraData = OraDatabase.CreateDynaset(sqlstr,0)
if oraData.Recordcount > 0 then
adataArray = oraData.GetRows()
Wscript.Echo(adataArray(0,row))
For row = 0 to Ubound(adataArray,2)
Wscript.Echo(adataArray(0,0))
' Get Managers Empnm and Matrix name
errtype = ""
strSub = "uid=" & CLng(adataArray(0,row)) & ",ou=people,o=fcx,c=us"
ADsPath = "LDAP://directory.fcx.com:389/" & strSub
Set oObjSec = GetObject(ADsPath)
empnum = oObjSec.Get("uid")
realname = oObjSec.Get("givenName") & " " & oObjSec.Get("sn")
mgrenbr = parseMgr(oObjSec.Get("manager"))
Wscript.Echo(empnum & " " & mgrenbr)
' Get Managers email address
errtype = ""
strSub = "uid=" & CLng(mgrenbr) & ",ou=people,o=fcx,c=us"
ADsPath = "LDAP://directory.fcx.com:389/" & strSub
Wscript.Echo(strSub)
Set oObjSec = GetObject(ADsPath)
mgremailaddr = oObjSec.Get("mail")
'Wscript.Echo(mgremailaddr)
'Send email notification to finance manager
Set objEmail = CreateObject("CDO.Message")
objEmail.To = sample@email.com 'matemail
objEmail.Cc = "boss@email.com" 'matemail
objEmail.From = "server@email.com"
objEmail.Subject = "Budget Transfer submitted and ready for review/approval"
strbody = "<html><head><body>"
strbody = strbody & "A budget transfer has been submitted by " & realname & " for your approval.<br><br>"
strbody = strbody & "</body></head></html>"
objEmail.HTMLbody = strbody
objEmail.Send
set objEmail = nothing
'objEmail.To = sample@email.com 'matemail
Next
end if
' Close and destroy objects created by this script
'
oraData.close
OraDatabase.close
'
' Reset all Oracle objects
'
set oraData = nothing
set OraDatabase = nothing
set dbsession = nothing
function parseMgr(instr)
outstr = ""
start = 5
incr = 0
while mid(instr,start+incr,1) >= "0" and mid(instr,start+incr,1) <= "9"
outstr = outstr & mid(instr,start+incr,1)
incr = incr + 1
wend
parseMgr = outstr
end function
