SET @CMD='echo "'+@data+'">'@PATH + @FILENAME;
EXEC xp_cmdshell @CMD,NO_OUTPUT;

USE [PV_TEST2]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER items_insert on Results
AFTER INSERT 
AS
BEGIN
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF 

DECLARE @OUTPUT AS VARCHAR(8000);
DECLARE @OUTPUT2 AS VARCHAR(8000);

DECLARE @CMD as VARCHAR(8000);
DECLARE @SEPARATOR as VARCHAR(5);
DECLARE @SERVERPATH as varchar(100);
DECLARE @BATCHFILE as varchar(100);

-- Variable for folder name
DECLARE @TodayDate as varchar(40);
DECLARE @TodayHour as varchar(40);
DECLARE @TodayMinu  as varchar(40);
DECLARE @TodaySec  as varchar(40);
DECLARE @TodayMSec  as varchar(40);
DECLARE @NewFileName as varchar(100);
DECLARE @GenericFileName as varchar(100);

--  INIT Variable

SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodaySec = DATEPART(ss,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())
SELECT @TodayMSec = DATEPART(ms,GETDATE())
SELECT @NewFileName =@TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '_'+ @TodaySec + '_' + @TodayMSec

-- Other variable, for file creation

SELECT @SEPARATOR='|';
SELECT @SERVERPATH='e:\PV\';
SELECT @BATCHFILE='uploadPV.bat';
SELECT @GENERICFILENAME='output.dat'

SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF

-- @OUTPUT should receive the concatenation of the data separated with a pipe

SELECT @OUTPUT = (SELECT left(SerialNumber,30) +@SEPARATOR+ 
		ModuleName +@SEPARATOR+ 
		convert(varchar,Reduction,120)
		FROM inserted)

-- This insert is for debug ; when the trigger is launch by hand with INSERT Statement, the output is good, as expected
-- When the trigger is fired from the application using DSN, this output is NULL

INSERT DebugTrace SELECT 'PV', USER_NAME(), getdate(), 'OUTPUT '+@OUTPUT


SET @CMD='mkdir ' +@SERVERPATH+@NewFileName

-- In all case, the folder is properly created

EXEC xp_cmdshell @CMD,NO_OUTPUT;

-- Debug insert ; always with expected values

INSERT DebugTrace SELECT 'PV', USER_NAME(), getdate(), 'MKDIR '+@CMD

SET @CMD='echo "'+@OUTPUT+'">'+@SERVERPATH+@NewFileName+'\'+@GENERICFILENAME


-- Here, again, manual insert : OK, "DSN insert" : NOK, @CMD is NULL
INSERT DebugTrace SELECT 'PV', USER_NAME(), getdate(), 'ECHO '+@CMD

EXEC xp_cmdshell @CMD,NO_OUTPUT;

-- batch call this parameters

SELECT @BATCHFILE=@SERVERPATH+@BATCHFILE+' '+@SERVERPATH+' '+@NewFileName +' '+ @GENERICFILENAME

-- batch always run

EXEC xp_cmdshell @BATCHFILE;

INSERT DebugTrace SELECT 'PV', USER_NAME(), getdate(), 'BATCHFILE '+@BATCHFILE

END

INSERT DebugTrace SELECT 'PV', USER_NAME(), getdate(), 'OUTPUT '+@OUTPUT

quote:

---

Originally posted by elifrance

... It doesn t matter if field are NULL or not.

---