Importing .txt file using BCP in a .vbs script

Question

Post reply

Importing .txt file using BCP in a .vbs script

Patrick Whitson

SSC Veteran

Points: 275
More actions
September 16, 2005 at 10:08 am

#67045

Hello all,
     I am new to the forum and have a question about running BCP through a VBScript.
     Currently we are testing importing a .txt file on a server path in the .vbs file using ADO connection. We are able to open the connection and delete records from the table, but when it comes to sending the BCP through ADO it gives an 8004e014 error.
     The error has told us that the file does not exist or path not found, but we have access to that folder and the file IS there. Any suggestions on using BCP through ADO?
     Heer is the script:
Dim cn
Dim strSQL

'create ADO connection and recordset objects

Set cn=CreateObject("ADODB.Connection")

'Open a connection to sqlserver

cn.open = "Provider=SQLOLEDB.1;Data Source=MHP-DB1;Initial Catalog=FACTSdata;user id=bills;password=potgod84"

'clean up table

strSQL="DELETE FROM tblMemberTest"
cn.execute strSQL

'load table from file using BULK INSERT statement
'This is where it's bombing
strSQL ="BULK INSERT FACTSdata.dbo.tblMemberTest" &_
  " FROM 'f:\usr\d3services\tblMember052571.txt'" & _
  " WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', FIRSTROW=2)"
cn.execute strSQL

'clean up ADO objects

Set cn=nothing

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

What is the error message?

Can you manually run the sql string in query analyzer?

Try it there, and you should see where the errors are

Patrick Whitson SSC Veteran Points: 275 More actions · Answer 2

The error given is:

Line: 24

Char: 2

Error: Could not Bulk Insert because file '\\..\..' could not be opened. Operating system error code 67 (the network name cannot be found).

Code: 80040E14

Source: Microsoft OLE DB Provider for SQL Server

We get the same error when we run it through Quer Analyzer as well.

Patrick

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

Check to make sure that the login who owns the SQLSERVERAGENT account has read access to the folder with the data in it.

Patrick Whitson SSC Veteran Points: 275 More actions · Answer 4

Our own user id's have privileges to read/write to the folder, but we are dealing with a third party controlling the SQL Server box.

I believe this is the issue as we have asked them to check to be sure the SQLSERVERAGENT id has access. We'll push ahead to find out.

Thanks.

Patrick

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 5

I misspoke earlier when I said the SQLSERVERAGENT account; I meant to say, SQLSERVERAGENT service. I see, however, that you interpreted my garbled message perfectly.

I haven't used BULK INSERT very much, but I assume it's just a front-end wrapper for BCP IN, and if that's the case, I was thinking that the login that owns the SQLSERVERAGENT service will need access to that file.