Re: Using xp_cmdshell in a trigger

Thanks Dan,
I won't have multi-row inserts so I don't believe I'll need the cursor so
this will work..
SELECT @strcmdshell  =   'echo FirstName: ' +  @FirstName + '>>' + @filename
EXEC master..xp_cmdshell @strcmdshell
SELECT @strcmdshell =
'echo LastName: ' + @LastName + '>>' + @filename
EXEC master..xp_cmdshell @strcmdshell

My next problem is an additional variable I have @Essay which is retrieved
from an html form which will always have carriage returns. I've tried
replacing the carriage returns with the newline character but it seems those
can not be embedded in a command line either.

Any thoughts? Is there some other method  which would allow me to write a
file from a trigger where I won't run into these formatting issues.

Thanks...


"Dan Guzman" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> You can't embed a carriage return on a command line so you'll need one
ECHO
> command per line.  Also, you need to code your trigger to handle a
multi-row
> insert.  For example:
>
> CREATE TABLE tblApplications
>     (
>      FirstName  varchar(75) NOT NULL,
>     LastName  varchar(75) NOT NULL
>     )
> GO
>
> CREATE TRIGGER [CreateFile] ON tblApplications
> FOR INSERT
> AS
> SET NOCOUNT ON
> DECLARE @FirstName varchar(75)
> DECLARE @LastName varchar(75)
> DECLARE @strcmdshell varchar(150)
> DECLARE @filename varchar(100)
> DECLARE @filecontents varchar(150)
> SELECT @filename = 'c:\test.txt'
> DECLARE Inserted CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT FirstName, LastName FROM inserted
> OPEN Inserted
> WHILE 1 = 1
> BEGIN
>     FETCH NEXT FROM Inserted
>         INTO @FirstName, @LastName
>     IF @@FETCH_STATUS = -1 BREAK
>     SELECT @strcmdshell  =
>         'echo FirstName: ' +  @FirstName + '>>' + @filename
>     EXEC master..xp_cmdshell @strcmdshell
>     SELECT @strcmdshell =
>         'echo LastName: ' + @LastName + '>>' + @filename
>     EXEC master..xp_cmdshell @strcmdshell
> END
> CLOSE Inserted
> GO
>
> INSERT INTO tblApplications
> SELECT 'First1', 'Last1'
> UNION
> SELECT 'First2', 'Last2'
> GO
>
> Another consideration is concurrent inserts; only one process can write to
a
> file at a time.  I don't know your business rules so I didn't include an
> example here.
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Terri" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > I'm using xp_cmdshell to output a text file from a trigger like this
> >
> > CREATE TRIGGER [CreateFile] ON tblApplications
> > FOR INSERT
> > AS
> >
> > DECLARE @FirstName varchar(75)
> > DECLARE @LastName varchar(75)
> >
> > Declare @strcmdshell varchar(150)
> > Declare @filename varchar(100)
> > Declare @filecontents varchar(150)
> >
> > SELECT @FirstName = FirstName FROM INSERTED
> > SELECT @LastName = LastName FROM INSERTED
> > SELECT @filecontents  = 'FirstName: ' +  @FirstName+char(13)+'LastName:
'
> +
> > @LastName+char(13)+
> > SELECT @filename = 'c:\test.txt'
> > SELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filename
> > exec master..xp_cmdshell @strcmdshell
> >
> > When I concatenate @FirstName and @LastName(neither is NULL) and try to
> > insert a blank line with char(13) the command doesn't run. If I don't
use
> > char(13) it works fine. Does this make sense? IS there any other way I
can
> > format the output file.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>