CODEWITHSUNDEEP
sql-serverlinuxbulkinsert
DataDoer
DataDoer

Reputation: 209

SQL Server BULK INSERT - Linux/RHEL error

I'm running below bulk insert query (via a stored procedure) against SQL Server 2019 which resides in a Linux / RHEL VM. Upon execution, I get this error:

Msg 4860, Level 16, State 1, Procedure dbo.Import_Store, Line 25 [Batch Start Line 14]

Cannot bulk load. The file "/home/user1/FileStorage/Store1.csv" does not exist or you don't have file access rights.

This error message is not correct - i.e. the file exists physically on the VM at the specified path and with correct permissions (everybody got full permission!).

Any insight is highly appreciated. Thanks.

BULK INSERT dbo.Store
    FROM '/home/user1/FileStorage/Store1.csv'
    WITH
    (
        FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n'
    );

FYI - I tried many options like use C:\ or C:/ instead of /home, but nothing worked so far.

Upvotes: 0

Views: 400

Answers (2)

Tal Lam
Tal Lam

Reputation: 1

The file must be located in a directory that SQL Server can access. On Linux, this often means ensuring the file is placed in a directory like /var/opt/mssql/data or ensuring proper permissions are set.

Upvotes: 0

DataDoer
DataDoer

Reputation: 209

Thanks Larnu for your comment above. That made me do something that resolved my problem. Here is how I solved it for anybody who is in the same boat.

I basically created a brand new shared folder at root on Linux VM. Then created a new user group and gave it full access to the shared folder. Then I added the user "mssql" to the group. Now my SQL Server can access the location without any errors!

sudo mkdir -p /SharedFolder

sudo groupadd SharedUsers

sudo chgrp -R SharedUsers /SharedFolder
sudo chmod -R 2775 /SharedFolder

sudo usermod -a -G SharedUsers mssql

Upvotes: 0

Related Questions