SQL Bulk Insert Problems !

Question

SQL Bulk Insert Problems !

John Burchel

Hall of Fame

Points: 3611
More actions
November 28, 2006 at 6:38 am

#69160

Hi All,

I have a problem with Bulk insert and security, here is what happens when a use runs the code below:

Windows Authentication SA rights: Fails
SA login remotely: Works
Windows Authentication on server (Using SA rights): Works

Error: Cannot bulk load because the file "\\10.100.2.54\PersonProfile.txt'" could not be opened. Operating system error code 5(error not found).

BULK INSERT #TEMP_BCP
FROM '\\10.100.2.54\PersonProfile.txt'
WITH (FORMATFILE = 'E:\formatfile.fmt' ))

Any help would be appreciated.

Thanks
Kindest Regards,
John Burchel (Trainee Developer)

Viewing 15 posts - 1 through 15 (of 17 total)

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello John,

It seems that the login with which you are trying to bulk insert the data, is not having enough permissions on the network / remote path.

Operating System Error Code 5 resembles to "Access is denied".

Hope this helps you in resolving the problem.

Thanks

Lucky

Peter Gadsby SSCrazy Points: 2718 More actions · Answer 2

Hi,

When it fails are you running the process using SQL Agent? If so does the SQL Agent login have permission to the file you are bulk loading?

Pete

Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk

RichB SSCrazy Eights Points: 9651 More actions · Answer 3

Daft question, but is it actually set up as a network share?

John Burchel Hall of Fame Points: 3611 More actions · Answer 4

Hi

Thanks all

I am actually not running from a job, i am running from Query analyzer.

If its executed from the server, it works fine, so it will work in SQL agent, but for some reason when executed even with Windows Authentication and SA rights from my Pc it doesnt work, but if I do thesame on my PC using SA SQL login details, it works.

Thanks

Kindest Regards,

John Burchel (Trainee Developer)

Peter Gadsby SSCrazy Points: 2718 More actions · Answer 5

From your local PC can you open the file?

Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 6

Are you sure you have privilege to access the text file?

John Burchel Hall of Fame Points: 3611 More actions · Answer 7

I can open the text file and read/write to it.

I am not sure if this is related, the SQL Server startup account is a local account, but the path isnt on a domain.

But I doubt its the SQL server account that is the problem, as the process works on the server, and also works on my Pc If I use SA login.

Kindest Regards,

John Burchel (Trainee Developer)

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 8

That might be problem. Does the local account have the privilege to access the text file? Or more precisely, does the account you used have the privilege to access the text file?

This is the first spot I would like to make sure.

John Burchel Hall of Fame Points: 3611 More actions · Answer 9

Hi

Yes it does, thesame account is used for SQL server user SA and Bulk insert works.

what amazes me is why it doesnt work for windows authentication with SA rights.

It works on the server with Windows Authentication works with Bulk Insert, so there is a bit of inconsistency there. If Windows Authentication didnt work on the server, I would have suspected the account.

Kindest Regards,

John Burchel (Trainee Developer)

senthilnath.nathan SSC Rookie Points: 46 More actions · Answer 10

Hi,

Iam also getting the same error.But my server set up is different.

I have a Server A which has SQL Server 2005 installed.Another Windows NT server(Server B) has Perl script and the Flat file.

Iam executing the Perl script from Windows NT server which checks the flat file first in the same error,then connects to the SQL server 2005(server B) using DBI:ODBC connection.

After getting the connection when the Bulk insert query is executed

my $query = "BULK INSERT $TableName FROM $DB_file WITH (FIELDTERMINATOR = '|',ROWTERMINATOR = '\',MAXERRORS=5)";

my $sth = $dbh->prepare($query);

$sth->execute() or die("Error in inserting row , error code DBI:errstr");

$sth->finish();

Iam getting the error

"DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Can

not bulk load because the file "MPP.NA.MPD051B.LMIKB.LOD" could not be opened. Operating system error code 5(Access is denied.). (SQL-42000)(D

BD: st_execute/SQLExecute err=-1)".

The SQL Server is access using the ID "sa".

Please provide a solution to solve the above ID.

Thanks in Advance

SenthilNathan

reto SSC Enthusiast Points: 138 More actions · Answer 11

Hi John

I'm having the same problem here with Windows Authentication for bulk insert:

Cannot bulk load because the file "\\WXPPRO-xxx98\C_Drive\SPAImport.txt" could not be opened. Operating system error code 5(Access is denied.).

If the file is on the sql server it works, but that's not what i need.

Did you get any helo on this?

Regards

Reto

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 12

John,

Might the problem be that the format file does not exist on your PC rather than the file you are trying to load? The format file is explicitly identified as E:\.

I would have thought that it would make no difference as the code is submitted to the server and then executed as opposed to DTS where the package is executed on your local machine.

I might be barking up the wrong tree (or just barking) but worth checking.

Jez

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

We had similar problems with many features of SQL Server when we shifted to Windows Authentication... they all went away when we granted the machine account extra privs on it's own C:\Temp folder of the server. Fixed OPENROWSET, LINKED SERVERS, BULK INSERT, etc, etc, etc. You may also have to change a registry entry to allow certain ad-hoc updates via OPENROWSET and LINKED SERVERS... find the following registry entry for SQL Server and change it as indicated in the following...

DisallowAdhocAccess = 0

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Also, the Server account must be an account that has the privs to see the file. So does the user running the Bulk Insert... doesn't matter if they have SA privs or not... they have to have privs to see the source for Bulk Insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)