SQL Server Performance

What ID is used for Import Wizard?

Discussion in 'SQL Server 2005 General DBA Questions' started by matty1stop, Dec 19, 2006.

  1. matty1stop New Member

    Does anyone know what ID is used for the import wizard?

    am trying to run a bulk insert statement, but continue to get error messages. I have checked with our server team and they state that the SQLService network ID has rights to the fileshare.

    I can import the file to the network sql server using the import wizard. I can import the file to my local server from the network share using the same bulk insert statement. But when I try to run the bulk insert from the network SQL Server I get the following message. As I said the server team claims that the sql service ID has rights to the share.

    Thanks for your help,
    Matt

    Error Message:
    Msg 4861, Level 16, State 1, Line 1
    Cannot bulk load because the file "\MyServerMyShareTuesday_DailyHours.csv" could not be opened. Operating system error code 5(Access is denied.).

    Here is the code

    declare @dayName varchar(32)
    declare @filePathAndName varchar(256)

    set @dayName =
    CASE datepart(dw,getdate())
    WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    When 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    When 7 THEN 'Saturday'
    END

    set @filePathAndName = '\MyServerMyShare' + @dayName + '_DailyHours.csv'

    declare @insertStatement varchar(256)
    set @insertStatement =
    'BULK INSERT hoursTable FROM ''' +
    @filePathAndName +
    ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''
    '')'

    exec(@insertStatement)
  2. MohammedU New Member

    "Operating system error code 5(Access is denied.)" means it is access/Security issue.

    When you run the import/export wizard, it uses your authentication when run it as a job it uses sql service account.
    Make sure sql service account has the access to the file.



    Mohammed U.
  3. satya Moderator

    Specifically SQL Server agent service account.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. matty1stop New Member

    Thanks for the info. Do you know where I can look to determine what ID the account uses?
  5. MohammedU New Member

    You can through SQL Server Configuration Manager or Services window...


    Mohammed U.
  6. satya Moderator

    Or even looking at the SQL server properties.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page