BCP (bulk copy program) exports empty file

Question

I have a bcp that runs as a stored procedure. It worked for several months and then it started writing a blank file. Since it was working my first thought as that there must have been a change in the environment.

Windows Server 2012 r
Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64)

declare @alenzi varchar(8000)
select @alenzi = 'bcp "select * from [MYReporting].[dbo].[Patron_Deactivate]" queryout \server01\Import\' + REPLACE(CONVERT(VARCHAR(12), GETDATE(),102),'.','') + 'PatronDeactivate.mp -c -t, -T -S' + @@servername

exec master..xp_cmdshell @alenzi

Answer 1

bcp streams out to a file exactly what the SQL statement returns.

You need to run that select * from [MYReporting].[dbo].[Patron_Deactivate] statement to see what is going on. Obviously, while doing that you need to use the same integrated account that is used for launching a bcp stored procedure.

Answer 2

Hi @ERPISE ,

1. What is the output of query 'select * from [MYReporting].[dbo].[Patron_Deactivate]'?Is the output correct or as expected? Have you checked to ensure that none of your fields contain a NULL. If yes, you could try to use ISNULL() to replace NULL with ''.
2. You could also use the -w option to tell bcp to use unicode characters for all output.
3. Is the export pathor directory correct?Has this path or folder been changed or moved?
4. Run @@servername and check whether the server is changed or not.
5. Check the account and whether it has full control permissions to the directory.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.