Bulk insert multiple files inside a folderAsk Question

问题:

To the wonderful people at Stack who have helped me time and time again... I am trying to run a bulk insert routine for all the files in a folder. This is my approach, but I seem to have hit a roadblock with a message saying "Access is denied in my select statement"

EXEC [dbo].[procReadFolder] 'C:\Users\ABC\Downloads\NYSE_2015'

I have admin access to all the folders and files so unsure of the next step.

See Logic below:

ALTER procedure [dbo].[procReadFolder] @path sysname
as
begin
set nocount on

declare @dirContent table(
id int identity(1,1),
FileName sysname NULL
)
declare  @cmd nvarchar(512)
set @cmd = 'DIR /b ' + @path

insert into @dirContent
exec master..xp_cmdshell @cmd

select * from @dirContent

-- Code to Loop through all the records in the file
-- To be written

-- Routine that takes the file name as a parameter and inserts the file
EXEC [dbo].[BulkInsert] 'Path'

end

Result Set:

1   Access is denied.
2   NULL

回答1:


You will need to ensure that the account that the SQL Server Service is running as has access to the specific path.

The stored procedure is executed under the security context of the account configured to run the SQL Server Service, and therefore that is the account that will need to be given permissions to the folder on the drive.

回答2:


I changed the SQL service account to another user account "SQLService" instead of the default NT/MSSQLServer account and it worked

标签: sql-server tsql
© 2014 TuiCode, Inc.