November 25, 2003 at 8:53 am
Hi,
I'm having a problem running a script from DTS packet and I'll need some help.
The script stops on the line "CreateObject
("Scripting.FileSystemObject")
The Error: Error Code 0; Error Source:msxml3.dll; Error
Description: The system cannot locate the resource
specified; Error line :26
I did load a patch from MS KB to clear the registry and to
register msxml3.dll. It did not help.
I'm running IE6.28 with SP1 and all security updates.
What I am missing? This is the second time I use xml...so, may it's really simple...
Thanks a lot in advance.
Here is the script:
'**********************************************************
************
' Visual Basic ActiveX Script
'**********************************************************
**************
Dim xmlServers
Function Main()
' Read in the server list
InitServers
' Get the job status and write the web page
GetJobStatus
Main = DTSTaskExecResult_Success
End Function
Sub GetJobStatus()
Dim xmlServer
Dim ServerName
Dim ConnectionString
Dim cn
Dim rs
Dim SQL
Dim JobName
Dim ErrorMessage
Dim RunDate
Dim RunTime
Dim OK
Dim fso
Dim ts
Set fso = CreateObject
("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile
("\\YOUR_SERVER_NAME\c\ISCommon\SQLWebLinks\Utilities\SQLJo
bStatus.htm", 2,-1)
Set cn = CreateObject("ADODB.Connection")
' If this is monday, the include the whole weekend
RunDate = Now()
if DatePart( "w", RunDate ) = 2 then
RunDate = DateAdd("d",-3,RunDate)
else
RunDate = DateAdd("d",-1,RunDate)
end if
ts.WriteLine "<HTML><HEAD>" & _
"<title>Gold Kist - SQL Server Job
Status</title>" & _
"</HEAD>"
ts.WriteLine "<BODY>" & _
"<H3>SQL Server Job Failures</H3>" & _
"<H3>" & FormatDateTime(Now(),vbShortDate)
& "</H3>" & _
"<H3>from " & FormatDateTime
(RunDate,vbShortDate) & " to date</H3>" & _
"<TABLE border=1><TR><TH>Server</TH><TH>Job
Name</TH><TH>Message</TH><TH>Failure Date</TH></TR>"
RunDate = Year(RunDate) * 10000 + Month(RunDate) *
100 + Day(RunDate)
SQL = "select j.name, jh.message, jh.run_date,
jh.run_time" & _
" from sysjobs j" & _
" inner join sysjobhistory jh" & _
" on j.job_id = jh.job_id" & _
" and jh.step_id = 0" & _
" and run_status = 0" & _
" and run_date >= " & RunDate & _
" where j.enabled = 1" & _
" order by j.name, jh.run_date, jh.run_time "
For Each xmlServer In xmlServers.selectNodes
("//Servers/Server")
ServerName = xmlServer.getAttribute("Name")
ConnectionString = "provider=sqloledb;server="
& ServerName & ";database=msdb;"
If xmlServer.getAttribute
("IntegratedSecurity") = "Yes" Then
ConnectionString = ConnectionString
& "Trusted_Connection=yes"
Else
ConnectionString = ConnectionString
& "UID=" & _
xmlServer.getAttribute("UID")
& ";PWD=" & _
xmlServer.getAttribute("PWD")
End If
ts.WriteLine "<TR><TD>" & ServerName & "</TD>"
On Error Resume Next
cn.Open ConnectionString
If Err.Number = 0 Then
On Error GoTo 0
Set rs = cn.Execute(SQL)
Do Until rs.EOF
If Not IsNull(rs("message").Value) Then
JobName = rs("name").Value
ErrorMessage = rs("message").Value
RunDate = DateSerial(rs
("run_date").Value / 10000, rs("run_date").Value / 100 Mod
100, rs("run_date").Value Mod 100)
RunTime = TimeSerial(rs
("run_time").Value / 10000, rs("run_time").Value / 100 Mod
100, rs("run_time").Value Mod 100)
ts.WriteLine "<TR><TD>" &
ServerName & "</TD><TD>" & JobName & _
"</TD><TD>" & ErrorMessage & _
"</TD><TD>" & FormatDateTime
(RunDate,vbshortdate) & _
" " & FormatDateTime(RunTime,
vbShortTime) & "</TD>"
End If
ts.WriteLine "</TR>"
rs.MoveNext
Loop
rs.Close
cn.Close
Else
ts.WriteLine "<TD>Unable to connect:" &
Err.Description & "</TD></TR>"
End If
On Error GoTo 0
Next
ts.WriteLine "</TABLE></BODY></HTML>"
ts.Close
Set ts = Nothing
Set fso = Nothing
End Sub
Sub InitServers()
set xmlServers = createObject("MSXML.DOMDocument")
xmlServers.Load "\\YOUR_SERVER_NAME\c\SQLUtilitiesSQLServers.ini"
End Sub
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply