Re: Using xp_cmdshell in a trigger

File output is probably best handled in a client application rather than in
a trigger.  I suggest you insert data into a staging table and process data
from there.  You can schedule an ActiveX scropt via SQL Server agent to
generate the output periodically.  Example below:

CREATE TABLE tblApplications
    (
    FirstName  varchar(75) NOT NULL,
    LastName  varchar(75) NOT NULL,
    Essay  varchar(75) NOT NULL,
    )
GO

CREATE TABLE PendingOutputs
    (
    SequenceNumber int IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_PendingOutputs PRIMARY KEY,
    FirstName varchar(75) NOT NULL,
    LastName varchar(75) NOT NULL,
    Essay varchar(75) NOT NULL,
    )
GO

CREATE TRIGGER [CreateFile] ON tblApplications
FOR INSERT
AS
SET NOCOUNT ON
INSERT INTO PendingOutputs
SELECT FirstName, LastName, Essay
FROM inserted
GO

'ActiveX script step (VBScript)
Set Connection = CreateObject("ADODB.Connection")
Set RecordSet = CreateObject("ADODB.Recordset")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set OutFile = FSO.OpenTextFile("C:\Temp\MyFile.txt", 8, True)
Recordset.CursorLocation = 3
Connection.Open _
    "Provider=SQLOLEDB;" & _
    "Data Source=MyServer;" & _
    "Integrated Security=SSPI;" & _
    "Initial Catalog=MyDatabase"
Set Recordset.ActiveConnection = Connection
RecordSet.Open _
    "SELECT " & _
    " SequenceNumber," & _
    " FirstName," & _
    " LastName," & _
    " Essay" & _
    " FROM PendingOutputs"
Do While RecordSet.EOF = False
    OutFile.WriteLine "FirstName: " & RecordSet.Fields(1)
    OutFile.WriteLine "LastName: " & RecordSet.Fields(2)
    OutFile.WriteLine "Essay: " & RecordSet.Fields(3)
    Connection.Execute "DELETE FROM PendingOutputs" & _
        " WHERE SequenceNumber = " & RecordSet.Fields(0)
    RecordSet.MoveNext
Loop
OutFile.Close
RecordSet.Close
Connection.Close
Set Connection = Nothing
Set RecordSet = Nothing
Set File = Nothing
Set FSO = Nothing

-- 
Hope this helps.

Dan Guzman
SQL Server MVP



"Terri" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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...
>
>