XP_cmdshell not working

[gauravgmbhr]

Hi,

I require a trigger that dumps the updated row into a txt file.
I am trying to achieve this using XP_CMDSHELL and bcp

ALTER TRIGGER Dump_text
   ON  dbo.XXXX
   AFTER INSERT,UPDATE
AS 
declare @bcp_command varchar(300)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
       SELECT @bcp_command = 'bcp "SELECT * FROM DSeries..ESP_XXXX WHERE identifier='''+identifier+'''" queryout c:\gaurav.txt -c -t -UAAA -P BBBB"' FROM inserted
    --SELECT @bcp_command = 'MD TEST'
    EXEC master..xp_cmdshell @bcp_command
 
END
GO
 

The @bcp_command works fine if i run it thru DOS. But whenever triggers submits the BCP operation time outs. Also i have tried submitting different command , they all work fine accept the one i need.

Can some one please tell me why its timing out.

[ck9663]

Hi,

I require a trigger that dumps the updated row into a txt file.
I am trying to achieve this using XP_CMDSHELL and bcp

ALTER TRIGGER Dump_text
   ON  dbo.XXXX
   AFTER INSERT,UPDATE
AS 
declare @bcp_command varchar(300)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
       SELECT @bcp_command = 'bcp "SELECT * FROM DSeries..ESP_XXXX WHERE identifier='''+identifier+'''" queryout c:\gaurav.txt -c -t -UAAA -P BBBB"' FROM inserted
    --SELECT @bcp_command = 'MD TEST'
    EXEC master..xp_cmdshell @bcp_command
 
END
GO
 

The @bcp_command works fine if i run it thru DOS. But whenever triggers submits the BCP operation time outs. Also i have tried submitting different command , they all work fine accept the one i need.

Can some one please tell me why its timing out.

Is time out your only problem? I mean if you do an xp_cmdshell in query analyzer, is it working? The reason am asking is because this system stored proc is not enable by default.

Assuming you have xp_cmdshell configured properly and it's working on other part of your SP.

I would assume (read: not sure) you're putting too much stress on your server if you put the xp_cmdshell - bcp inside a trigger. This would mean every time there's an INSERT or UPDATE on your table, this will fire and we're talking per row. That could be the reason for the time out. I would recommend you dump all inserted rows into a table and after the INSERT or UPDATE command, do the bcp.

-- CK

-- CK

[gauravgmbhr]

Is time out your only problem? I mean if you do an xp_cmdshell in query analyzer, is it working? The reason am asking is because this system stored proc is not enable by default.

Assuming you have xp_cmdshell configured properly and it's working on other part of your SP.

I would assume (read: not sure) you're putting too much stress on your server if you put the xp_cmdshell - bcp inside a trigger. This would mean every time there's an INSERT or UPDATE on your table, this will fire and we're talking per row. That could be the reason for the time out. I would recommend you dump all inserted rows into a table and after the INSERT or UPDATE command, do the bcp.

-- CK

-- CK

Well XP_cmdshell is working with other commands, like CD MD ,RD, DIR, but not with BCP, the row inserts or UPDATES are very few, hardly 2 or 3 a day.