SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upload directory in Stored Procedure


Upload directory in Stored Procedure

Author
Message
Meatloaf
Meatloaf
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
Points: 466 Visits: 421
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62293 Visits: 17954
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.

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)
Meatloaf
Meatloaf
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
Points: 466 Visits: 421
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
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71344 Visits: 40930
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
Meatloaf
SSC-Addicted
SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)SSC-Addicted (466 reputation)

Group: General Forum Members
Points: 466 Visits: 421
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!!:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search