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!

[RedSon]

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

URL notation and the file's existence on a web server available to the internet are completely separate things. As long as you have a domain controller or active directory server in your case you can still access files on your intranet using URLs. My company's internal servers are not available to the internet but i can still get to certain things using URLs.

[NeoPa]

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.

This all gets very messy!
You're sort of on the right lines RedSon, but remember that the whole idea of web sites is that thet're open to the world and, when accessing a site, your account tokens are not used (typically). The account IUSER_{MachineName} is used instead for ALL users. Typically this account does not have access rights to any of the network shares. It will be a machhine account and not even on the network. This is something that makes web development of standard internal systems such a head-ache.

Well, we had the problem too somewhen ago, so I HAD to figure out a way of doing it (you'll be pleased to hear). My solution is in IIS and I am certainly no expert there. I just had to get in and get my hands dirty once.

In IIS Manager, select the site you want this to work on and add a new Virtual Directory. I don't remember exactly the steps I used, but the "Virtual Directory" tab should be set up (as a starting point at least, you can tweak when working to your own preferences) :

1. A share located on another computer
2. Network Directory = UNC of the share (\\Server\Share).
3. You must have a "special" account set up for the Connect As. Click on the button to use this account. It is a BAD idea to try to set up the IUSR_... account for this - apart from blowing your whole security system.
4. Script Source Access is the only checkbox not set.
5. Execute Permissions = None.

You may need to get a Domain Administrator to set this up for you. You will need to be able to assure them that your code logic fully handles security in such a way as to ensure that opening up this hole will only grant access to your system and there are no loopholes for anyone to slip through. Security is not a trivial aspect and what you're trying to do here is, by it's very nature, adding to its complexity (bad thing). Keep the variation from the norm to an absolute minimum (otherwise resolving problems later will be very difficult).

[NeoPa]

URL notation and the file's existence on a web server available to the internet are completely separate things. As long as you have a domain controller or active directory server in your case you can still access files on your intranet using URLs. My company's internal servers are not available to the internet but i can still get to certain things using URLs.

Except in situations where the default security is mucked up (people do because they find the whole concept so confusing) these files are only available to a user via the windows based interfaces. The web interface is generally set as a "current machine only" system. This is supporting what you're saying RedSon if I understand you correctly ;)

[mbatestblrock]

This all gets very messy!
You're sort of on the right lines RedSon, but remember that the whole idea of web sites is that thet're open to the world and, when accessing a site, your account tokens are not used (typically). The account IUSER_{MachineName} is used instead for ALL users. Typically this account does not have access rights to any of the network shares. It will be a machhine account and not even on the network. This is something that makes web development of standard internal systems such a head-ache.

Well, we had the problem too somewhen ago, so I HAD to figure out a way of doing it (you'll be pleased to hear). My solution is in IIS and I am certainly no expert there. I just had to get in and get my hands dirty once.

In IIS Manager, select the site you want this to work on and add a new Virtual Directory. I don't remember exactly the steps I used, but the "Virtual Directory" tab should be set up (as a starting point at least, you can tweak when working to your own preferences) :

1. A share located on another computer
2. Network Directory = UNC of the share (\\Server\Share).
3. You must have a "special" account set up for the Connect As. Click on the button to use this account. It is a BAD idea to try to set up the IUSR_... account for this - apart from blowing your whole security system.
4. Script Source Access is the only checkbox not set.
5. Execute Permissions = None.

You may need to get a Domain Administrator to set this up for you. You will need to be able to assure them that your code logic fully handles security in such a way as to ensure that opening up this hole will only grant access to your system and there are no loopholes for anyone to slip through. Security is not a trivial aspect and what you're trying to do here is, by it's very nature, adding to its complexity (bad thing). Keep the variation from the norm to an absolute minimum (otherwise resolving problems later will be very difficult).

That seemed to do the trick.. only, there is other files inside this directory... I guess I should have thought of that before. It throws off the result.. these files are brought in via ftp and the script is run on a scheduler to do it every hour.. I simply added the destination to my folder in my root IIS directory that will now hold these text files. So problem is solved, they are being copied in there every hour now.


Everything seems to be working just great.. This could have never happened without all of your guys' help. So seriously thank you

this had made me realize I should really learn ASP. I have a general question.. Could this same stuff be done using php? I have told myself I REALLY need to learn one of these languages.. any input on which one you guys feel is the better of the two?

[markrawlingson]

Except in situations where the default security is mucked up (people do because they find the whole concept so confusing) these files are only available to a user via the windows based interfaces. The web interface is generally set as a "current machine only" system. This is supporting what you're saying RedSon if I understand you correctly ;)

Well, I just did some testing and RedSon might be right about the \\domain\\foo\bar was of referencing it through the FSO - but i'm not sure because of the way I did this.. so i'll explain...

I just mapped a folder on my server's drive to a network drive. So basically C:\whatever\whatever is now mapped as X:\domain\folder\whatever\whatever OR \\domain\folder\whatever\whatever

I tried referencing this drive with a physical path ("X:\domain\folder\whatever\whatever") and it gave me Path Not Found.

I then tried what RedSon suggested using its network path ("\\domain\folder\whatever\whatever") and the script works.

What I'm unsure about is whether it works because it's a drive on the server mapped as a network drive to the server or not (it's basically mapped to itself). I think it should work - either way it's still a network drive though, right?

I had also been under the impression that the FileSystemObject in asp can only be passed a physical path which has to start with a drive letter - but this obviously proves that theory wrong - as well as my numerous findings on google related to the subject.

Sincerely,
Mark

[markrawlingson]

That seemed to do the trick.. only, there is other files inside this directory... I guess I should have thought of that before. It throws off the result.. these files are brought in via ftp and the script is run on a scheduler to do it every hour.. I simply added the destination to my folder in my root IIS directory that will now hold these text files. So problem is solved, they are being copied in there every hour now.


Everything seems to be working just great.. This could have never happened without all of your guys' help. So seriously thank you

this had made me realize I should really learn ASP. I have a general question.. Could this same stuff be done using php? I have told myself I REALLY need to learn one of these languages.. any input on which one you guys feel is the better of the two?

Ok well I guess that answers the questions I just posted. haha! Thanks for sharing Mike. I'm glad it's working for you!

Ah, it's probably not a good idea to ask people which is better - asp/php. Generally you start fights that way haha.

Seriously though, there's always been quabbles between developers about whether PHP is better than ASP. The answer you'll get to that question generally depends on who you ask, but it's fairly widely accepted that PHP is superior. Almost all PHP developers I've talk to think that ASP has an 'ugly' syntax - I tend to disagree with that for many reasons, but that's a whole different story.

Part of the problem in answering the question of whether asp is better than php or visa versa is that almost no one knows both - generally either you know ASP, or you know PHP - so I've found that most people comment on either language more or less from the standpoint of ignorance, bashing a language they know nothing about. As far as I am concerned I will admit that when it comes to PHP my knowledge is indeed limited - I am in the process actually of learning it. So far, I'd say I'm quite enjoying a few aspects of PHP which eliminate annoying syntactical problems within ASP - but at the same time it also generates a few problems itself which you wouldn't get with ASP. For instance, ASP is not a Case-Sensitive language, PHP is - or at least the variables are. EG: $sName is not the same as $sname - and of course, you have to end each statement with a semicolon or the whole page bombs on you - in ASP you don't. I'm a little nitpicky when it comes to things like that.

Generally there are more cons to ASP than there are for PHP.

For instance, try running ASP on Linux, or on any other operating system other than Windows for that matter.

A LAMP (Linux Apache MySQL PHP) setup is much faster than a windows based environment, and is a completely open source stack - Free, and constantly improved with bug fixes and other changes to make development easier, and faster.

Sincerely,
Mark

[mbatestblrock]

Ok well I guess that answers the questions I just posted. haha! Thanks for sharing Mike. I'm glad it's working for you!

Ah, it's probably not a good idea to ask people which is better - asp/php. Generally you start fights that way haha.

Seriously though, there's always been quabbles between developers about whether PHP is better than ASP. The answer you'll get to that question generally depends on who you ask, but it's fairly widely accepted that PHP is superior. Almost all PHP developers I've talk to think that ASP has an 'ugly' syntax - I tend to disagree with that for many reasons, but that's a whole different story.

Part of the problem in answering the question of whether asp is better than php or visa versa is that almost no one knows both - generally either you know ASP, or you know PHP - so I've found that most people comment on either language more or less from the standpoint of ignorance, bashing a language they know nothing about. As far as I am concerned I will admit that when it comes to PHP my knowledge is indeed limited - I am in the process actually of learning it. So far, I'd say I'm quite enjoying a few aspects of PHP which eliminate annoying syntactical problems within ASP - but at the same time it also generates a few problems itself which you wouldn't get with ASP. For instance, ASP is not a Case-Sensitive language, PHP is - or at least the variables are. EG: $sName is not the same as $sname

Generally there are more cons to ASP than there are for PHP.

For instance, try running ASP on Linux, or on any other operating system other than Windows for that matter.

That all makes really good sense. So what you had wrote for me in ASP, the same outcome could have been done writing a scrip t in php?

[markrawlingson]

It most certainly can. As one PHP developer once put it to me.. "That and about a billion other things that ASP couldn't hope to do"

Sincerely,
Mark

[mbatestblrock]

As one PHP developer once put it to me.. "That and about a billion other things that ASP couldn't hope to do"

hahahaha.

thanks again for everything guys! you rock.

[RedSon]

Except in situations where the default security is mucked up (people do because they find the whole concept so confusing) these files are only available to a user via the windows based interfaces. The web interface is generally set as a "current machine only" system. This is supporting what you're saying RedSon if I understand you correctly ;)

Yea, so I apologize. Normally when I am trying to work something out, I open up everything so that I have no problems other then the problems that my own code produces. Then I go back later and start locking stuff down and testing the results. So if this is something that is going to be used on a live server then going about it in a more secure fashion would be best.

[MMcCarthy]

I just wanted to say thanks to everyone for participating in this thread.

Its nice to get these cross forum threads going occasionally (not too often mind - it did get a bit long) :)

Great work by everyone to a successful conclusion. Thats what I like to see.

Mary

[NeoPa]

Yea, so I apologize. Normally when I am trying to work something out, I open up everything so that I have no problems other then the problems that my own code produces. Then I go back later and start locking stuff down and testing the results. So if this is something that is going to be used on a live server then going about it in a more secure fashion would be best.

I'm not sure why the apology. Your input added to the whole. That's how I saw it :)

[mbatestblrock]

Thats how I saw it too!