December 12, 2007 at 1:39 pm
I'm having a little trouble with xp_cmdshell...wonder if anyone can spot anything incredibly stupid I'm missing.
This pdfcombine program just combines the first 2 files into one .pdf file. If I run the script below, it prints out 'GOOD'. along with the value of @CMD. But the file never gets created. However if I open a command prompt window using the same security context as the sqlserveragent account, copy the output from the PRINT @CMD, and run it, it works.
Am I missing something obvious? I've tried various combinations of quotes, and have even tried a full UNC path. In all cases, running the command in the command line works, but stops working as soon as I'm in SQL server. But I can do something simple like SET @CMD = 'DIR D:\' and that works.
DECLARE @CMD VARCHAR(512)
DECLARE @RESULT INT
SET @CMD =
'D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF'
PRINT @CMD
EXECUTE @RESULT = master.dbo.xp_cmdshell @CMD
IF @RESULT = 0
PRINT 'GOOD'
ELSE
PRINT 'BAD'
The Redneck DBA
December 12, 2007 at 2:13 pm
What authentication method are you using to connect to SQL Server, Windows authentication or SQL Authentication?
* Noel
December 12, 2007 at 2:18 pm
noeld (12/12/2007)
What authentication method are you using to connect to SQL Server, Windows authentication or SQL Authentication?
Windows Authentication. The user is both a sysadmin, and a local admin on the server involved.
The Redneck DBA
December 12, 2007 at 2:54 pm
What service account is SQL Server running under? Make sure it has the necessary NTFS permissions to run the executable and write to the destination folder.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 12, 2007 at 2:58 pm
Scott Duncan (12/12/2007)
What service account is SQL Server running under? Make sure it has the necessary NTFS permissions to run the executable and write to the destination folder.
I don't think it's permissions. I logged onto the server as the user that the services are running under, and that's what I'm using to manually execute from the command prompt.
It's got to be something just silly I'm missing...
The Redneck DBA
December 12, 2007 at 3:23 pm
What happens if you try running:
master..xp_cmdshell 'D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF'
Are any error messages returned? Or is it just the never helpful 'NULL'
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 13, 2007 at 4:27 am
Hi,
The code is good
Try just to execute in the command line
D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF to see if it works
Check the following link
http://www.coolutils.com/forum/read.php?f=1&i=914&t=914
Regards,
Ahmed
December 13, 2007 at 8:05 am
Ahmed Bouzamondo (12/13/2007)
Hi,The code is good
Try just to execute in the command line
D:\JPMorgan_IMPORT\pdfcombine.exe D:\431411406.PDF D:\431411408.PDF D:\COMBINED.PDF to see if it works
Check the following link
http://www.coolutils.com/forum/read.php?f=1&i=914&t=914
Regards,
Ahmed
That's the strange thing. It works fine from the command line (when running with the same user the service accounts are running as).
I've even tried putting a bunch of those commands in a batch file, and just running the batch file from xp_cmdshell. It doesn't work. But if I run the batch file from the command line it works again.
Normally I would think it's a permission error, but since xp_cmdshell (at least according to books online) runs with the credentials of the sql server agent service account, and I can log on as that service account to the desktop and run it manually with sucess, I'm not sure it's security. I can do simple things like:
...
xp_cmdshell 'dir d:\'
And that works, so I also don't think it's any obvous configuration issue either.
The Redneck DBA
December 13, 2007 at 1:12 pm
It's finally solved.
Our network guy who(m) is much more clever than me added an entry in the PATH variable and it magically started working. Apparently the pdfcombine program was having troulbe finding the license info.
One interesting that that was discovered:
Run the following: EXECUTE master.dbo.xp_cmdshell 'SET'
and then run SET from the command prompt. (make sure you are logged in as the account that the agent is running as). For me it's returning different things in the 'PATH' variable.
The Redneck DBA
December 13, 2007 at 1:14 pm
Thanks Jason for sharing the info.
Regards,
Ahmed
December 13, 2007 at 1:47 pm
Run the following: EXECUTE master.dbo.xp_cmdshell 'SET'
and then run SET from the command prompt. (make sure you are logged in as the account that the agent is running as). For me it's returning different things in the 'PATH' variable.
That's because SQL Server set's up it's own environment when it boots and each "command session" has it's own environment. You can simulate the same thing... open two CMD windows... make a bunch of changes in one... do a SET to verify the changes... do a SET in the other... you'll see nothing changed. Make changes in the second CMD window and verify that you can't see thos changes in the first. Open a 3rd CMD window and verify that none of the changes appear.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply