SQL Server Agent Job - Operating System (CmdExec) Step failing

[arodri]

Hello,

I've never set up this type of job (Operating Sytem (CmdExec)) so I'm completely lost when it comes to the syntax. We have several other jobs that run this type of step, and I'm following the same syntax, but it's not working for me.

All I need to do is execute a Windows command script (.cmd) that will encrypt two text files that I have in the same folder. I tried doing this from Visual Studio, and it worked in Visual Studio, but once I uploaded the VS package to a SQL Server Job it woudln't work, so I thought making this part a seperate step in the job would be the way to go.

Path of my .cmd file that needs to be executed:
d:\MFT\VC\crypt.cmd

I tried putting that directly into the command area in the job step but got the following error:
Files\gnu\gnupg\gpg.exe"" was unexpected at this time. Process Exit Code 255. The step failed.

I also tried in quotes and got the same error.

I would really appreciate any help - Thank you!

 

[Qik3Coder]

e x e c  m a s t e r . d b o . x p _ c m d s h e l l '<your cmd here>'

A lack of experience doesn't prevent you from doing a good job.

 

[arodri]

Is this the right syntax?

exec master.dbo.xp_cmdshell "d:\MCWFTP\VeraCentra\vcencrypt.cmd"

I ran this and got the following error:

The process could not be created for step 2 of job 0xD06DF464FC91A24EBE158A6017822DD2 (reason: The system cannot find the file specified). The step failed.

 

[Qik3Coder]

Should be single quotes. The post engine was freaking out, which is why i had all the spaces. You verified that the file is on the D:\ of the server?

A lack of experience doesn't prevent you from doing a good job.

 

[arodri]

Yes, I can run the file directly from Windows Explorer using the path I listed above and it runs fine. Once I double click the .cmd file, it created two new encrypted documents from 2 text files I already have out there, but in SQL I can't even create the job without this step failing.

I changed it to single quotes, so now my command is:
exec master.dbo.xp_cmdshell 'd:\MCWFTP\VeraCentra\vcencrypt.cmd'

I'm still getting the exact same error =/

 

[Qik3Coder]

are you running the job manually? it may be looking for it on your d:\ you may want to put it on a network share or fully qualify the name.

A lack of experience doesn't prevent you from doing a good job.

 

[arodri]

Yes, I'm running it manually just to test it but it will be scheduled once it works.
The file is on a networks share - but I've been testing it directly from the server that it's on, so there's no way it would be looking for it on my D:\

We also have other similar jobs that run these sorts of steps (none that are trying to execute a .cmd file, but they're executing .exe applications) and here's what one of them has for their command:
"D:\Data\Texg\Mesle.exe" PROCESS

I have no idea what the "PROCESS" does? This one works fine, so that's why I thought it was as simple as listing the path.

Any ideas?

 

[arodri]

Okay, I was finally able to run the job without it failing using:

"d:\MCWFTP\VeraCentra\vcencrypt.cmd" PROCESS

But it's encrypting the wrong documents. Its encrypting txt files in the System32 folder on the C: drive - but it should be encrypting the text files in d:\MCWFTP\VeraCentra

Again, when I double click directly on the .cmd file from windows explorer, it runs fine and encrypts the correct files, but now it's encrypting text files in an entirely different directory!

Here is the message I'm getting:

Executed as user: CWP_ADMIN\Administrator. Starting Script... Fri 06/17/2011 12:24:37.72 Initiating script: "d:\MCWFTP\VeraCentra\vcencrypt.cmd" PROCESS (from directory: C:\WINDOWS\system32) Fri 06/17/2011 12:24:37.72 Encrypting "clusoc.txt" Fri 06/17/2011 12:24:37.72 Encrypting "Crystal_tempEula.txt"Fri 06/17/2011 12:24:37.72 Encrypting "ErrorLog.txt"Fri 06/17/2011 12:24:37.72 Encrypting "eula.old.0.txt" Fri 06/17/2011 12:24:37.72 Encrypting "eula.txt" Fri 06/17/2011 12:24:37.72 Encrypting "FREESSL_COPYRIGHT.txt"Fri 06/17/2011 12:24:37.72 Encrypting "h323log.txt" gpg: can't open `h323log.txt': No such file or directory gpg: h323log.txt: encryption failed: file open error Fri 06/17/2011 12:24:37.72 Encrypting "ibm.txt" Fri 06/17/2011 12:24:37.72 Encrypting "log.txt" gpg: can't open `log.txt': No such file or directory gpg: log.txt: encryption failed: file open error Fri 06/17/2011 12:24:37.72 Encrypting "sfmuam.txt" Script completed. Process Exit Code 0. The step succeeded.

 

[Qik3Coder]

http://msdn.microsoft.com/en-us/library/ms175046.aspx

A lack of experience doesn't prevent you from doing a good job.

 

[Qik3Coder]

So, it looks like you have a relative path issue. Are the files always in the same place? So that you could just hardcode the full path in the .cmd file? Otherwise you'll have to add a parameter to the cmd which is the starting directory.

A lack of experience doesn't prevent you from doing a good job.

 

[arodri]

Yes, the files will always be in the same place.
I found this part in the cmd code:

rem
rem Set the absolute pathname of the GPG executable here; leave blank to use PATH or GPG installation default:
set gpgexec=
rem

So since it's blanke, it's using the GPG installation default, which is on C, right? (Sorry! not too good at this kind of stuff)

Would I just put d:\MCWFTP\VeraCentra\ in that part and save it?

 

[Qik3Coder]

It found the encrypter, so you're probably fine there.
There should be another path type variable that it's using to get the directory to process, but it's probably blank, so it's using the directy the "prompt" is at currently "c:\system32\"

A lack of experience doesn't prevent you from doing a good job.