BCP 命令不写入文件

Question

我使用以下作为导出查询，我不得不转移到新升级的Windows Server 2003-2008R2。 我还从SQL 2005升级到SQL 2012，显然搞砸了一些权限。 以下代码应该写入一个由 dbmail 拾取并通过电子邮件发送的文件。 问题是它没有写入文件，我相信权限是问题所在，但不确定我哪里出错了。 我知道这是有效的，因为它在另一台服务器上工作了多年而没有遇到任何问题，所以问题必须是我猜的权限。

我收到的消息如下，因为从未创建过该文件：

以用户身份执行：NT SERVICE\SQLSERVERAGENT。附件文件 D：\Argosy_import_090513_0001.txt 无效。[SQLSTATE 42000]（错误 22051）。 步骤失败。

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'[e-mail]',
@body='Message Body', 
 @subject ='Import file from My Company',
@profile_name ='Mail',
@file_attachments = @FileName;

Answer 1

附件大小是否已更改为默认值？

查看服务器上是否存在附件！

下面的代码显示了当前附件大小，然后为 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';

此外，请确保邮件中允许使用附件扩展名。

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

Answer 2

观察到 BCP 命令的奇怪行为：

我。如果从命令行管理程序执行 BCP 命令，则生成的文件位于本地计算机上

ii. 否则，如果在 SQL Server Management Studio 上执行 BCP 命令，则生成的文件位于命令中提到的服务器名称上。

因此，请确保在 BCP 命令中提供正确的服务器名称（-S 参数）。