0

I am trying to bulk insert a file from remote server to MS SQL server.

CONN = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ReadEMAPI.My.MySettings.IntradayDataConn").ConnectionString)
cmd.Connection = CONN
CONN.Open()
cmd.CommandText = "BULK INSERT BidAsk FROM '" & "G:\Intraday Data\EMAPI\SET\Test.csv" & "' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n',KEEPNULLS)"
cmd.ExecuteNonQuery()
CONN.Close()

I got this error:

Cannot bulk load because the file ""G:\Intraday Data\EMAPI\SET\Test.csv"" could not be opened. Operating system error code 3(The system cannot find the path specified.).

However, when I ran the sql command exactly the same in MSSQL Management Studio on the server, it was able to execute correctly.

Please help.

jarlh
  • 42,561
  • 8
  • 45
  • 63
tisaros
  • 1
  • 2
  • Maybe the drive is not known as "G" when entering remotely? Perhaps something like this could help: https://stackoverflow.com/questions/21482825/find-unc-path-of-a-network-drive – Nanna Mar 03 '20 at 08:41
  • 2
    If `G:` is a network path, it's generally better to use UNC paths. Also ensure SQL Server Service account has access to the directory. – Thom A Mar 03 '20 at 08:57
  • When you logged in to SSMS to run the command were you using Domain or Windows credentials by chance? When that happens drive and share access for `BULK INSERT` happens in the context of your own Domain or Windows account. If the VB program uses different Domain or Windows credentials then the `BULK INSERT` happens in the context of that Domain or Windows account. If the VB program uses SQL Login credentials then the `BULK INSERT` happens in the context of the Domain or Windows account under which the SQL Server service is running. – AlwaysLearning Mar 03 '20 at 09:13
  • @AlwaysLearning access to the file will be under the credentials of the SQL Server Service Account, not the Login/User who is connected to the instance. Otherwise, for example, it would be impossible to perform a `BULK INSERT` with a SQL Authentication Login. The `LOGIN` simply needs permission to perform Bulk Operations. – Thom A Mar 03 '20 at 10:49
  • Your error message looks suspicious. Is this your actual code? It makes no sense to hard code a path and use concatenation with a bunch of literals to create a dynamic query that is completely static. – SMor Mar 03 '20 at 12:51

0 Answers0