We are using xp_cmdshell in a trigger to call a VB program and pass to it
required parameters.  The call with the parameters in the desired structure
is below (code stripped down to bare essentials):

declare @sDirectory varchar(200), @sBartender varchar(200), @sOutput
varchar(200), @sFormat varchar(3), @sFileName varchar(50), @sCommand
varchar(500)

set @sOutput = '48,2,4,07901372,00001,CA,TEST DESCRIPTION,10061'
select @sFormat = '004'
set @sFileName = 'testme'
set @sDirectory = '\\apioptr1\c$\barcode\data\' + @sFormat + '\' +
@sFileName + '.dat'
set @sBartender = '%BTW% /AF=\\apioptr1\c$\barcode\labels\004\RFID2x4.btw
/D=<Trigger File Name> /PRN="\\apioptr1\apio168" /R=3 /P /DD~%END%~'
set @sCommand = '\\apioptr1\c$\barcode\app\create_text_file ' + @sDirectory
+ '@' + @sBartender + @sOutput
PRINT @sCommand
execute master..xp_cmdshell @sCommand

The printed output of @sCommand is:
\\apioptr1\c$\barcode\app\create_text_file
\\apioptr1\c$\barcode\data\004\testme.dat@%BTW%
/AF=\\apioptr1\c$\barcode\labels\004\RFID2x4.btw /D=<Trigger File Name>
/PRN="\\apioptr1\apio168" /R=3 /P /DD~%END%~48,2,4,07901372,00001,CA,TEST
DESCRIPTION,10061

The output of xp_cmdshell is:
The system cannot find the fil
NULL

If we change @sBartender to specify a trigger file name that is not
bracketed by <>, xp_cmdshell runs @sCommand without errors:

set @sBartender = '%BTW% /AF=\\apioptr1\c$\barcode\labels\004\RFID2x4.btw
/D=' + '\\apioptr1\c$\barcode\data\' + @sFormat + '\' + @sFileName + '.txt
/PRN="\\apioptr1\apio168" /R=3 /P /DD~%END%~'

The output of the revised @sCommand is:
\\apioptr1\c$\barcode\app\create_text_file
\\apioptr1\c$\barcode\data\004\testme.dat@%BTW%
/AF=\\apioptr1\c$\barcode\labels\004\RFID2x4.btw
/D=\\apioptr1\c$\barcode\data\004\testme.txt /PRN="\\apioptr1\apio168" /R=3
/P /DD~%END%~48,2,4,07901372,00001,CA,TEST DESCRIPTION,10061

Both commands when run from the cmd console function properly.  Why does
xp_cmdshell appear to have a problem with the <Trigger File Name> portion of
the string?

We're on SQL Server 2000 sp3 on Win2k Advanced Server sp4.

Thanks,
Shelley

Hello Shelley,

Whilst I don't want to comment on the calling of a VB program from a trigger
I will say it is very bad practice. A better approach is to insert into a
table and then have a secondary process monitor the table and then call the
external program.

As for you issue I guess the issue is that you are try to create a file called
"<Trigger File Name>". This is an invalid filename. Where as when hardcoded
it is a valid filename.

Are you expecting something to replace "<Trigger File Name>" with the name
of a file?

Have you tried wrapping <Trigger File Name> in double quotes. xp_cmdshell
'dir <fred>' doesn't work shilst xp_cmdshell 'dir "<fred>"' does


Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons


Show quoteHide quote

Simon,

I don't mind the comments, and I'm aware of best practices, but this thing
was created by a contractor and at this point, it can't be changed.  Just to
explain the scenario a bit, we have a commercial program called Bartender
that takes text files as input and produces tags, such as pallet tags and
RFID tags.  The custom VB program was written to take a set of parameters and
create a file in the appropriate structure for the Bartender program to
properly generate the tags.

No, the code is not creating a file by the name of <Trigger File Name>; this
is actually a parameter that is passed to the Bartender application. 
Bartender uses the parameter to determine a particular file to use in its
operations.  It used to be that we stated that file's name and location
explicitly, but the newer version of Bartender that we have wants the
<Trigger File Name> parameter and not the explicit name of the file.

To explain the construction of @sCommand, the
\\apioptr1\c$\barcode\app\create_text_file portion of the string is the call
to the VB application.  The \\apioptr1\c$\barcode\data\004\testme.dat is the
parameter for the file name, and the %BTW%
/AF=\\apioptr1\c$\barcode\labels\004\RFID2x4.btw /D=<Trigger File Name>
/PRN="\\apioptr1\apio168" /R=3 /P /DD~%END%~48,2,4,07901372,00001,CA,TEST
DESCRIPTION,10061 is the parameter for the contents of the file.  The VB
program recognizes @ as a separator between the file name and file contents.

As I stated in my post, when you run the @sCommand string from the command
console, it functions properly with either the <Trigger File Name> or
explicit name in the contents.  It is only when xp_cmdshell is used with the
former that there is an error.  That's why I believe that the problem is with
xp_cmdshell.  Yes, I tried wrapping <Trigger File Name> in double quotes. 
That did not change anything, and it would be incorrect in the file generated
to Bartender, as Bartender is expecting <Trigger File Name>, not "<Trigger
File Name."

Hope this helps clarify the situation.

Actually, I have to back up on what I said about the command running from the
command line.  My colleague said that it was working, but I decided to
double-check and it does not work from the console either, so yes, the issue
must be the < and > characters.  The problem now is that if I enclose them in
double quotes, while the file will get generated, Bartender will not read the
"<Trigger File Name>" portion of the file correctly as it is not expecting
double quotes.  Oh well, maybe this will push us to re-architect the process.
Thanks for your responses.

smaas@newsgroups.nospam (smaasnewsgroupsnospam@discussions.microsoft.com)
writes:
< and > are redirection operators in command-line mode. Try using
quotes:

   /D="<Trigger File Name>"

Update Statement
Date Range
UNDO
SQL query
Problem with repeated use of temp tables
Small problem with NOT EXISTS...
Linked Server Error: Login failed for user NT AUTHORITY\ANONYMOUS LOGON
How best to create a unique identifier across two or more tables...
Win Server 2003 SP2 - SQL2k5 Performance Improvements
Case-sensitivity and LIKE - not working in 2000?