• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

tsql sql 2000 is the text file open (locked)

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
9772885
Asked:
9772885
  • 7
  • 5
  • 2
  • +1
1 Solution
 
James MurrellProduct SpecialistCommented:
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
 
9772885Author Commented:
how can i do that using tsql rather than vb?
0
 
shoppedudeCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
James MurrellProduct SpecialistCommented:
nice catch shoppedude works for me

0
 
Anthony PerkinsCommented:
Or simply used the undocumented function xp_readerrorlog
0
 
9772885Author Commented:
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
 
shoppedudeCommented:
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
 
9772885Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
Did you even try my solution?
0
 
9772885Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
9772885Author Commented:
I am really interested and would appreciate any help you can provide, Im just not sure how to set this up?
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
I believe the solution provided here http:#a24512867 should be awarded points.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now