这是我的整个日常：
Declare @AttFileType as char(5), @HQCo as int, @FormName as Varchar(15), @KeyID as VarChar(10), @UniqueID as uniqueidentifier, @FilePath as Varchar(100), @StringCommand as Varchar(200)
Declare @AttID as int
DECLARE @cmd as VARCHAR(500)
DECLARE @cmd2 as VARCHAR(500)
CREATE TABLE #tmp(eFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\Users\*****\Desktop\Test_Images';
Declare @FileName varchar(100)
Set @UniqueID = NewID()
While (Select Count(*) From #tmp where eFileName is not null) > 0
Begin
Select Top 1 @FileName = eFileName From #tmp
Set @FilePath = 'C:\Users\*****\Desktop\Test_Images\' + @FileName
Set @AttID = (Select TOP 1 AttachmentID FROM dbo.bHQAF ORDER BY AttachmentID DESC) + 1
Set @AttFileType = '.jpg'
Insert Into dbo.bHQAF (AttachmentID, AttachmentFileType)
Select @AttID, @AttFileType
SET @cmd = '
Declare @AttID2 as int, @AttFileType2 as char(5), @FilePath2 as Varchar(100)
Set @AttFileType2 = ''.jpg''
Set @AttID2 = (Select TOP 1 AttachmentID FROM dbo.bHQAF ORDER BY AttachmentID DESC)
Update dbo.bHQAF
Set AttachmentData = (SELECT * From OPENROWSET (Bulk ''' + @FilePath + ''', Single_Blob) rs)
Where AttachmentID = @AttID2 and AttachmentFileType = @AttFileType2'
Exec (@cmd)
Set @HQCo = 101
Set @FormName = 'HRCompAssets'
Set @KeyID = 'KeyID=2'
Insert Into dbo.bHQAT (HQCo, AttachmentID, FormName, KeyField, UniqueAttchID)
Select @HQCo, @AttID, @FormName, @KeyID, @UniqueID
Insert Into dbo.bHQAI (AttachmentID, HRCo)
Select @AttID, @HQCo
Update dbo.bHQAT
Set Description = 'TEST3', AddDate = GETDATE(), AddedBy = '****', DocAttchYN = 'N', DocName = 'Database', OrigFileName = @FileName, TableName = 'HRCA'
Where AttachmentID = @AttID and HQCo = @HQCo
Insert Into dbo.bHQAI (AttachmentID, HRCo)
Select @AttID, 101
Update dbo.bHRCA
Set UniqueAttchID = @UniqueID
Where HRCo = 101 and Asset = '00001'
Delete from #tmp Where eFileName = @FileName
End
我已经验证代码可以正常工作，可以将单个图像加载到服务器中，但这里没有这一点：
-- Declarations here
CREATE TABLE #tmp(eFileName VARCHAR(100));
INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\Users\*****\Desktop\Test_Images';
While (Select Count(*) From #tmp where eFileName is not null) > 0
Begin
Select Top 1 @FileName = eFileName From #tmp
-- Rest of code here
Delete from #tmp Where eFileName = @FileName
End
但是，一旦添加 while 循环和 xp_cmdshell 语句，文件名将返回为“访问被拒绝”。
任何帮助，将不胜感激！
我不是 SQL 专家，但有人要求我将大约 1000 个 PDF 和 JPEG 文件加载到数据库中，而脚本似乎是最合乎逻辑的方法。
当一切都说完并完成后，我希望脚本从文件夹中获取每个图像并将其加载到数据库中。
如有必要，我愿意使用不同的循环方法。
编辑：
我还尝试将以下内容添加到代码的开头，但没有解决问题：
--Allow for SQL to use cmd shell
EXEC sp_configure 'show advanced options', 1 -- To allow advanced options to be changed.
RECONFIGURE -- To update the currently configured value for advanced options.
EXEC sp_configure 'xp_cmdshell', 1 -- To enable the feature.
RECONFIGURE -- To update the currently configured value for this feature.
我还进入 Facets > Surface Area Configuration 并确保 xp_cmdshell 已启用/允许（true）。它还已在 Facets > Server Security 下标记为 true。
