SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SS2008 Job Agent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

themar42
Starting Member

8 Posts

Posted - 12/04/2012 :  11:29:35  Show Profile  Reply with Quote
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
3383 Posts

Posted - 12/04/2012 :  11:45:11  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 - 12/06/2012 :  11:05:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  11:41:56  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 - 12/06/2012 :  12:36:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  12:43:52  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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 - 12/06/2012 :  13:39:29  Show Profile  Reply with Quote
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 - 12/06/2012 :  14:28:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000