Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Upload directory in Stored Procedure Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 9:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:58 AM
Points: 52, Visits: 133
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
Post #1449771
Posted Monday, May 6, 2013 10:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1449775
Posted Monday, May 6, 2013 10:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:58 AM
Points: 52, Visits: 133
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.
Post #1449776
Posted Monday, May 6, 2013 10:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449778
Posted Monday, May 6, 2013 10:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:58 AM
Points: 52, Visits: 133
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!!
Post #1449794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse