1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
| '**********************************************************************
' Script Visual Basic ActiveX
'************************************************************************
Function Main()
dim connS
dim rs
dim nom
dim serveur
dim messsage
dim datetime
dim duree
set connS = CreateObject("ADODB.Connection")
connS.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxxx;User Id=xxx;Password=xxxx;'"
connS.Open
Set rs = connS.Execute("select S.server_id as nserveur, S.last_outcome_message as message, J.name, convert( datetime, convert( varchar , last_run_date ), 112 ) + convert( datetime, stuff( stuff( replicate ('0', 6 - len (convert(varchar, last_run_time )) ) + convert(varchar, last_run_time ), 3, 0 , ':') , 6, 0 , ':' ) , 108) date_heure_derniere_execution, convert( varchar, stuff( stuff( replicate ('0', 6 - len (convert(varchar, last_run_duration )) ) + convert(varchar, last_run_duration ), 3, 0 , ':') , 6, 0 , ':' ) , 108) duree "&_
" from sysjobservers S"&_
"inner join sysjobs J on J.job_id = S.job_id"_&
" where S.last_run_outcome <> 1")
do until rs.EOF
nom = rs.fields("name").value
serveur = rs.fields("serveur_id").value
messsage = rs.fields("last_outcome_message").value
datetime = rs.fields("date_heure_derniere_execution").value
duree = rs.fields("duree").value
connS.Execute("declare @Mail int "&_
"Declare @datecreated as varchar(30)"&_
"Declare @string as varchar(2000)"&_
"Set @datecreated = REPLACE(CONVERT(VARCHAR(8),Getdate(),1),'/','')"&_
"SELECT @string = 'exec master.dbo.xp_smtp_sendmail"&_
"@FROM = ''xxxx@xxx.fr'',"&_
"@FROM_NAME = ''test'',"&_
"@TO = ''xxx@xxx.fr'',"&_
"--@CC = ''xx@xxx.fr'',"&_
"--@priority = ''HIGH'',"&_
"@subject = ''Ft Myers Link File'',"&_
"@message = ''Le lot portant le nom : '" & nom & "' ne s'est pas deroulé comme prévu. Information complémentaire : n° du serveur :'" & serveur & "' message : '" & message & "' date et heure de la derniere execution :'" & datetime & "' Durée :'" & duree & "' erreur du :'" & @datecreated & "'' "&_
"@type = ''text/plain'',"&_
"@server = ''srv-bcexch01''"&_
"Exec (@string)"&_
"select MAIL = @mail')"&
rs.movenext
loop
rs.Close
Set rs = Nothing
connS.Close
Set connS = Nothing
Main = DTSTaskExecResult_Success
End Function |
