How do I give permissions to run a BULK INSERT?

Question

I have a SQL Server 2008 running on a remote machine. Let us say that the machine is called XYZ. The following works:

1. Remote desktop into XYZ
2. Open SSMS and then connect to SQL Server running on XYZ

3. Run the following:

   USE SampleDB
   GO
   
   CREATE TABLE [dbo].[SampleData](
       [ColA] [varchar](50) NULL,
       [ColB] [varchar](500) NULL
   ) ON [PRIMARY]
   
   GO
   
   BULK INSERT [dbo].[SampleData]
   FROM "H:\Scratch\OUTPUT_Sample"
   WITH
   (
       FIELDTERMINATOR = '$',
       ROWTERMINATOR = '\n',
       FIRSTROW = 2
   )
   GO
   

This runs perfectly fine. Now, I connect to another SQL Server ABC by doing the following:

1. Remote desktop into XYZ
2. Open SSMS and then connect to SQL Server running on ABC
3. Run the above script

I get the following error:

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "H:\Scratch\OUTPUT_Scratch" could not be opened. Operating system error code 3(The system cannot find the path specified.).

Can someone tell me how to fix this problem? I guess I need to give some permissions to some account but am not sure how to find this out nor what permissions to give that account. I get the same error when I use sqlcmd.exe as well.

Any suggestions?

Answer 1

Looks like I found my answer here. In summary, it looks like the file should be on the same machine as the SQL Server for it to work. To make it look for the local file, it should be done via UNC share or something similar.