Solved

SQL 2000 checking if file exist on disk and update status

Posted on 2009-04-08
2
653 Views
Last Modified: 2012-06-27
I have SQL base developed by someone else, I don't have option to contact for support.
I have a field file_name which contains path to file (\\servername\folder\...)
I have a field file_status which contains file status (D exist, DD deleted)

I have application which allow users to doubleclick to query result and open file in viewer.
Everything worked fine until few days. Now after every restart files change status to DD even they exist on disk, and aplication is showing that file is deleted. If I add a new file status is D which is OK. I am trying to figure what is the reason, but in meantime I will like to have temporary solution to allow users to see files.

I will like to have query which I will run when needed. I need to check if file with status DD and ID with name in file_name exist on disk and update that ID file status to D.

I hope I was clear enough.
0
Comment
Question by:Kechka
2 Comments
 
LVL 5

Assisted Solution

by:TheVeee
TheVeee earned 50 total points
ID: 24096407
Following your question.  

Things to check why your program isnt working anymore:
1.  What userid is your program running under? It may have lost authority to the directory to read.
2.  Is the directory the program is reading on the same machine?  If not, see if you can ping the machine from the machine running the program.
3.  If running against another machine for files, has the userid lost access to that machine?

The reason a program was created to run against your database to update statuses, is because sql was created to do maintenance against the database, not to process against the machine.  Your asking sql to process against the machine to find files and so forth, it wont, it will just update/delete/add against a database.
 
0
 
LVL 10

Accepted Solution

by:
Kechka earned 0 total points
ID: 24302034
I'll found this:
if object_id('tempdb..#temp') is not null
drop table #temp

CREATE TABLE #Temp(Directory varchar(1000),depth int)
INSERT INTO #temp
EXEC master..xp_dirtree 'C:\',1,0
IF @@ROWCOUNT > 0
 Print 'Path Exists'
ELSE
PRINT 'DIR Does Not Exist'
Will try to use...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

932 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now