BULKINSERT Network Access denied Issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BULKINSERT Network Access denied Issues

Author

Topic

k006b
Starting Member

10 Posts

Posted - 2005-01-21 : 15:24:06

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.

USE TESTDB

--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)
as
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
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @query
exec (@query)
insert into logtable (query) select @query
end

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.
K006B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-21 : 16:26:28

So the account that runs this code, does it have access to that share? Just because a share exists doesn't mean that everyone has access to it. And can the database server get to that share? BULK INSERT runs from the database server and not from the client.

Tara

k006b
Starting Member

10 Posts

Posted - 2005-01-24 : 10:50:49

Dear Tara,

Thanks for your quick reply,

I am using an administrator account on the client machine and using sa account on sql server query analyzer. The folder where the sql script is lying is a shared folder. But i can manage to run this script on the server itself till i get a PERMANENT solution. Another problem is this script is working perfectly on Sqlserver7.0 and giving error on sqlserver 2000. Would appreciate if you could give me the correct syntax to get the following output.

set @query ='BULK INSERT '+ @Tablename + ' FROM "'+
@Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'

if i use the above line on the script it is returing error near comma(,) on sqlserver2000 whereas the same script is working perftly without any problems.

I should get the following EXACTLY if i run the script.

BULK INSERT kbAccount FROM 'c:\myimport\a.csv' WITH ( FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')

BULK INSERT kbAccount FROM 'c:\myimport\b.csv' WITH ( FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')

BULK INSERT kbAccount FROM 'c:\myimport\c.csv' WITH ( FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')

a.csv,b.csv,c.csv are the files available on c:\myimport folder on server where the sqlserver2000 is installed.

I am facing problems because of single quotes.

thanks in advance
K006B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-24 : 12:46:18

Run:

PRINT @query

to see what your code is doing.

Just because a share is setup doesn't mean that permissions have been set. I would try Windows Authentication from Query Analyzer rather than sa. Make sure to login with a domain account that has permissions on the share, preferably one that also has local admin on the db server.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources