| Author |
 Topic  |
|
|
themar42
Starting Member
8 Posts |
 Posted - 12/04/2012 : 11:29:35
|
Trying to create a job that steps include finding a file; copying that file; then moving the file. Upon completion an email is sent stating that the job was successful, the email subject should contain the source filename...I keep getting error: "Incorrect syntax near 'objfso'. [SQLSTATE 42000] (Error 102) "
Here's what I have so far:
Dim folder, files, newsfileSET objfso = createobject("Scripting.FileSystemObject")SET f = objfso.getfolder("\\server31\C$\test")SET fc = f.filesSET newfile =
fso.createtextfile("\file_.txt", true)
FOR each f1 IN fcIF LEFT(f1.name, 16) = "file_" then
objfso.copyfile f1, "\\server31\C$\move"
objfso.movefile f1, "\\server31\C$\move2"
SET files = f.files
FOR each "file_" IN files
newfile.writeline(fileg_.name)
nextSET objmessage = createobject("CDO.Message")
objmessage.FROM = "email@email.com"
objmessage.TO = "email@email.com"
objmessage.subject = ("the file called".newfile)
objmessage.textbody = "Job successful."
objmessage.send
endIF
next
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/04/2012 : 11:45:11
|
You have a for each .... then
and there is an endif rather than a next (assuming you mean two loops).
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
themar42
Starting Member
8 Posts |
Posted - 12/06/2012 : 11:05:42
|
Here's what I changed it to, now I get the error 'Incorrect syntax near 'objfso'
Dim folder, files, newsfileSET objfso = createobject("Scripting.FileSystemObject")SET f = objfso.getfolder("\\server31\C$\test")SET fc = f.filesSET newfile =
fso.createtextfile("\file_.txt", true)
FOR each f1 IN fc
IF LEFT(f1.name, 16) = "file_" then
objfso.copyfile f1, "\\server31\C$\move"
objfso.movefile f1, "\\server31\C$\move2"
SET files = f.files
FOR each "file_" IN files
newfile.writeline(file_.name)
nextSET objmessage = createobject("CDO.Message")
objmessage.FROM = "email@email.com"
objmessage.TO = "email@email.com"
objmessage.subject = ("the file called".newfile)
objmessage.textbody = "Job successful."
objmessage.send
next
SET objfso = nothingSET objmessage = nothing |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/06/2012 : 11:41:56
|
How are you trying to run this in the agent - an activex script?
Try running it using a debugger.
IF LEFT(f1.name, 16) = "file_"
probably should be IF LEFT(f1.name, 5) = "file_"
You have an "if" and no "end if".
What is this trying to do?
FOR each "file_" IN files
It doesn't need to be there if this is inside the if statement.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
themar42
Starting Member
8 Posts |
Posted - 12/06/2012 : 12:36:06
|
yes this is in the agent as a step, the If LEFT(f1.name, 16) is actually because the prefix of the file name will always be 16 chars long and anything after the underscore would be the date of the file (i.e. xxxxxxxxxxxxxxxx_20121202.txt)so grab any file that starts with those first 16 then copy and move the file. After that, send a email with the file NAMES only (not attachments just the names of the files) alerting users that they were moved successfully. I'm a beginner so it's been trying. Here is a more simpler version of just the email part which works in the job step.. if I could just add the part where the file name is included in the textbody of the email I'd be golden! thanks...
Type: Active X/ VB script
SET objFSO = CreateObject("Scripting.FileSystemObject")
Set f = objFSO.GetFolder("\\server\hp\folder\folder")
Set fc = f.Files
For Each f1 in fc
if left(f1.name, 16) = "xxxxxxxxxxxxxxxx_" then
objFSO.copyFile f1, "\\server\folders$\"
objFSO.moveFile f1, "\\server\folder\otherfolder\"
set objMessage = CreateObject("CDO.Message")
objMessage.From = "email@email.com"
objMessage.To = "email@email.com"
objMessage.Subject = "success"
objMessage.TextBody = "The file transfer was successful"
objMessage.Send
end if
next
SET objFSO = Nothing
SET objMessage = Nothing |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/06/2012 : 12:43:52
|
how about
objMessage.TextBody = "The file transfer was successful: " & f1
or maybe
objMessage.TextBody = "The file transfer was successful: " & f1.name
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
themar42
Starting Member
8 Posts |
Posted - 12/06/2012 : 13:39:29
|
I believe I'm close, now all I'm getting is VBScript runtime error Error Description: Path not found
|
|
|
|
themar42
Starting Member
8 Posts |
Posted - 12/06/2012 : 14:28:06
|
| I was able to remedy the path issue and got "success" using the suggestion but when I went to check my mail there wasn't an mail, I set the from and to attributes to be my email address inside the domain. Any ideas? |
|
|
| |
Topic |
|
SS2008 Job Agent
