BCP Utility Not returning/Hangs

Question

I am creating a very simple stored procedure to export the output of a query to a text file using BCP as shown below.

When I execute the SP from SQL Management Sudio, the results pane just sits there with "Executing Query..." It doesn't return an error nor come back.

Any help is appreciated..I have spend a lot of time on this already.

CREATE procedure spTestShell
WITH EXECUTE AS 'CmdShell'
AS
BEGIN

    DECLARE @Command varchar(512)

    set @Command = 'bcp "SELECT * FROM DBName.dbo.[ImportFileTable]" queryout "C:\bcptest.txt" -T -c -S' + @@SERVERNAME

    print @Command  
    EXEC xp_cmdshell @Command

END

exec spTestShell 

Answer 1

The fact that it 'hangs', strongly suggests to me that the process is trying to, but cannot connect to your server.

I think your problem is that there is no space between the -S parameter and the server name. So it won't be able to connect to your server as the name is invalid. Try adding a space to see if that fixes it, i.e.

set @Command = 'bcp "SELECT * FROM DBName.dbo.[ImportFileTable]" queryout "C:\bcptest.txt" -T -c -S ' + @@SERVERNAME

If that doesn't work, perhaps put [] brackets round the server name, as the name itself might be causing problems.