Error in BULK insert query

Question

Error in BULK insert query

senthilnath.nathan

SSC Rookie

Points: 46
More actions
November 2, 2007 at 8:29 am

#70385

Hi,
I have problem in executing the Bulk insert query from Perl to a SQL Server 2005.
My Setup is
Server A : WINDOWS NT Server has Perl script and Flatfile
Server B : WINDOWS NT SQL Server 2005.
Iam Executing a perl script from Server A which first checks whether the prescribed Flatfile is present in Server A.Then executes a connection to SQL Server 2005 using DBI:ODBC connection using the login "sa".
Then it will executes the Bulk insert query like this way.
my $query = "BULK INSERT $TableName FROM $DB_file WITH (FIELDTERMINATOR = '|',ROWTERMINATOR = '\',MAXERRORS=10)";
my $sth = $dbh->prepare($query);
$sth->execute() or die("Error in inserting row , error code DBI:errstr");
$sth->finish();
The Bulk insert query has to take the file from Server A which the folder is shared for any domain user.
But during execution iam getting the following error.
"DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot bulk load because the file "\\ServerA\share\MPP.NA.MPD051B.LMIKB.LOD" could not be opened. Operating system error code 5(Access is denied.). (SQL-42000)(DBD: st_execute/SQLExecute err=-1)"
Please provide resolution for the above error.
Thanks in Advance
SenthilNathan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

The system has to be able to see the file to load it... make sure SQL Server Services use a startup account that can see the path to the file.

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Does the WIndowsNT account running SQL Server have appropriate access rights to folder \\ServerA\share\ ?

_____________
Code for TallyGenerator

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

Hi,

Thanks for the response.

The Windows SQL server able to access the flat file present in the Windows NT server(another server).The file path is shared to access for all the domain ID's.

Also iam using the domain ID for both the system.

Please let me know what other access i have to give for any ID to access the file through the Query from SQL server.

To execute the query iam using the sa login in SQL server 2005.

Thanks in Advance

SenthilNathan.A

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Does the SQL Server service start under a local account or a domain account?

John

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

Hi,

It was started with "Local System" account.Did i have to change to my Domain account.

Thanks

SenthilNathan.A

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Yes, you do. The account needs to have permissions on the share that you're copying to or from.

John

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

Hi,

I made the Service to start with my Domain id.It is in my office network.

Using that id i can share the Flat file from Windows system which has SQL server 2005.

Even the perl script which is running in another Windows NT system also uses this Domain ID to execute.

Shall i use the sa login in SQL server 2005 to connect the DATABASE from my Perl script.

Thanks

Senthilnathan.A

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Sentilnathan

It's not really a good idea from a security point of view to use sa for application use. But from a purely technical perspective, you should be able to use any login that has the necessary permissions on the database.

John

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

Hi John,

Now i have done the set up as per the discussion.

But still the issue persist.Please help me to solve the issue.

Thanks

Senthilnathan.A