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.

Improve this question
5
  • Maybe the drive is not known as "G" when entering remotely? Perhaps something like this could help: stackoverflow.com/questions/21482825/… Commented Mar 3, 2020 at 8: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. Commented Mar 3, 2020 at 8: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. Commented Mar 3, 2020 at 9: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. Commented Mar 3, 2020 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. Commented Mar 3, 2020 at 12:51
Related questions
3
SQL Server Bulk Insert failing when called from .NET SqlCommand
2
Can't bulk insert file
1
SQL Server Bulk Insert fails (Network related)
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.