Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SS2008 Job Agent

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_" 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-04 : 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.
Go to Top of Page

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 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
Go to Top of Page

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 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.
Go to Top of Page

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 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
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-06 : 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.
Go to Top of Page

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

Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -