Author |
Topic |
themar42
Starting Member
8 Posts |
Posted - 2012-12-04 : 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_" thenobjfso.copyfile f1, "\\server31\C$\move"objfso.movefile f1, "\\server31\C$\move2"SET files = f.filesFOR each "file_" IN filesnewfile.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.sendendIFnext |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-04 : 11:45:11
|
You have a for each .... thenand 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 - 2012-12-06 : 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 fcIF LEFT(f1.name, 16) = "file_" thenobjfso.copyfile f1, "\\server31\C$\move"objfso.movefile f1, "\\server31\C$\move2"SET files = f.filesFOR each "file_" IN filesnewfile.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.sendnextSET objfso = nothingSET objmessage = nothing |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-06 : 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 filesIt 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 - 2012-12-06 : 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 scriptSET 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 ifnextSET objFSO = NothingSET objMessage = Nothing |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-06 : 12:43:52
|
how aboutobjMessage.TextBody = "The file transfer was successful: " & f1or maybeobjMessage.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 - 2012-12-06 : 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 - 2012-12-06 : 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? |
|
|
|