help writing a multiple table query

[mbatestblrock]

I really need some direction writing a query here.

I provided a screen cap so you may catch the drift.

http://www.mykesdesigns.com/tblrock1...s/terminfo.jpg

as you may see there are a ton of tables. there are 58 tables that are all nearly identical to the one shown...

each table represents a txt file that is imported every night to our server and each txt file is a store. These files hold the info about the terminals..

Every day I sift through these same 60 files and look to make sure #2 in the TermName field is set to Backup in the Role field, and also that no other Terms have the Role backup in the field.

I would really love some direction to take on how to make a query output a "REDFLAG" if you will, pointing out that a sertain table is not how it should be.


I hope this makes sense. Ill explain anything better that anyone wants me to.

Thanks a ton in advance!

[MMcCarthy]

Yeah I do not know a whole lot about ASP. I know that I have used it before to create a DB connection with access and it pulls a query for me, and it works wonderfully for me, And I figured the query is only thing I could figure out how to get working because it does not require me to open the DB or any of that. Thats why I was hoping to get all of this output into a query.

Pulling a table is the same as pulling a query. The issue will be triggering the creation of the table.

We have some very good experts in ASP. I'll ask a couple of them to look at this issue and see if its feasible.

Mary

[mbatestblrock]

good deal, I am looking forward to what they come up with!

[NeoPa]

How practical will it be to have this merge process run?
Pros :

1. Much (much) easier and efficient processing of the data in your queries.

Cons :

1. Data out of date if files updated in the background.
2. Need to find a way to trigger the preparation of the data without it running every time the data is queried from the web page.

If you're happy with this balance (only you really know the full requiirements of your system), then that's the way to proceed.
Let us know :)

[mbatestblrock]

How practical will it be to have this merge process run?
Pros :

1. Much (much) easier and efficient processing of the data in your queries.

Cons :

1. Data out of date if files updated in the background.
2. Need to find a way to trigger the preparation of the data without it running every time the data is queried from the web page.

If you're happy with this balance (only you really know the full requiirements of your system), then that's the way to proceed.
Let us know :)

Well the data will be changing in these files in the background, constantly.. Whatever it takes to launch the web page, and it does all the work for me. I have done it before. but I was dealing with VBA or anything. just straight up queries, and linked tables, and it worked just fine.

I dont know if this can be accomplished without using VBA, though?

[NeoPa]

Firstly, is the changing data critical?
Is it possible perhaps to run the merge of all the data before EVERY query of the database (seems unlikely)?
I believe that we can arrange for a query to be called from your web page which can simply return a function's value. If we make this function do some work before it returns the value then we should be able to use this technique to get some VBA code to run from your web page.

[mbatestblrock]

Firstly, is the changing data critical?
Is it possible perhaps to run the merge of all the data before EVERY query of the database (seems unlikely)?
I believe that we can arrange for a query to be called from your web page which can simply return a function's value. If we make this function do some work before it returns the value then we should be able to use this technique to get some VBA code to run from your web page.

Sounds awesome. The changing of data is very critical, If one of these is not set the way they should be, a bunch of data could be potentially lost. We check these files a good couple times a day, which is why I am trying to simplify our lives by doing this instead of reading through 70 text files. and Access is not on all of computers, THUS the web page idea spawned.

[MMcCarthy]

Sounds awesome. The changing of data is very critical, If one of these is not set the way they should be, a bunch of data could be potentially lost. We check these files a good couple times a day, which is why I am trying to simplify our lives by doing this instead of reading through 70 text files. and Access is not on all of computers, THUS the web page idea spawned.

To save some on the query load you could amalgamate the text files to one text file using VBA or possibly through ASP. However, it would still have to be triggered every time the data was accessed.

I have to go out for a few hours but will check in when I get back.

Mary

[mbatestblrock]

To save some on the query load you could amalgamate the text files to one text file using VBA or possibly through ASP. However, it would still have to be triggered every time the data was accessed.

I have to go out for a few hours but will check in when I get back.

Mary

But then I run into the problem with the store number, because all the queries I have is where I get the store number from. Unless there is still a way to got those dang store numbers in there??

[MMcCarthy]

But then I run into the problem with the store number, because all the queries I have is where I get the store number from. Unless there is still a way to got those dang store numbers in there??

That wouldn't be a problem.

To amalgamate a text file you have one empty file that you write new records to. You would loop through all the other files and read them in line by line. You can go to specific values on the line and read the value and set conditions on it just the same as any query. You can then add the store number or any any value you like when adding the data the new text file. Essentially as a new field.

What I'm not sure about is if this can all be done is ASP bypassing the database altogether. However, as Ade (NeoPa) said whatever way we do this will have to be triggered every time the data is accessed.

I'm off now but will check in when I get back.

Mary

[mbatestblrock]

That wouldn't be a problem.

To amalgamate a text file you have one empty file that you write new records to. You would loop through all the other files and read them in line by line. You can go to specific values on the line and read the value and set conditions on it just the same as any query. You can then add the store number or any any value you like when adding the data the new text file. Essentially as a new field.

What I'm not sure about is if this can all be done is ASP bypassing the database altogether. However, as Ade (NeoPa) said whatever way we do this will have to be triggered every time the data is accessed.

I'm off now but will check in when I get back.

Mary

So what we need to figure out is how to trigger this vba code in the databse with asp correct?

[markrawlingson]

Hello, mbatestblrock. I am one of the ASP Experts Mary spoke of above.

I have read through this entire thread, and perhaps I've missed something here because that was quite a bit to read; so if i have missed something please excuse my ignorance. The first question I have for you is:

Why are you transferring the data from the text files to access? You are effectively transferring one data store to another.

If there is no "technical" reason why you're moving the data from the text fles to access, I would use ASP's FileSystemObject to simply access the data directly from the text files without using access. Using the FSO you can read the name of the file EX: store123.txt - and output this on the page. This would be very effective in solving the problem of not being able to get the store name - if it is in the filename of the file - asp can grab this for you and display it. You could also use it to read the contents of the text file line by line, checking to see what the status of the store is (backup or what have you) - and output the storename and status of the store.

For instance...

Store1: ALERT! USING BACKUP!
Store2: Nominal
Store3: Nominal
Store4: Nominal
Store5: Nominal
Store6: Nominal

Let me know first if there is anything stopping you from doing this, and if there isn't, i will write a script for you to accomplish this task. Just be sure to send me an example of one of these text files over PM if you can, so I know what I am working with.

Secondly, if you wish to use the function mary created instead, below is the ASP version of it.

Function appendToTable(tblName)
    sSQL = "SELECT * FROM " & tblName
    Set rs = Server.CreateObject("ADODB.RecordSet") 
    rs.Open sSQL,cnn,3,3 ' cnn is, of course, the connection to your database.
    ' this will append each record from the passed table name to the tmpTable
    Do Until rs.EOF
        ' Each field name will have to be specified.  
        ' For the purposes of examples I am just going to use 3 and show
        ' how to pass different value types.  The first is a String, the second 
        ' a date and the third a number.
        cnn.execute("INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
            "VALUES ('" & rs("FieldName1") & "', #" & rs("FieldName1") & "#, " & rs("FieldName1") & ")")
         rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
End Function
 

[mbatestblrock]

Hello, mbatestblrock. I am one of the ASP Experts Mary spoke of above.

I have read through this entire thread, and perhaps I've missed something here because that was quite a bit to read; so if i have missed something please excuse my ignorance. The first question I have for you is:

Why are you transferring the data from the text files to access? You are effectively transferring one data store to another.

If there is no "technical" reason why you're moving the data from the text fles to access, I would use ASP's FileSystemObject to simply access the data directly from the text files without using access. Using the FSO you can read the name of the file EX: store123.txt - and output this on the page. This would be very effective in solving the problem of not being able to get the store name - if it is in the filename of the file - asp can grab this for you and display it. You could also use it to read the contents of the text file line by line, checking to see what the status of the store is (backup or what have you) - and output the storename and status of the store.

For instance...

Store1: ALERT! USING BACKUP!
Store2: Nominal
Store3: Nominal
Store4: Nominal
Store5: Nominal
Store6: Nominal

Let me know first if there is anything stopping you from doing this, and if there isn't, i will write a script for you to accomplish this task. Just be sure to send me an example of one of these text files over PM if you can, so I know what I am working with.

Secondly, if you wish to use the function mary created instead, below is the ASP version of it.

Function appendToTable(tblName)
    sSQL = "SELECT * FROM " & tblName
    Set rs = Server.CreateObject("ADODB.RecordSet") 
    rs.Open sSQL,cnn,3,3 ' cnn is, of course, the connection to your database.
    ' this will append each record from the passed table name to the tmpTable
    Do Until rs.EOF
        ' Each field name will have to be specified.  
        ' For the purposes of examples I am just going to use 3 and show
        ' how to pass different value types.  The first is a String, the second 
        ' a date and the third a number.
        cnn.execute("INSERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
            "VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")")
         rs.MoveNext
    Loop
 
    rs.Close
    Set rs = Nothing
 
End Function
 

wow thanks a bunch for all of this, and I apologize you had to read through all that JUNK I posted!

Hopefully MY ignorance did not give you a headache.

To answer your question, there is nothing stopping me from doing the asp way you had suggested. As long as I can pull from the files at the launch of the web page I do not care how it is accomplished. This just the only I knew it could be done, or couldnt be done, hah.

I posted a few of these files here

http://www.mykesdesigns.com/tblrock1...terminfo22.txt
http://www.mykesdesigns.com/tblrock1...terminfo24.txt
http://www.mykesdesigns.com/tblrock1...terminfo25.txt

and there are exactly 58 of the files. all named "terminfo#.txt" Where number is of course the store number.

the only thing I want output to the web page are the ones that are "redflagged" or in the wrong.

and that criteria is if Terminal 2 is NOT set as back up, or if any other Terminal BUT number two is set to backup.
and to make matters a little more difficult.. All of these stores will have terminal 2,3,4,5 what they are named after the number will always be different,

IE
05=#5/DT-OT in one file
and
05=#5/CTR in another file.

Now there will always be, like I said, a 02,03,04,05 but in some cases there could be anywhere up ..20. and anyone of those 20 could be set as backup...

I hope this making sense. for instance
http://www.mykesdesigns.com/tblrock1...erminfo171.txt

has a 6 and 7...

And then the output of the page needs to say something like
"store 22 has terminal 3 set as backup"
"store 171 has terminal 4 set as backup"
"store 69 has no backup!"

etc



Let me know if you need to know anything else, ill gladly give you any answers you need.

Thanks a million!

[markrawlingson]

Sounds good, I can provide you with a pretty decent solution, give me a couple of hours

Sincerely,
Mark

[mbatestblrock]

Sounds good, I can provide you with a pretty decent solution, give me a couple of hours

Sincerely,
Mark

I cant even thank you enough!

Looking forward to it!

[markrawlingson]

<%
'Replace this value with the path to the folder where the files are kept
sFolder = "C:\folder\folder\folder\"
'Open the File System Object
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( Server.MapPath( sFolder ) )
 
'Loop through each file in the folder
For Each File In oFolder.Files
    'Open the text file.
    Set oText = oFSO.OpenTextFile(File)
    'Skip the first line because it's just column delimited garbage and contains no information we care about as far as this code goes
    oText.SkipLine
    'Read each line there-after of the file
    Do Until oText.AtEndOfStream
        'Split the data on the line into an array so we can manipulate the data easier.
        aTerminal = Split(oText.ReadLine, ",")
        'If we're on line 2, we want to make sure this terminal is set to backup
        If oText.Line = 2 Then
            'Make sure line 2, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
            If InStr(Trim(LCase(aTerminal(1))), LCase("Backup")) <= 0 Then
                Response.Write "NOTE! " & Replace(File.Name,".txt","") & " does not have " & aTerminal(0) & " as backup!<br/>"
                bError = True
            End If
        'Else, if we are on any other line
        Else
            'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
            If InStr(Trim(LCase(aTerminal(1))), LCase("Backup")) > 0 Then
                Response.Write "NOTE! " & Replace(File.Name,".txt","") & " has " & aTerminal(0) & " as backup!<br/>"
                bError = True
            End If
        End If
    Loop
    oText.Close
    Set oText = Nothing
Next
 
oFolder.Close
Set oFolder = Nothing
oFSO.Close
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% End If %>
</form>
</body>
</html>
 

Note that I've included a button at the bottom of the page to generate a report. If you'd like, this button could be used to slap all of the errors found within the text files into 1 single text file of only errors. Then, you could simply send this text file to whomever to inform them that there are errors. The script can even send an email directly to your superior or whoever so you don't have to send an email yourself.

Let the computer do all the work for you I say, Unless this is your only position at this job.. then you're kind of putting yourself out of a job and well, that isn't a good idea. haha

So, with that said, simply create a new blank text file - name it TerminalErrors.asp (or whatever, just make sure the .asp extension is there) and save it to an asp-capable directory.

Note also that I have NOT tested this code - If by some act of God this works first try, the way it is supposed to with no errors - you are one lucky guy. If there are problems with it let us know, and we can take a look and fix them. I, unfortunately, have just been given a pile of work to do - I will test it later for you to make sure it functions effectively.

Sincerely,
Mark

[mbatestblrock]

<%
'Replace this value with the path to the folder where the files are kept
sFolder = "C:\folder\folder\folder\"
'Open the File System Object
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( Server.MapPath( sFolder ) )

'Loop through each file in the folder
For Each File In oFolder.Files
'Open the text file.
Set oText = oFSO.OpenTextFile(File)
'Skip the first line because it's just column delimited garbage and contains no information we care about as far as this code goes
oText.SkipLine
'Read each line there-after of the file
Do Until oText.AtEndOfStream
'Split the data on the line into an array so we can manipulate the data easier.
aTerminal = Split(oText.ReadLine, ",")
'If we're on line 2, we want to make sure this terminal is set to backup
If oText.Line = 2 Then
'Make sure line 2, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
If InStr(Trim(LCase(aTerminal(1))), LCase("Backup")) <= 0 Then
Response.Write "NOTE! " & Replace(File.Name,".txt","") & " does not have " & aTerminal(0) & " as backup!<br/>"
bError = True
End If
'Else, if we are on any other line
Else
'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
If InStr(Trim(LCase(aTerminal(1))), LCase("Backup")) > 0 Then
Response.Write "NOTE! " & Replace(File.Name,".txt","") & " does not have " & aTerminal(0) & " as workstation!<br/>"
bError = True
End If
End If
Loop
oText.Close
Set oText = Nothing
Next

oFolder.Close
Set oFolder = Nothing
oFSO.Close
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% End If %>
</form>
</body>
</html>


Note that I've included a button at the bottom of the page to generate a report. If you'd like, this button could be used to slap all of the errors found within the text files into 1 single text file of only errors. Then, you could simply send this text file to whomever to inform them that there are errors. The script can even send an email directly to your superior or whoever so you don't have to send an email yourself.

Let the computer do all the work for you I say, Unless this is your only position at this job.. then you're kind of putting yourself out of a job and well, that isn't a good idea. haha

So, with that said, simply create a new blank text file - name it TerminalErrors.asp (or whatever, just make sure the .asp extension is there) and save it to an asp-capable directory.

Note also that I have NOT tested this code - If by some act of God this works first try, the way it is supposed to with no errors - you are one lucky guy. If there are problems with it let us know, and we can take a look and fix them. I, unfortunately, have just been given a pile of work to do - I will test it later for you to make sure it functions effectively.

Sincerely,
Mark

I am trying this right now...

[mbatestblrock]

I am trying this right now...

okay, so I tried this two ways. One by copying exactlly what you gave me.

And when I pasted it into notepad it gave me #'s everywhere. and it didnt work, I got this.

Microsoft VBScript compilation error '800a0400'

Expected statement

/terminalerrors.asp, line 3

#
^



----
i thought maybe all the #'s were throwing it off. so I pasted it with no #'s

and get this...

Server.MapPath() error 'ASP 0172 : 80004005'

Invalid Path

/terminalerrors.asp, line 7

The Path parameter for the MapPath method must be a virtual path. A physical path was used.


------
I am not sure if I should copy and paste the ASP code in your post or the straight text in the reply.

thank you for helping with your busy schedule!

[markrawlingson]

No problem, I figured there would be errors - Like i said if it worked first try you're one lucky man. This means you're not lucky, today is a day to avoid the casino haha.

I just have to wrap up my workload here and then I will test this out for you. The other ASP Experts have been notified of this thread and asked if they wouldn;t mind giving a hand as well, so if I don't get around to it for several hours perhaps they can about getting the code I posted to work.

Sincerely,
Mark

[mbatestblrock]

No problem, I figured there would be errors - Like i said if it worked first try you're one lucky man. This means you're not lucky, today is a day to avoid the casino haha.

I just have to wrap up my workload here and then I will test this out for you. The other ASP Experts have been notified of this thread and asked if they wouldn;t mind giving a hand as well, so if I don't get around to it for several hours perhaps they can about getting the code I posted to work.

Sincerely,
Mark

Ill be sure to stay away from the casinos tonight!

You all are good people for helping out with this. I hope we can get this licked!

[MMcCarthy]

Ill be sure to stay away from the casinos tonight!

You all are good people for helping out with this. I hope we can get this licked!

Paste the straight text from the reply rather than the ASP code from the post.

[mbatestblrock]

Paste the straight text from the reply rather than the ASP code from the post.

okie dokie, i get the error stated above

Server.MapPath() error 'ASP 0172 : 80004005'

Invalid Path

/terminalerrors.asp, line 7

The Path parameter for the MapPath method must be a virtual path. A physical path was used.


ill use the straight text from now on

[markrawlingson]

Ok, I'm back - sorry that took so long. I had to make a billion changes to a customer's site.

Can you send me, by way of email, about 5 of those text files? Doesn't matter which ones, I just want to run this script on my test box and see if I can work all the kinks out of it for you. I'll send you a PM with my address.

Sincerely,
Mark

[NeoPa]

You're impressing Mark.
Great attitude :)

[markrawlingson]

Thank you Neo :)

mbatestblrock,

Here you go! All the errors are worked out. I've tested it quite thoroughly and it works great.

So just to recap - if line #2, column #2 (ASP is reading this as line 4 though, for some reason...) is NOT "Backup", it will write an error to the page. If any other line, column #2 in the file IS "Backup", it will write an error to the page.

<%
'Replace this value with the path to the folder where the files are kept
sFolder = "D:\Inetpub\wwwroot\netTest\Test\Reports"
bError = False
'Open the File System Object
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( sFolder )
Function Val( sValuePrivate )
    Set oRegExp = New RegExp
    oRegExp.Pattern = "[^0123456789\.]"
    oRegExp.IgnoreCase = True
    oRegExp.Global = True
    Val = Abs( oRegExp.Replace( "0" & sValuePrivate, "" ) )
    Set oRegExp = Nothing
End Function
 
'Loop through each file in the folder
For Each File In oFolder.Files
    'Open the text file.
    Set oText = oFSO.OpenTextFile(File)
    'Skip the first 2 lines because it's just column delimited garbage and contains no information we care about as far as this code goes
    oText.SkipLine
    oText.SkipLine
    'Read each line there-after of the file
    Do Until oText.AtEndOfStream
        'Split the data on the line into an array so we can manipulate the data easier.
        aTerminal = Split(oText.ReadLine, ",")
        'If we're on line 2, we want to make sure this terminal is set to backup
        If Val(oText.Line) = 4 Then
            'Make sure line 4, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
            If Trim(aTerminal(1)) <> "Backup" Then
                Response.Write "<font face='Verdana' size='1' color='red'>NOTE! " & Replace(File.Name,".txt","") & " does not have " & aTerminal(0) & " as    backup!</font><br/>"
                bError = True
            End If
        'Else, if we are on any other line
        Else
            'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
            If Trim(aTerminal(1)) = "Backup" Then
                Response.Write "NOTE! " & Replace(File.Name,".txt","") & " has " & aTerminal(0) & " as Backup!<br/>"
                bError = True
            End If
        End If
    Loop
    oText.Close
    Set oText = Nothing
