By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,358 Members | 3,874 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,358 IT Pros & Developers. It's quick & easy.

XP_cmdshell not working

gauravgmbhr
100+
P: 107
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

Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER Dump_text
  2.    ON  dbo.XXXX
  3.    AFTER INSERT,UPDATE
  4. AS 
  5. declare @bcp_command varchar(300)
  6. BEGIN
  7.     -- SET NOCOUNT ON added to prevent extra result sets from
  8.     SET NOCOUNT ON;
  9.        SELECT @bcp_command = 'bcp "SELECT * FROM DSeries..ESP_XXXX WHERE identifier='''+identifier+'''" queryout c:\gaurav.txt -c -t -UAAA -P BBBB"' FROM inserted
  10.     --SELECT @bcp_command = 'MD TEST'
  11.     EXEC master..xp_cmdshell @bcp_command
  12.  
  13. END
  14. GO
  15.  
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.
Mar 21 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
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

Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER Dump_text
  2.    ON  dbo.XXXX
  3.    AFTER INSERT,UPDATE
  4. AS 
  5. declare @bcp_command varchar(300)
  6. BEGIN
  7.     -- SET NOCOUNT ON added to prevent extra result sets from
  8.     SET NOCOUNT ON;
  9.        SELECT @bcp_command = 'bcp "SELECT * FROM DSeries..ESP_XXXX WHERE identifier='''+identifier+'''" queryout c:\gaurav.txt -c -t -UAAA -P BBBB"' FROM inserted
  10.     --SELECT @bcp_command = 'MD TEST'
  11.     EXEC master..xp_cmdshell @bcp_command
  12.  
  13. END
  14. GO
  15.  
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
Mar 21 '08 #2

gauravgmbhr
100+
P: 107
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.
Mar 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.