Post reply

Importing data from a flat file

  • December 3, 2011 at 4:20 am

    #263934

    I have just upgraded from SQL 2008 Enterpise to SQL 2008 R2 Enterprise. Prior to the upgrade I had several procedures which imported data from flat files located on another server. These imports have been working properly for over a year. Since the upgrade I am getting access denied errors. The upgrade did not change the domain account used to start the SQL services. Is there a new security setting in SQL 2008 R2 that needs to be set?

    Thanks

    Chuck

  • December 3, 2011 at 4:31 am

    #1416273

    How are you importing from flat files? What’s the error message?

    I believe advanced options might have reset. Please crosscheck those.

    Setting Server Configuration Options

    http://msdn.microsoft.com/en-us/library/ms189631.aspx

  • December 3, 2011 at 4:59 am

    #1416275

    I'm using bulk insert. The exact error is:

    Cannot bulk load because the file "<<file.csv>>" could not be opened. Operating system error code 5(Access is denied.).

    The domain user executing the script and the domain user starting the SQL service are both domain administrators and in the sysadmin server roll.

    I don't recall making in configuration perminent changes in prior service. I do make changes to configuation in procedures by turning on what I need, executing script and then turning id off again.

    Are you aware of any default configuration changes that may be in R2 that might cause this issue?

    Thanks

    Chuck

  • December 3, 2011 at 5:19 am

    #1416279

    Please check the permissions on the file "<<file.csv>>".

  • December 3, 2011 at 5:32 am

    #1416280

    The permission are the same as they were before the upgrade to R2. Domain users have read execute and domain administrators have full control.

    Chuck

  • December 3, 2011 at 5:37 am

    #1416283

    I guess these two culprits. Can you please post your Bulk Insert code?

    ad hoc distributed queries Option

    By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

    xp_cmdshell Option

    Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure

  • December 3, 2011 at 6:25 am

    #1416287

    Here is the code:

    bulk insert #t2 from '\\efs2\e$\WebFiles\Uploads\boaMother.csv' with(firstrow=5,fieldterminator=',',rowterminator='

    Is bulk insert considered Ad Hoc?

    Chuck

  • December 3, 2011 at 6:29 am

    #1416288

    Chuck 17099 (12/3/2011)

    Here is the code:

    bulk insert #t2 from '\\efs2\e$\WebFiles\Uploads\boaMother.csv' with(firstrow=5,fieldterminator=',',rowterminator='

    Is bulk insert considered Ad Hoc?

    Chuck

    Nope.

  • December 3, 2011 at 6:33 am

    #1416289

    My doubt is more on file permissions only.

    Can you login on server (Windows Login) and open '\\efs2\e$\WebFiles\Uploads\boaMother.csv' file in excel or notepad?

  • December 3, 2011 at 6:36 am

    #1416290

    Yes

  • December 3, 2011 at 6:41 am

    #1416292

    I didn’t want to ask for it but did you restart services / server after upgrade?

    Is downtime acceptable?

Viewing 11 posts - 1 through 11 (of 11 total)

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