How to Clear Hostname Variable

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Nicholas.Gerritz
Posts: 5
Last visit: Mon Jul 26, 2021 3:40 am

How to Clear Hostname Variable

Post by Nicholas.Gerritz »

Hi Support,

I have a Powershell GUI Script/App which does a SQLCMD query to a (hostname) DB table lets call it MyDB\INST1 and then pipes the result to an out-string, which is then set to a variable. It works perfectly well.

I have a problem however when I switch to a different environment (from within my GUI Script/App), and then run that same SQLCMD query on an ientical DB (that has the same hostname but different IP address and subnet) but the SQLCMD query runs on the first environment's DB host.

Is there a way for me to get my variables to forget what they were last run against, so that when I run them a second time, they are forced to do a fresh lookup, and therefore target the new hostname/ip pair, which will resolve in the hostsfile and target the correct IP address.

For some reason, no matter if I wrap the step in a function, or even just script the query straight into a button, it works the first time and then fails to work "correctly" the second time, and when I have switched to a different environment that has the same hostnames but different host IP addresse, the GUI Script/App refuses to target the new host/ipaddress. I have to use Hostnames to target the DB, since they are common in name, but i cant easily manage the IP address as a target, since there are hundreds and they often change in IP but not in name, so managing hostname as the target is FAR more practical to do.

BTW : My host file is updated each time with the new environments host/ip pairs and runs an "ipconfig /flushdns" step when the environment target is switched. Its the hostname/ip that seems to persist in the Powershell GUI somehow?

Any ideas?
Thanks in advance

jvierra
Posts: 14929
Last visit: Sat Sep 18, 2021 12:31 am
Answers: 14
Has voted: 3 times
Been upvoted: 11 times

Re: How to Clear Hostname Variable

Post by jvierra »

Sorry but it is impossible to know what you are asking. Without some idea of what variable you are asking about there is no way to suggest an answer.

WHat are teh "host/ip pairs" There is no such technical variable or thing. Without some clear idea of what you have and how you are proceeding then we cannot guess at anyting.

Nicholas.Gerritz
Posts: 5
Last visit: Mon Jul 26, 2021 3:40 am

Re: How to Clear Hostname Variable

Post by Nicholas.Gerritz »

Hi @jvierra thanks for the reply,

So I Have a variable that is called $textbox_return which I later then print to a textbox object $txtbox1 using
$txtbox1.text = $textbox_return

I set $textbox_return = Invoke-SQLCMD -query "select * from table where columnID = 27" -ServerInstance DBHost1\INST1 -Database MyDB -Username x -password y | Out-String

$txtbox1.text = $textbox_return

In this case DBHost1 is a hostname of my DB Server and my pc looks in my hostfile for DBHost1 IP to reach the server to run the query - which works just fine.

Then I have a button called $btn_ChangeEnvironment that does a Hostfile Change on my PC on a click event which works fine too,
this is verifiable using a ping to check confirm

$pingResult = ping DBHost1 -n 4 | out-string
$txtBox1.text = $pingResult (and this confirms the IP has changed after the hostfile updates from the $btn_ChangeEnvironment_Click event)

After the Environment is changed (ie: the host file is changed where DBHost1 now has a different IP address mapping in my hostfile)
I then run my original SQLCMD Query :

$textbox_return = Invoke-SQLCMD -query "select * from table where columnID = 27" -ServerInstance DBHost1\INST1 -Database MyDB -Username x -password y | Out-String

$txtbox1.text = $textbox_return

and print out the return string which shows that the query looked again at the 1st env DBHost1 and not the new env DBHost1, despite DBHost1 in my hostfile now being different interms of the IP address mapped to it in my hosts file.

I cant seem to get the Powershell script GUI APP to forget the context of DBHost1 when it is run more than once, and where DBHost1 is mapped to a new IP relitive to the changed Hostfile mappings effected by the step $btn_ChangeEnvironment_Click

Does that make any more sense?

Just to clarify with an example, when the GUI Script runs my host file is like this :

Environment 1 :
DBHost1 192.168.50.2
APPHost1 192.168.50.3
WebHost1 192.168.50.4

$pingResult returns "192.168.50.2"

After executing $btn_ChangeEnvironment_Click

Environment 2:
DBHost1 172.16.0.2
APPHost1 172.16.0.3
WebHost1 172.16.0.4

$pingResult returns "172.16.0.2"

jvierra
Posts: 14929
Last visit: Sat Sep 18, 2021 12:31 am
Answers: 14
Has voted: 3 times
Been upvoted: 11 times

Re: How to Clear Hostname Variable

Post by jvierra »

WHy do you have to change the hostname? WHy are you using SQLCMD in a GUI? Changing a hostname after starting an app will not change it foro the app unless you exit the app and restart it.

Overal I see that you have no programming experience and you are just guessing at how to do this. Normally we would just use a Net class to query a dataaabse. Also a database should only be queried by its own instance name. Doing otherwise will usually cause conflicts with the database instance.

Once a connection is made using a name then changing the IP will not remove the cached connection which will just be reused the next time you use the same name.

Of course all of this is also dependent on the installation and the configuration of you machine, network and database.

Nicholas.Gerritz
Posts: 5
Last visit: Mon Jul 26, 2021 3:40 am

Re: How to Clear Hostname Variable

Post by Nicholas.Gerritz »

Thanks for the Feedback Jverra, You are correct, I am not a developer and do not claim to have programming skills to the level you are suggesting.
I have however built a complex GUI Powershell Application that works very well, and serves the purpose of the organization that purchased the software for me, to assist me where my programming skills are not high, while still being able to leverage the GUI Studio tools to get some automation to my daily workflows. This was what they were sold the software on, and that is why I have it and why I use it. So im unsure why that is relevant here.

You ask me why am i using SQLCMD in a GUI?
The Gui provides the button on my GUI PS App and i build event/logic based on that button action.
We use SQL CMD to query table data that are Output to Grid Views (-ogv) which works just fine, so im unsure why that seems strange to you?

You ask why do we need to change the Hostname?
As I said, we DONT change the hostname, we have 167environements that each run a collection of servers(not on domain) that each have the same name in each environment. Including the DBs that have the same names and the instances that have the same names, they are essentially identical Environments that are provisioned for different teams, and since I administer these all, I have to switch between all the environments at any point of the day.
When switching between env1 and env2 we only change hosts files, and the Hostnames remain the same and the IP addresses only change.

The good news is, you have pointed at the core issue I have : "Once a connection is made using a (host?)name then changing the IP will not remove the cached connection which will just be reused the next time you use the same name."
Now THIS is what i need to find a solution for, as I tried to explain in my original question, How can i get the session to forget the Hostname/IP mapping once its been targeted and used the first time, so that the second time I target that hostname that has a different IP address, I can have a new lookup performed to my hostfile?

This is what i need to solve. Somewhere, somehow, this value is stored in memory or wherever, and i need to find out how to cleanup/forget/clear that stored value and force a new lookup. Solving this one single challenge, opens up a HUGE amount of capability we can have from this sofware from Sapien, relitive to our business needs and intermediate technical skillset.

Thanks for your feedback and Time - Lets try get to the bottom of this one fundemental core issue of the session locking the Hostname/IP pariing value.
Take care
Cheers

jvierra
Posts: 14929
Last visit: Sat Sep 18, 2021 12:31 am
Answers: 14
Has voted: 3 times
Been upvoted: 11 times

Re: How to Clear Hostname Variable

Post by jvierra »

Clearly you have little technical training in databases and networking so understanding the issue will require that you may need a consultant to help you.

PSS is not a replacement for technical training, it is a professional toolbox intended to be used by people trained in the technology.

Also your statement that all database instances have the same name is incorrect. Every instance in a network must have a unique name which is the PC name for the default instance and the pcname plus the local instance name combined with a slash.

The next issue is that using the "host" file to identify resources has been deprecated and should not be done and this has been true for the last 20 years. This tells me that there are likely other misunderstandings in the deployment and configuration of your network.

You really should hire a trained and certified consultant to help you with this. We cannot provide deep consulting services in a forum for basic technical questions.

jvierra
Posts: 14929
Last visit: Sat Sep 18, 2021 12:31 am
Answers: 14
Has voted: 3 times
Been upvoted: 11 times

Re: How to Clear Hostname Variable

Post by jvierra »

One other note isa that the "hosts" file will not override the DNS in most situations. Trying to override DNS with a host file will cause all manner of odd conflicts.

If you have to switch between networks then the DNS on the new network will give you the cor4rect name translation without any need to alter the machine.

Again there is no way to have multiple hosts with the same name on the same network and use the NETBIOS name convernting. If the networks are subnetworks then use the FQDN of the host.

Again, learning networking or hiring a trained consultant will help you sort this out.

Nicholas.Gerritz
Posts: 5
Last visit: Mon Jul 26, 2021 3:40 am

Re: How to Clear Hostname Variable

Post by Nicholas.Gerritz »

OK let me explain what an environment is.

It is an isolated network running in a workgroup type of configuration, hosts are stored within a canister running on cloud infrustructure.
We have well over 100 environments, that are all identicle in structure host configuration, since they are all provisioned from a GoldMaster copy.

They all have 15 servers they all have the same name and they all have the same content when provisioned from the Gold Master copy.
The only difference is that they all have thier own unique IP Address configurations, relitive to which subnet they are in, for their canistered vlans.
So perhaps it would be constructive here to consider that there are smart ways to run networks under the same roof using subnetted canistered vlans.

I didnt come here to be told the level of my technical skills. I assure you i am highly certified and with nearly two decades of commercial experience. If thats not good enough to qualify asking how to make powershell studio forget a hostname to IP address binding, when powershell itselff has no problem doing that same thing, then i guess i will never get to the bottom of the core issue here.

Powershell studio is a tool i use to make the GUI APP, and the function running targets a hostname/ip address and the Powershell APP is the one caching this and not releaseing that. So if you know how to make Powershell Studio GUI App unbind the cached Hostname to IP address, then my problem is solved. So since you are technically advanced, at least relitive to me, then please may i kindly lean on your advanced skillset to solve this one simple problem.

I appreciate your time and insight

jvierra
Posts: 14929
Last visit: Sat Sep 18, 2021 12:31 am
Answers: 14
Has voted: 3 times
Been upvoted: 11 times

Re: How to Clear Hostname Variable

Post by jvierra »

Clearly you are now adding many more layers of complexity to the issue which is why I suggest a consultant as you have left out the majority of your configuration until the last post. There is no way for me or anyone else to guess this. If you were network and database trained you would have given the full configuration up front as it explains the complexity of the environment.

I now understand why and how your addressing is being used. What is missing is how you are gaining cloud access and container selection.

If you have "ping" access to a host by IP then just substitute the IP in the SQL CMD and it will connect to the correct host if the host is pingable and the port is open. You can use the special tools in your SQLServer client installation to test the complete connection as a SQL connection. You can also use a specific connection alias for each remote system. With cloud containers the hosts file will not work depending on your local configuration.

The easiest way to start is at a PowerShell prompt and type a simple SQLCMD to see if it connects using the IP.

Code: Select all

$dbhost = '10.0.0.1'
Invoke-SQLCMD -query "select * from table where columnID = 27" -ServerInstance "$dbhost\INST1" -Database MyDB -Username x -password y
This effectively does the same as changing the host file but is safer and more reliable.

If it is a cloud container then you will have to troubleshoot the whole of the container to see if the SQL port is open and usable.

Nicholas.Gerritz
Posts: 5
Last visit: Mon Jul 26, 2021 3:40 am

Re: How to Clear Hostname Variable

Post by Nicholas.Gerritz »

also, i think it prudent to point out,

If i select env1 and then ping my webserver called "Webserver" i get ping output of 192.168.0.1
if i then select env2 and switch to it and my host file updates, and i click my button called Ping Webserver, it now returns 172.16.0.1
So this works, Same hostname, different IP Address and it is correctly returned / successfully.

When i target a SQLCMD query to MyDB1\INST1 on env1 my query returns the results of the table records i query / successfully
if i then select env2 and switch to it and my host file updates, and i click my button called query MyDB1, it returns the same table data from the previously selected env ie: env1 and not env2 despite the ping button returning a correct switch from MyDB1 in env1 to MyDB1 in env2.

So maybe you are onto something here, relitive to the MyDB1\INST1 binding being locked or cached to IPaddress from the first used env ie: env1
and when i select and switch to env2 and hosts file changes, the SQLCMD query to the MyDB1\INST1 does not return the data from the env2 that i have switched to.

So it looks like the SQLCMD query is binding an IP to the MyDB1\INST1 and caching\locking that. Everything else in the same GUI App session works as it should when switching envs.

Locked