"Cannot redirect" error from xp_cmdshell

[RichardHayes]

I am trying to run a simple DOS command using xp_cmdshell, which works every time when typed directly into a command prompt.

The command is designed to convert an RTF file to text and is along the lines of:

rtfconv -crtf -ciso-1 notes.rtf txt

I get the error "Cannot redirect" and after extensive searching I cannot work out what the problem is.

Any help would be greatly appreciated.

 

[ClaireHCM]

Did you try putting this code in a batch file and executing the batch through command shell?

 

[ClayG]

When you try this on a command prompt, is that a command prompt on the server ? Its easy to forget that xp_cmdshell executes on the server. And then consider what username it executes as - mostly this is the username that runs SQLAgent, see BOL.

 

[RichardHayes]

I have tried creating a batch file and I get the same error.

I have also tried executing the command directly on the server, and it works fine there.

How would I check what username it is using? How would I change it?

Thanks for your comments.

Richard.

 

[billchris]

Here are a few things to look into:

Check to make sure that the NT account that SQL Server runs xp_cmdshell in has permission to execute the command. That includes the permission to write to the directory where the output file will reside and read from the directory where the rtf source file exists.

On SQL 2000, if the SQL login that is executing xp_cmdshell is not part of the sysadmin group, then SQL Server uses the NT account defined by the xp_sqlagent_proxy_account procedure. You can see what NT account that is by executing

  EXEC master.dbo.xp_sqlagent_proxy_account N'GET'

There are a lot more convoluted permission restrictions associated with xp_cmdshell. You can see all the details in the Remarks section of this page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

“I have always found that plans are useless, but planning is indispensable.” --Dwight Eisenhower

 

[RichardHayes]

It turns out that all I needed to do was add "> NUL:" to the end of the command being executed. It only works with a slight variation of the command line, but the final working piece of code is as follows.

DECLARE @cmd VARCHAR(1000)
SET @cmd = '\\PathOfComFile\rtfconv.com -crtf -ciso-1 \\path\notes.rtf > \\path\notes.txt < NUL:'
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

Thank you all for your help.

Richard.