Cannot reference file in BULK INSERT statement

[dba112233]

for some reason, SQL cannot open the file below. I checked the path and in the run prompt it opens fine. It's a synax error I think but the error doesn't say, it's just a generic error:

BULK INSERT tablename FROM 'c:\Documents and Settings\sss\Desktop\myfile.txt'

 

[gmmastros]

Is SQL Running on the same computer you are trying to BULK Insert from?

Ex. Here at the office, I have a workstation (let's call it GEORGE) and sql installed on a server (let's call it SQL_SERVER).

If you are running the command from the workstation, and the file is on the workstation, but SQL is on a server, then this will not work as you expect it to.

The command will run on the SQL Server computer. Does the SQL Server have a folder named 'c:\Documents and Settings\sss\Desktop\myfile.txt' ?

You may also run in to permissions issues. Whatever account you are using for SQL Server must have permissions to the folder and file you are trying to bulk load.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dba112233]

>>>>Is SQL Running on the same computer you are trying to BULK Insert from?
Not the server, the user simply has a client install where they're using Analyzer to talk to a remote server to insert records into a table they created

Ex. Here at the office, I have a workstation (let's call it GEORGE) and sql installed on a server (let's call it SQL_SERVER).

>>>If you are running the command from the workstation, and the file is on the workstation, but SQL is on a server, then this will not work as you expect it to.

 

[dba112233]

thank you, I understand. Really, it's looking at the server's C drive at that point because you're connected to the server in Analyzer, not (local)

 

[gmmastros]

Yes, you got it.

What some people do is to set up a share on the server to hold the data files. The app copies the file to the server and then runs a stored procedure which performs the bulk insert.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jekker]

Hi
I am a total novice on SQL and am getting this problem returned from our software.

Package Failed error x0, 0

Step GenericPkgStep failed x80040E14 x800400000 + 3604
Could not bulk insert because file
'{{network path}}\/CONFGPRM.tmp' could not be opened.
Operating system error code 5 (access is denied)

Have not included proper network path as it is confidential due to the nature of our business.

This operation (the one above) worked fine until applied SQL SP4 to accommodate the latest release of our software.

Any ideas on what could cause this ??

 

[gmmastros]

Most likely, you are running in to a permissions error.

I may be wrong here, but, this is what I suggest you check first.

The SQL Server service is started with a particular account. To see which account SQL Server uses...

Click Start -> Run
services.msc

Scroll through the list. You will see...
MSSQLSERVER
OR
MSSQLSERVER$instance name

Right click -> Properties
Click Log On tab.

The log on can be set the 'Local system account', or you can specify an account. In either case, make sure that the account used by the SQL Server service has access to the folder where your file is located.

* Note: This may not fix your problem, but this is where I would look first.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom