?
Solved

Stored Procedure checking for File existance

Posted on 2002-07-09
10
Medium Priority
?
393 Views
Last Modified: 2012-12-11
Can a stored procedure read a physical drive. I want to make an stored procedure that sets a Boolean value based on the existence of a file on a drive. It would not be located on the same server as SQL. Are there any security issues too. An example would be nice.
0
Comment
Question by:rian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7141472
Please maintain these open questions:

2000 Mapping Error Date: 07/25/2001 01:53PM PST
http://www.experts-exchange.com/win2k/Q_20157467.html
Flex Grid Question Date: 06/06/2002 01:09PM PST  
http://www.experts-exchange.com/visualbasic/Q_20308730.html
FlexGrid Multiple selection Date: 04/16/2002 08:21AM PST
http://www.experts-exchange.com/visualbasic/Q_20290075.html

Thanks,
Anthony

0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 7142009
I usually do it via a dir command but you could also use the file system object.

create procedure spCheck
@filename varchar(128) ,
@fileexists int output
as

select @fileexists = 0
create table #a (s varchar(1000))
declare @sql varchar(1000)
select @sql = 'dir c:\dir\*.*'
insert #a exec xp_cmdshell @sql
if exists (select * from #a where s like '%' + @filename + '%'
select @fileexists = 1

go

You will need permission on the directory for the user context under which xp_cmdshell runs on your system - probably the sql server service.
0
 
LVL 9

Expert Comment

by:miron
ID: 7142308
an extended stored procedure xp_fileexist migth be of help.

execute master.dbo.xp_fileexist 'C:\boot.ini'

output

[File Exists] 1 -- integer
[File is a Directory] 0 --integer
[Parent Directory Exists] 1 --integer
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7142629
>>it would not be located on the same server as SQL
This said, the stored proc can only see local drives to the SQL Server, for drives on other PC it would be an UNC path (\\workstation\C$\), for which the (admin) permissions need to be given
CHeers
0
 

Author Comment

by:rian
ID: 7143826
WHat kind of security do I need to setup. I have a server called MAserver which is connected to my machine. However when I do a
execute master.dbo.xp_fileexist '\\maserver\cdrive\config.sys'

it gives me 0,0,0 in all three fields. that file does exist. and I am logged on using query analyzer as sa. plus xp_fileexist is set for public access.
Ofcourse, this procedure will probably be run on individual sql accounts.
0
 
LVL 9

Expert Comment

by:miron
ID: 7145130
Looks like stored procedure is working, it returned zeros, indicative that it was un-able to see the share.

I would verify account running sql server, it need to be domain account, not a local to the machine running sql server, also this account need to have "full control" privilege granted to access the share, where file was checked.

Cheers
0
 

Author Comment

by:rian
ID: 7146463
I am not sure how you can set security setting in Windows for SQL users only. For example, I have a SQL user called joe with password joeb and when the user logs on, the sql connection is made with his username and password.
0
 
LVL 9

Accepted Solution

by:
miron earned 400 total points
ID: 7148212
sql users authenticated via sql server authentication are not the ones you need to worry when connecting to remote share. Please ,try to find account, Windows account that is running sql server, then everything will be clear.
0
 

Expert Comment

by:CleanupPing
ID: 9280174
rian:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question