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
1 Access is denied. 2 NULL
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.
I changed the SQL service account to another user account "SQLService" instead of the default NT/MSSQLServer account and it worked