Upload directory in Stored Procedure

Question

Upload directory in Stored Procedure

Meatloaf

SSCrazy

Points: 2564
More actions
May 6, 2013 at 9:54 am

#100150

Hi,
I am using a stored procedure and would like it to find a file on a different server location. When on the server, I have access to the drive using UNC or if I map a drive. However, when I try to do this within my Stored Procedure it does not work. Any suggestions to what the problem may be?
The problem seems to be related to trying to reach the other server. (Could this be a network permission?)
Here is my code:
@GetFileName nvarchar(500),
@filedir nvarchar(50),
@cleanfilename nvarchar(500),
@filepath nvarchar(500),
@dircmd nvarchar(500),
-- 1. Get File from Upload directory and pull out file name
-- SET @filedir = 'Y:\' Mapped drive attempt
SET @filedir = '\\servername\directory\'
SET @cleanfilename = (SELECT SUBSTRING(@getfilename,4,100))
SET @filepath = @filedir + @cleanfilename
SET @dircmd = 'dir ' + @filepath

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

rayh 98086 (5/6/2013)
Hi,
I am using a stored procedure and would like it to find a file on a different server location. When on the server, I have access to the drive using UNC or if I map a drive. However, when I try to do this within my Stored Procedure it does not work. Any suggestions to what the problem may be?
The problem seems to be related to trying to reach the other server. (Could this be a network permission?)
Here is my code:
@GetFileName nvarchar(500),
@filedir nvarchar(50),
@cleanfilename nvarchar(500),
@filepath nvarchar(500),
@dircmd nvarchar(500),
-- 1. Get File from Upload directory and pull out file name
-- SET @filedir = 'Y:\' Mapped drive attempt
SET @filedir = '\\servername\directory\'
SET @cleanfilename = (SELECT SUBSTRING(@getfilename,4,100))
SET @filepath = @filedir + @cleanfilename
SET @dircmd = 'dir ' + @filepath

Can you explain "does not work"? From what you posted I would expect that you get NULL as @cleanfilename because @getfilename is NULL. We need a little more detail here to know where to begin.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Meatloaf SSCrazy Points: 2564 More actions · Answer 2

Hi Sean,

Thank you. What I mean is that the file cannot be found, I cannot reach the directory - its as if the directory is not there.

Lowell SSC Guru Points: 323504 More actions · Answer 3

almost certainly permissions, because the call to disk is not going to use the account you are expecting it to.

when you access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,

SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

so you typically want to create a new user on the local machine or in active directory which would have access to the shared network resources in question, and then to change the startup account that SQl server is using;

here's an example where i put in my account instead, with my domain account and password, and then i can test the shared path i was trying to access/use before;

[/quote]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Meatloaf SSCrazy Points: 2564 More actions · Answer 4

Hi Lowell,

Thank you very much, this looks like it could be it as the Logon is not showing under the Services. I am checking with our server team. Thanks again!!:-)