October 5, 2010 at 12:24 pm
I have a stored procedure which is executed from a trigger to process data and output it to a file using BCP. The stored procedure accepts 1 parameter, runs queries to generate data to a temp table, and then executes a BCP statement to output the contents of the temp table.
Executing the stored procedure manually generates the desired file. When the stored procedure is called from the trigger , based on an application event, the stored procedure hangs at the BCP command. The BCP process is shown in Task Manager, but will not complete and generate the output file.
ALTER PROCEDURE [dbo].[USER_SP_EDI940_GENERATE_XFER]
@EVENT_NO VARCHAR(15)
AS
BEGIN --> 1
SET NOCOUNT ON;
SET FMTONLY OFF
IF(OBJECT_ID('tempdb..##EDI940') IS NULL)
CREATE TABLE ##EDI940 (ID UNIQUEIDENTIFIER, SEQ_NO INT, FILE_LINE VARCHAR(200))
SELECT @EDI940ID = NEWID()
-- SCRIPTS TO POPULATE ##EDI940 TABLE
/*******************************************/
/**** PROCESS OUTPUT TO FILE ****/
/*******************************************/
DECLARE @OUTPUT_PATH VARCHAR(100), @FILE_NAME VARCHAR(100), @SQL VARCHAR(1000)
SELECT @OUTPUT_PATH = C.EDI_OUTPUT_PATH
, @FILE_NAME = @XFER_NO + '.edi'
, @SQL = 'BCP "SELECT FILE_LINE FROM tempdb..##EDI940 WHERE ID = ''' + CAST(@EDI940ID AS VARCHAR(80)) + ''' ORDER BY ID, SEQ_NO" QUERYOUT C:\Temp\BCPOut.txt -c -T -e C:\Temp\err.out'
FROM USER_EDI_CTL C
WHERE C.KEY_ID = 1
PRINT @SQL
EXEC master..xp_cmdshell @SQL
DELETE FROM ##EDI940 WHERE ID = @EDI940ID
-- DELETE THE TEMP TABLE IF IT IS EMPTY
DECLARE @REC_CNT INT
SELECT @REC_CNT = COUNT(*) FROM ##EDI940
IF (@REC_CNT = 0)
DROP TABLE ##EDI940
END --< 1
Execute stored procedure manually:
EXEC USER_SP_EDI940_GENERATE_XFER '100015'
Generates this BCP command:
BCP "SELECT FILE_LINE FROM tempdb..##EDI940 WHERE ID = 'E75D92ED-9ADB-4950-8FE1-0AC2FA4A9700' ORDER BY ID, SEQ_NO" QUERYOUT C:\Temp\BCPOut.txt -c -T -e C:\Temp\err.out
When executed manually, the stored procedure completes and generates the desired output file.
Trigger which calls the stored procedure based on an application event:
ALTER TRIGGER [dbo].[USER_TR_SY_EVENT]
ON [dbo].[SY_EVENT]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EVENT_NO T_DOC_NO, @PKG_ID T_PKG_ID, @EVENT_TYP T_TRX_TYP, @SYS_ERR T_BOOL
SELECT @EVENT_NO = I.EVENT_NO, @PKG_ID = I.PKG_ID, @EVENT_TYP = I.EVENT_TYP, @SYS_ERR = I.SYS_ERR
FROM INSERTED I
-- CHECK IF THIS IS A TRANSFER-OUT POSTING EVENT THAT HAS COMPLETED SUCCESSFULLY (SYS_ERR = 'N')
IF(@PKG_ID = 'IM' AND @EVENT_TYP = 'XFO' AND @SYS_ERR = 'N')
EXEC USER_SP_EDI940_GENERATE_XFER @EVENT_NO
END
When the application event occurs, the trigger is fired and the stored procedure is called, but processing halts when the BCP command is executed. "BCP.exe" is visible in Task Manager processes, but does not complete. This causes the stored procedure to get stuck and the application to hang.
The permissions on the C:\Temp folder are to allow Full Control to "Everyone".
Question:
Why would BCP not complete execution when called from a sproc fired by a trigger, but will complete successfully when the sproc is executed manually?
October 15, 2014 at 8:40 am
I habe the same problem with this bcp hang up.
My Stored Procedure contains a bcp command, that create a file with data. I can execute this Stored Procedure without problems manually in SQL Server Management Studio Query Analyzer.
But from a insert-Trigger, that contains this Stored Procedure, the bcp.exe hang up, shown in Task Manager.
Why is it so? What is the reason the bcp.exe hang up?
Many Thanks
Thom
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply