Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2000 Stored Procedure - Operating system error code 5(Access is denied.).

Posted on 2009-05-08
9
Medium Priority
?
726 Views
Last Modified: 2012-06-27
Hi - I'm receiving an error in a SQL Server 2000 Stored Procedure using Bulk Insert.  

Here's the error:

"Could not bulk insert because file '\\server\<path>\file.txt' could not be opened. Operating system error code 5(Access is denied.)."

This has worked for several years, and it appears that there were some recent changes made that is causing this error.  If I log into the database server, I can get to the file path in question, so even though the message says "Access is denied" I'm not convinced that is the issue.

Has anyone seen this before?

Thanks.
Rob


BULK INSERT ProcedureName 
FROM '\\server\<path>\file.txt'
WITH (
   FIRSTROW = 2,
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = '\t',
   ROWTERMINATOR = '\n',
   MAXERRORS = 1
 
)

Open in new window

0
Comment
Question by:Rob132332
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 800 total points
ID: 24338129
Are you sure your procedure has access to that folder/file?
0
 
LVL 11

Author Comment

by:Rob132332
ID: 24338144
mohan - could you give me some more info to check what you're suggesting?  steps, things to check?
0
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 800 total points
ID: 24338174
If the txt file is in a shared folder on a different server, check if 'read' permission is granted to your user id

for more info - http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/137d2f16-bed3-4b98-a16f-f6db1a961c46
0
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 24338518
Or to be more precise since you are using BULK INSERT that the SQL Server service startup account has access to those files and is not a Local System account.
0
 
LVL 11

Author Comment

by:Rob132332
ID: 24338605
and, how would I check that the SQL Server Service startup account has access?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24338967
You either check it yourself or ask your DBA.
0
 
LVL 11

Author Comment

by:Rob132332
ID: 24339048
After re-reading my last response, it was unclear.  
I'm asking "how" I would check it myself.  Specific steps would be great, as I'm not familiar with how to do this.  
Thanks.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1200 total points
ID: 24340237
Look for the SQL Server (MSSQLSERVER) service and see what account is being used?  For example is it a Local System account, is it a domain account?
0
 
LVL 11

Author Comment

by:Rob132332
ID: 24354544
That did it - the account that was listed under the MSSQLSERVER service did not have permissions to get to the necessary network path.  Thanks for your help.  Rob
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

During and after that shift to cloud, one area that still poses a struggle for many organizations is what to do with their department file shares.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question