Solved

tsql sql 2000 is the text file open (locked)

Posted on 2009-05-09
17
363 Views
Last Modified: 2012-05-06
I want to be able to check a text file to see if its open and if it is wait until its closed again after which im going to read the contents into sl server 2000
0
Comment
Question by:9772885
[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
  • 7
  • 5
  • 2
  • +1
17 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 24344241
maybe could use some vbs script like

Dim ff As Integer
On Error GoTo errHandler

ff = FreeFile
'run you code here.....................
Close #ff

errHandler:
    wait 20 seconds


unsure if can be done in just sql
0
 

Author Comment

by:9772885
ID: 24344966
how can i do that using tsql rather than vb?
0
 
LVL 2

Expert Comment

by:shoppedude
ID: 24344971
You can accomplish this through SQL using the sp_OA method and the File system object.  A very thorough write up on how to code the process to check the actual file status as well as create a "waiting" loop can be found at http://www.databasejournal.com/features/mssql/article.php/3492046/SQL-Server-How-to-Check-the-Status-of-a-File-Before-Processing.htm .

Hope this helps!

-Lee
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 31

Expert Comment

by:James Murrell
ID: 24344990
nice catch shoppedude works for me

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24348987
Or simply used the undocumented function xp_readerrorlog
0
 

Author Comment

by:9772885
ID: 24364286
i've tried the link but i just seem to get a 0 return code whether the file is closed, open or being written to.

acperkins, i had a look at your suggestion but from what i could see it was for 2005
0
 
LVL 2

Expert Comment

by:shoppedude
ID: 24364503
Is the text file that is being written to located on a physical drive where the script is being run or a network share?

-Lee
0
 

Author Comment

by:9772885
ID: 24364987
At present ive been running a test kicking off the script on my local drive. The text file was being written to a server networked drive (same server as sql server 2000).

in the future i plan to kick off the script from and export to the same folder on the server, but im waiting for something to be installed so the script wil run from that location.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24365643
>>i had a look at your suggestion but from what i could see it was for 2005<<
I believe it was available as far back as SQL Server 7 and I have certainly used it with SQL Server 2000.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24512722
Did you even try my solution?
0
 

Author Comment

by:9772885
ID: 24512736
i have looked at the error log but dont see how its useful for identifying if a text file is locked. Do you have an example showing how to use it?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24512789
>>i have looked at the error log<<
I never said anything about the error log.  I referred to the "undocumented function xp_readerrorlog".  While it can be used to read the SQL Server error log and SQ Server Agent log, you can also use it to read any file.

But you are obviously not that interested in a solution, so I won't waste anymore of your time.

Good luck.
0
 

Author Comment

by:9772885
ID: 24512827
I am really interested and would appreciate any help you can provide, Im just not sure how to set this up?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24512867
This link contains a couple of T-SQL solutions (one uses xp_readerrorlog):
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21279024.html

And this one also contains two solutions:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21086593.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24523411
I object as once again I am not getting any feedback to the solutions, nor does your history of abandoned questions infuse me with much hope that this thread is going to be any different.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24532786
I believe the solution provided here http:#a24512867 should be awarded points.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
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.

728 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