Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_cmdshell


xp_cmdshell

Author
Message
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
What authentication method are you using to connect to SQL Server, Windows authentication or SQL Authentication?


* Noel
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
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
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3336 Visits: 932
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.



Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
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
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3336 Visits: 932
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'



Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

SQL_ABD
SQL_ABD
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 1349
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
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
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
TheRedneckDBA
TheRedneckDBA
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2592
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
SQL_ABD
SQL_ABD
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1332 Visits: 1349
Thanks Jason for sharing the info.

Regards,
Ahmed
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search