Next
 
Set oFolder = Nothing
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError = True Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% Else %>
<font face="arial" size="1" color="green">The system is nominal. No errors to report!</font>
<% End If %>
</form>
</body>
</html>
 

It is important to note that the "Generate a Report" button is merely cosmetic at this point. I was going to add in some code to have it generate a report and email it to you but it dawned on me that I don't know what email component you have installed on your system, probably CDOSYS would work fine but i didn't want to take the chance of the page throwing more errors.

All you have to do is replace the value of the sFolder variable with the physical FOLDER path to where the text files are kept on your server. There are no other files in this folder I hope...

Let me know if there are any problems with it.

Sincerely,
Mark

[mbatestblrock]

I am super stoked to give this its run tomorrow.

I will do it first thing in the morning, and let you all know how it turned out!!

[MMcCarthy]

trying to post to get the straight text!

Click reply on the original post from Mark with the code.

[mbatestblrock]

well If I paste exactly what you gave in the CODE I get this error

Microsoft VBScript runtime error '800a004c'

Path not found

/terminalerrors.asp, line 8



I was trying to do a reply, so I could get the straight text and it didnt work, as you can see my blank posts???

[mbatestblrock]

Click reply on the original post from Mark with the code.

thats what I am doing, for some reason it is just a blank post??

[MMcCarthy]

Here is the code in straight text. Let me know when you've posted it and I'll delete it again.

Mary

[code deleted]

[mbatestblrock]

got it. thank you for doing that!

[MMcCarthy]

got it. thank you for doing that!

No problem, I'm going to delete the code now.

[markrawlingson]

And if you are getting "Path not found" error on line 8 that means that you haven't specified a correct folder path to the folder where the files are kept.

Sincerely,
Mark

[mbatestblrock]

dangit, I still have the same error


Microsoft VBScript runtime error '800a004c'

Path not found

/terminalerrors.asp, line 8

[mbatestblrock]

It most certainly is the correct path though. I am in file explorer and I am copy what is in the address bar. I even tried making a new one right where the .asp file was. still didnt work

[markrawlingson]

What is the path?

Mark

[mbatestblrock]

What is the path?

Mark

F:\IMPORT\manpoll\FTP\misc


It is a mapped network drive I dont think that should matter, but I thought I would throw it out there.

[markrawlingson]

It seems like an IIS security/authentication issue - IIS can't see the mapped drive.

I've never accessed a mapped drive myself while using the FSO so I wouldn't be able to give you any competant advice there - however I am reading up on the subject - i will see what I can find, Unless of course someone knows the answer already ?

Sincerely,
Mark

[mbatestblrock]

It seems like an IIS security/authentication issue - IIS can't see the mapped drive.

I've never accessed a mapped drive myself while using the FSO so I wouldn't be able to give you any competant advice there - however I am reading up on the subject - i will see what I can find, Unless of course someone knows the answer already ?

Sincerely,
Mark

for the time being, i set up a folder in my root director to test this out, and sure enough it works beautifully!

I just hope we can get this network drive thing figured out, I am searching for it as well.

[markrawlingson]

It would certainly be good if we can, I've read a couple work arounds on the web regarding setting proper permissions to the mapped drive inside IIS. Not sure about it though.

Even if we can't get that figured out, if you have to move the contents of the folder from the mapped drive to a physical folder on the server itself 5 times a day, that's a whole lot easier than opening each file and reading them yourself line by line.

I'd like it to automate the procedure 100% for you, but if we can't get this last thing figured out at least we've made it 95% automated :)

I'm glad it worked for you somewhat though!

I'm wondering if Mary could ask some of the IIS experts to come take a look at this thread for you. I'm no IIS expert and this issue seems to be in regard to how IIS is looking at the mapped drive. I'll send her a PM and ask her if she wouldn't mind doing that.

