In Lucee 5 I am unable to run a Bulk insert (from a CSV file) no matter what I try. Clearly I am overlooking something simple. I have my basic query as follows
qImpObj = new query( name="qImport", sql = "BULK INSERT my_sql_server_table_name FROM 'd:\csv\csv_import.csv' WITH ( FIRSTROW = 2, FIELDTERMINATOR ='|', ROWTERMINATOR ='\n' ) " );
I get various version of the following file/permissions error whether I try to load the file after directly uploading to the SQL Server (so it has local direct access) or if I put it on a share (from the Lucee server). I even tried creating a Stored Procedure and running from Lucee and still same error. It is worth noting the stored procedure and all queries I have tried run without issue when ran directly on SQL Server, its just when Lucee calls them I get the permissions errors.
Operating system error code 3(The system cannot find the path specified.)
Cannot bulk load because the file ""d:\csv\csv_import.csv"" could not be opened. Operating system error code 5(Access is denied.).;lucee.runtime.exp.DatabaseException:
Any guidance others have used in such situations would be greatly appreciated.