Stored procedure with bcp hangs, but works when run as a script

Question

I've been working on exporting a table to a file, and had problems with the bcp (bulk copy program) part of the procedure locking up. The code worked fine when I ran it as a script, but would generate locked processes when I wrapped it in a stored procedure.

John Hunter · Accepted Answer · 2020-11-10 03:23:45Z

0

I seem to have found the solution; COMMIT. Namely, I had to wrap the code which truncated and inserted into the table which bcp would be picking up the data from within a BEGIN TRANSACTION...COMMIT. Now the procedure works

I think it is to do with the command exec master.dbo.xp_cmdshell @bcp going outside of the SQL session to the OS. Am I correct, or is there a better explanation?

answered Nov 10, 2020 at 3:23

John Hunter

191 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure with bcp hangs, but works when run as a script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related