BCP command not writing file

Question

I use the following as an export query and I had to transfer to a new upgraded windows server 2003-2008R2. I also upgraded from SQL 2005 to SQL 2012 and apparently screwed up some permission. The following code is supposed to write to a file that gets picked up by dbmail and emailed. The problem is its not writing the file and I believe permissions to be the problem but am not sure where I went wrong. I known th this works as it worked for years on the other server without a hitch so the problem has to be permissions I am guessing.

The message I get is as follows and occurs because the file is never created:

Executed as user: NT SERVICE\SQLSERVERAGENT. Attachment file D:\Argosy_import_090513_0001.txt is invalid. [SQLSTATE 42000] (Error 22051). The step failed.

Declare @PeriodStart Datetime
Declare @PeriodEnd Datetime
SELECT @PeriodEnd = GetDate()
,@PeriodStart = dateadd(hour,-72,getdate())

;WITH outpQ
AS
(
SELECT 1 AS grpOrd, CAST(null AS VARCHAR(255)) AS posInGrp, 'A' AS Ord, CaseNumberKey, 'A|' + ClientsKey + '|' + CAST(BRTNumber AS VARCHAR(11)) + '|' + IsNull(replace(convert(char(10), CoverDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(char(10), CoverDate, 101), '/', ''), '') + '|' + IsNull(ParcelNumber, '') + '|' + IsNull(AssessedBeg, '') + '|' + IsNull(AssessedDim, '') + '|' + IsNull(AbbrLegal, '') + '|' + IsNull(WaterFrom, '') + '|' + IsNull(WaterTo, '') + '|' + IsNull(Cast(WaterOpen AS VARCHAR(50)), '') + '|' + IsNull(TaxFrom, '') + '|' + IsNull(TaxTo, '') + '|' + IsNull(Cast(TaxOpen AS VARCHAR(50)), '')  AS Extract
FROM   newCityCollection.dbo.PropertyInformation
WHERE  DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2 
UNION ALL 
SELECT 1 As grpOrd, null AS posInGrp, 'A1', A.CaseNumberKey, 'A1|' + '|' + '|' + B.LienNumber + '|' +  IsNull(Cast(B.LienAmt AS VARCHAR(50)), '') + '|' + IsNull(replace(LienDate, '/', ''), '') + '|' + IsNull(B.LienReason, '') AS Extract
FROM   newCityCollection.dbo.PropertyInformation A
JOIN   newCityCollection.dbo.muniLiens B ON B.CaseNumberKey = A.CaseNumberKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2 
UNION ALL 
SELECT 2 AS grpOrd, CAST(C.InterestsKey AS VARCHAR(11)) AS posInGrp, 'B', A.CaseNumberKey, 'B|' + '|' + IsNull(C.First, '') + '|' +  IsNull(C.Middle, '') + '|' + IsNull(C.Last, '') + '|' + IsNull(C.Alias, '') + '|' + IsNull(C.ComName, '') + '|' + IsNull(C.DocRel, '') + '|' + Cast(C.InterestsKey AS VARCHAR(11)) AS Extract
FROM   newCityCollection.dbo.PropertyInformation A
JOIN   newCityCollection.dbo.Interests C ON C.CaseNumberKey = A.CaseNumberKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2  
UNION ALL  
SELECT 2 AS grpOrd, CAST(C.InterestsKey AS VARCHAR(11)) AS posInGrp, 'B1', A.CaseNumberKey, 'B1|' + IsNull(FullAdd, '') + '|' + IsNull(D.City, '') + '|' + IsNull(D.State, '') + '|' + IsNull(D.Zip, '') AS Extract
FROM   newCityCollection.dbo.PropertyInformation A
JOIN   newCityCollection.dbo.Interests C ON C.CaseNumberKey =  A.CaseNumberKey 
JOIN   newCityCollection.dbo.InterestAdd D ON D.CaseNumberKey = A.CaseNumberKey AND D.InterestsKey = C.InterestsKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2 
UNION ALL 
SELECT 2 AS grpOrd, CAST(C.InterestsKey AS VARCHAR(11)) AS posInGrp, 'B2', A.CaseNumberKey, 'B2|' + '|' + IsNull(E.SuitNumber, '') + '|' +  Cast(E.BDate AS VARCHAR(11)) + '|' + IsNull(E.Chapter, '') + '|' + IsNull(E.VS, '') AS Extract
FROM   newCityCollection.dbo.PropertyInformation A
JOIN   newCityCollection.dbo.Interests C ON C.CaseNumberKey = A.CaseNumberKey
JOIN   newCityCollection.dbo.Banks E ON E.CaseNumberKey = A.CaseNumberKey  AND E.InterestsKey = C.InterestsKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2 
UNION ALL
SELECT 3 As grpOrd3, null AS posInGrp, 'B3', A.CaseNumberKey, 'B3|' + '|' + F.DocType + '|' + IsNull(Cast(F.DocAmt AS VARCHAR(50)), '') + '|' + IsNull(replace(convert(char(10), DocDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(char(10), RecDate, 101), '/', ''), '') + '|' + IsNull(F.DocID, '') + '|' + IsNull(F.Grantee,'') + '|' + IsNull(F.Grantor,'') + Cast(F.DocIDKey AS VARCHAR(11)) AS Extract
FROM   newCityCollection.dbo.PropertyInformation A                                                                                                                                                                                                                                                                                                                            
JOIN   newCityCollection.dbo.Documents F ON F.CaseNumberKey = A.CaseNumberKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2                                          
UNION ALL
SELECT 4 AS grpOrd, null AS posInGrp, 'C', A.CaseNumberKey, 'C|' + IsNull(J.CType, '') + '|' + IsNull(J.plaintiffName,'') + '|' + IsNull(J.plaintiffAdd1, '') + '|' + IsNull(J.plaintiffCity, '') + '|' + IsNull(J.plaintiffState, '') + '|' + IsNull(J.plaintiffZip, '') + '|' + '|' + IsNull(J.defendantName, '') + '|' + IsNull(J.defendantAdd1, '') + '|' + IsNull(J.defCity, '') + '|' + IsNull(J.defState, '') + '|' + IsNull(J.defZip, '') + '|' +  '|' + IsNull(J.Court, '') + '|' + IsNull(J.CaseID, '') + '|' + IsNull(J.JAmt, '') + '|' + IsNull(replace(convert(VarChar(10), JDate, 101), '/', ''), '') + '|' + IsNull(replace(convert(VARCHAR(10), revivedDate, 101), '/', ''), '') AS Extract
FROM   newCityCollection.dbo.PropertyInformation A 
JOIN   Acme.new_judgment_system.dbo.selected_compiled_clean J ON J.CaseNumber = A.CaseNumberKey
WHERE  A.DateFinished BETWEEN @PeriodStart AND @PeriodEnd AND ClientKey = 2 AND J.plaintiffName NOT IN (SELECT Plaintiff FROM newCityCollection.dbo.excluded_Plaintiffs)
)

--Extract data set into a table -- dump table in .txt with current date as part of name then delete that table

SELECT Extract INTO datadump FROM outpQ ORDER BY CaseNumberKey, grpOrd, posInGrp, Ord
DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

SET @FileName = REPLACE('D:\Argosy_import_'+CONVERT(char(8),GETDATE(),1)+'_0001.txt','/','')

SET @bcpCommand = 'bcp "SELECT Extract FROM datadump" QUERYOUT "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P ********** -T -c'

EXEC master..xp_cmdshell @bcpCommand

DROP table datadump

EXEC msdb.dbo.sp_send_dbmail
 @recipients=N'[email protected]',
@body='Message Body', 
 @subject ='Import file from My Company',
@profile_name ='Mail',
@file_attachments = @FileName;

Answer 1

Did the attachment size get changed to the default?

See if the attachment exists on the server!

Code below shows current attachment size and then sets max size for DBMail.

-- show current size
exec msdb.dbo.sysmail_help_configure_sp 'MaxFileSize'

-- set the max attachment size to 20000 bytes
exec msdb.dbo.sysmail_configure_sp 'MaxFileSize', '20000';

Also, make sure that the attachment extension is allowed in mail.

-- show prohibited extensions
EXEC msdb.dbo.sysmail_help_configure_sp 'ProhibitedExtensions'

Answer 2

Observed a strange behavior with BCP command:

i. If BCP command is executed from command shell, then the file generated is on the local machine

ii. Else if BCP command is executed on SQL Server Management Studio, the file generated is on the server name mentioned with the command.

Hence, make sure you are providing the proper server name (-S parameter) in BCP command.