Sincerely,
Mark

[mbatestblrock]

that sounds perfect! thank you for all the help you've provided me, I am searching on this issue as well, but having an extremely rough time trying to even figure out a search phrase..

Hopefully the IIS gurus can lend a hand!

I am running verison 6 if that helps.

again i dont even know how much I could thank everyone for this. It kinda took a total 180 from where it started, but we got it!

Ill keep checking for the iis updates.


-mike

[RedSon]

that sounds perfect! thank you for all the help you've provided me, I am searching on this issue as well, but having an extremely rough time trying to even figure out a search phrase..

Hopefully the IIS gurus can lend a hand!

I am running verison 6 if that helps.

again i dont even know how much I could thank everyone for this. It kinda took a total 180 from where it started, but we got it!

Ill keep checking for the iis updates.


-mike

I'm no IIS expert but I would guess that the IIS user does not have permissions on that network share. See if opening up the permissions temporarily to allow read/write access to everyone might help the situation.

[mbatestblrock]

Okay, I hate to be pressing my luck here.. but heres what I am running into..

I copied all of the terminfos into that directory..

The text files aren't always laid out the same, and suppose this creates a problem.

I mean they are LAID out the same, they just are in a slightly different order..

For instance the terminal numbers are sorted the opposite way, starting with 6 and ending with 2. so the script comes back with things like #3 is not set as backup. and number two is set as backup -as if it was an error.

I think you get the idea.. I was hoping there was a way to fix this. I hope it makes this makes sense I am on the phone and trying to help someone with hardware and typing this at the same time!


let me know if you need more info. I sent you (mark) an example of one of those certain files.

[mbatestblrock]

I'm no IIS expert but I would guess that the IIS user does not have permissions on that network share. See if opening up the permissions temporarily to allow read/write access to everyone might help the situation.

I am going to give this a shot in a few minutes! if that does work, (i am no IIS expert either) how would I find the system name or the user name for the IIS server so I am not leaving this open to everyone?

[RedSon]

I am going to give this a shot in a few minutes! if that does work, (i am no IIS expert either) how would I find the system name or the user name for the IIS server so I am not leaving this open to everyone?

I think when you install IIS it creates some system user accounts. Try looking for user names with IIS in the name. Probably a quick MSDN search would reveal this answer too.

[markrawlingson]

Okay, I hate to be pressing my luck here.. but heres what I am running into..

I copied all of the terminfos into that directory..

The text files aren't always laid out the same, and suppose this creates a problem.

I mean they are LAID out the same, they just are in a slightly different order..

For instance the terminal numbers are sorted the opposite way, starting with 6 and ending with 2. so the script comes back with things like #3 is not set as backup. and number two is set as backup -as if it was an error.

I think you get the idea.. I was hoping there was a way to fix this. I hope it makes this makes sense I am on the phone and trying to help someone with hardware and typing this at the same time!


let me know if you need more info. I sent you (mark) an example of one of those certain files.

Sorry I was under the impression that the first terminal that is displayed is always #2.

Here's a fix...

If Val(oText.Line) = 4 Then
 

Find the line above and replace it with..

If InStr(Left(Trim(aTerminal(0)),2), "02") > 0 Then
 

This will look at the terminal name to make sure that the first two characters are 02 before checking whether it's status should be "Backup" - this way it doesn't matter which line it is on.

Of course, I'm assuming that it's always terminal 02 that is supposed to be set as "Backup" and that its name will always start with 02.

Sincerely,
Mark

[markrawlingson]

I think when you install IIS it creates some system user accounts. Try looking for user names with IIS in the name. Probably a quick MSDN search would reveal this answer too.

Hmmm, I'm wondering if you were to go to... My Computer --> Right click the mapped network drive (I think you said it was F:\) --> Properties --> Security Tab --> You should see the groups or usernames --> Click Everyone --> Make sure the checkbox under 'Allow' for 'Read' is checked off.

Or you may even want to just apply this to IIS's annonymous user account which is (usually) called IUSER_COMPUTERNAME - but may appear in this list as Administrator (COMPUTERNAME\Administrators) - Or even the System username.

You can find out who the "Owner" of the F:\ Drive is by clicking the advanced button at the bottom and then clicking on the "Owner" tab. It should say "Current owner of this item:"

If this doesn't work - try giving every account listed full permissions and see if the script works then. If it doesn't, just revert it back to the way it was. If it does - you know at least something worked and it's a trial and error sort of deal going through each user account and updating their permissions for the drive to 'Read' (for security purposes) until eventually the script fails - then you know which account needs permissions for this drive.

Sincerely,
Mark

[mbatestblrock]

can someone post the straight text code again, I am idiot and did not save a file with it, and I goofed it up by trying to edit it.. I cant figure out why I cant reply to the post.. but it doesnt work for me

[RedSon]

Here is another thing to try: when you map a drive you are basically making a short cut from \\domain\foo\bar to X:\. So instead of doing X:\MyFileName try doing something like \\domain\foo\bar\MyFileName. Or alternatively you can treat the file location as a URL, the various Microsoft technologies should be able to parse the \\*\*\ notation.

[markrawlingson]

Here you go.

<%
'Replace this value with the path to the folder where the files are kept
sFolder = "D:\Inetpub\wwwroot\netTest\Test\Reports"
bError = False
'Open the File System Object
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( sFolder )
Function Val( sValuePrivate )
Set oRegExp = New RegExp
oRegExp.Pattern = "[^0123456789\.]"
oRegExp.IgnoreCase = True
oRegExp.Global = True
Val = Abs( oRegExp.Replace( "0" & sValuePrivate, "" ) )
Set oRegExp = Nothing
End Function

'Loop through each file in the folder
For Each File In oFolder.Files
'Open the text file.
Set oText = oFSO.OpenTextFile(File)
'Skip the first 2 lines because it's just column delimited garbage and contains no information we care about as far as this code goes
oText.SkipLine
oText.SkipLine
'Read each line there-after of the file
Do Until oText.AtEndOfStream
'Split the data on the line into an array so we can manipulate the data easier.
aTerminal = Split(oText.ReadLine, ",")
'If we're on line 2, we want to make sure this terminal is set to backup
If InStr(Len(Trim(aTerminal(0)),2), "02") > 0 Then
'Make sure line 4, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
If Trim(aTerminal(1)) <> "Backup" Then
Response.Write "<font face='Verdana' size='1' color='red'>NOTE! " & Replace(File.Name,".txt","") & " does not have " & aTerminal(0) & " as backup!</font><br/>"
bError = True
End If
'Else, if we are on any other line
Else
'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
If Trim(aTerminal(1)) = "Backup" Then
Response.Write "NOTE! " & Replace(File.Name,".txt","") & " has " & aTerminal(0) & " as Backup!<br/>"
bError = True
End If
End If
Loop
oText.Close
Set oText = Nothing
Next

Set oFolder = Nothing
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError = True Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% Else %>
<font face="arial" size="1" color="green">The system is nominal. No errors to report!</font>
<% End If %>
</form>
</body>
</html>

That should do the trick. Let me know when you're done, i'll see if I can remove the text from my post - if not i'll get a mod or an admin to do it.

Sincerely,
Mark

[markrawlingson]

Here is another thing to try: when you map a drive you are basically making a short cut from \\domain\foo\bar to X:\. So instead of doing X:\MyFileName try doing something like \\domain\foo\bar\MyFileName. Or alternatively you can treat the file location as a URL, the various Microsoft technologies should be able to parse the \\*\*\ notation.

I thought about that and looked it up a couple hours ago and the concensus seems to be that you can't do it because of the way that the file system object works. I believe that will throw a "Invalid character" error.

Now that I think about it though I guess you could use the XML DOM to SCRAPE the contents of each of the files if you know the http://www.domain.com/foo/bar type location of the directory. You could scrape the contents, save it to a text file on the server's hard drive, and then point the FSO to that physical path.

But something tells me that these files arern't on the net. :P

Sincerely,
Mark