Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005

    Thumbs up Unanswered: IMPORT Multiple CSV Files to SQLSERVER Table

    Dear All,

    I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.


    --Table Creation Starts here

    Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
    AccountNo varchar(100), Balance money)

    Create table logtable (id int identity(1,1),
    Query varchar(1000),
    Importeddate datetime default getdate())

    --Table Creation ends here

    ---Stored Procedure Starts here

    Create procedure usp_ImportMultipleFiles @filepath varchar(500),
    @pattern varchar(100), @TableName varchar(128)
    set quoted_identifier off
    declare @query varchar(1000)
    declare @max1 int
    declare @count1 int
    Declare @filename varchar(100)
    set @count1 =0
    create table #x (name varchar(200))
    set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
    insert #x exec (@query)
    delete from #x where name is NULL
    select identity(int,1,1) as ID, name into #y from #x
    drop table #x
    set @max1 = (select max(ID) from #y)
    --print @max1
    --print @count1
    While @count1 <= @max1
    set @count1=@count1+1
    set @filename = (select name from #y where [id] = @count1)
    set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
    --print @query
    exec (@query)
    insert into logtable (query) select @query

    drop table #y

    --sp ends here

    Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

    If i use the above Exec like

    Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
    I am getting the following error:

    Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
    Operating system error code 5(Access is denied.).

    C Drive and MyImport folder is shared on system kb-02

    Would appreciate your valuable HELP.

    thanking your valuable help in advance.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    My guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.


  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts