[Resolved] Accessing Text file in Shared Folder

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 2005 Forums

Transact-SQL (2005)

[Resolved] Accessing Text file in Shared Folder

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-06-29 : 08:34:30

Getting an error accessing a .txt file.

Old way
Stored procedure located on server VGIWPW03-SQL and shared folder pointing to .txt files also located on VGIWPW03-SQL3. This solution is working fine.

New Way
We had to move the stored procedure to a different server VGIWPSQL2. The shared folder and corresponding .txt files remains on VGIWPW03-SLQ3 server.

Now running stored procedure renders an error:

Could not bulk insert because file '\\VGIWPW03-SQL3\AppNet$\20100628_111354.txt' could not be opened. Operating system error code 5(Access is denied.).

Code:

declare @PathFileName varchar(200)

set  @PathFileName = '\\VGIWPW03-SQL3\AppNet$\20100628_111354.txt'

If Exists(Select * From Information_Schema.Tables Where Table_Type = 'Base Table' And Table_Name = '#JobListTable')
 Begin
 	Drop Table #JobListTable
  End

CREATE TABLE	#JobListTable

		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_delivered decimal(8,2),
			qty_received decimal(8,2),
			qty_diff decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			plant_id char(10)
                                        		)

DECLARE @SQL varchar(2000)
 
SET @SQL = 'BULK INSERT #JobListTable FROM ''' + @PathFileName + ''' WITH (FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
EXEC (@SQL)

I assume that this has something to do with the path to the VGIWPW03_SQL server that is not working. Not sure how to get around this. Can someone please give me a hand? Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 08:38:04

Operating system error code 5(Access is denied.)

Your sp/user on VGIWPSQL2 has no rights to access that folder.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-29 : 08:56:14

Can someone please post a solution on how to solve this problem?
Is it to do in Windows OS or in SQL Server or in both?

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-06-29 : 09:02:10

We have given useres all rights to shared folder, so I do not think this is the problem. Besides, it worked the "old" way. I think this has to do with pointing to the VGIWPW03-SQL3 server.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-06-29 : 11:28:10

I also tried this:

EXEC master.dbo.xp_cmdshell '\\VGIWPW03-SQL3\AppNet$\20100628_111354.txt'

and it gives me error:
Access is denied.

So I think this is an authority issue after all.

Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-29 : 14:03:59

The account SQL is running under does not have access to that folder/share. It is also possible that you have a double hop issue. So, if the SQL Account is running under a domain account, make sure that account has access to the share.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-06-29 : 15:07:25

Found the problem:

Was using serveradmin as SQL account. Once added it worked. Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources