x

Issue while running the bulk insert script(SQL SERVER 2005)

Hi,

        I am facing some issue with running the Bulk Insert script. Here is the detailed information.                     
                    
        I am making an installer which will run on exchange server. This server may have the sql server on another machine on network.                     
                    
        So what I am doing is, in my installer I am creating one Shared Folder and Replace that path in the sql script. and than execute the script.                     
                    
        See below example.                    

BULK

INSERT Accounts

FROM '\MYEXCHANGEMACHINE\DBScripts\Accounts.txt’

WITH

(

-- FIRSTROW = 2,

  FIELDTERMINATOR =  '`~',                    
                    
  ROWTERMINATOR = '\n'                    

)

GO

Here MYEXCHANGEMACHINE is my exchange server machine and DBScripts is my Shared Folder.

 But while I am running this script, I am getting following error. 

    **Cannot bulk load because the file "\\DT424\DBScripts\Accounts.txt" could not be opened. Operating system error code 5(Access is denied.).**                              

     I searched a lot on google but no luck..                     

    Any idea or any other alternate solution?                    
more ▼

asked Mar 22, 2010 at 11:08 AM in Default

user-993 (yahoo) gravatar image

user-993 (yahoo)
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You need to make sure that your SQL Server service account has access to the remote share. If you are running the service as localsystem or network service you will have some issues connecting. Network Service can be granted on the share but localsystem is a non-started. Using a domain account is the best option in this case, check the Share and NTFS permissions on your exchange server to add the account.

more ▼

answered Mar 22, 2010 at 06:37 PM

Jason Cumberland gravatar image

Jason Cumberland
507 2

Hi jason, can you please explain little in detail.

currently what I am doing is:
1) I have a domain account in Exchange Server
2) I am running my Installer with this account only
3) I am creating one shared folder on local machine
4) In my Installer I am asking SQL Username/Password/Server name and I am using this credentials to run the DB Script in my Installer. SQL Server will be on same network only. And while running the DB script I am replacing BULK insert file path with the UNC path. can you please tell me what I have to change to successfully run the Script?
Mar 23, 2010 at 02:52 AM user-993 (yahoo)
Check to make sur ethat the service account is running as a domain account. Make sure that user has access (share and NTFS) to the share you are trying to pull the file from.
Mar 24, 2010 at 03:23 AM Jason Cumberland
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103

asked: Mar 22, 2010 at 11:08 AM

Seen: 1391 times

Last Updated: Mar 22, 2010 at 11:08 AM