| Author |
Topic |
|
billsox
Yak Posting Veteran
74 Posts |
 Posted - 2003-09-16 : 14:24:24
|
| Hi,I'm trying to enumerate all of the instances of SQL Server 2000 on my company's LAN using SQLDMO's ListAvailableSQLServers() method. I've had my enumeration routine working for several months (via VB.NET) but now that our network admins moved me from Win2K to WinXP, this method no longer enumerates the servers that have multiple instances of SQL Server running. After doing some research, I came across the ListInstalledInstances() method in SQLDMO. While this works for getting multiple instances from a server, it can only enumerate one server at a time -- thus greatly increasing the time it takes to enumerate over 40-50 servers in my company's LAN.Am I really supposed to run a nested loop that enumerates each server and then enumerates each instance on that server? Or is there a better way?Hoping you can help...Bill |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-16 : 15:01:46
|
| Does that mean you just want to count how many instances of sql server your company has?If it is...smells like the cart behind the horse...unless somethings changed (or I'm missing the boat again [splash]aaaeeeehhh[/spash]), you need a license per...And for a mere $49.00 bucks..it'll slice..it'll dice...it'll look like swiss cheese...it'll come with bug (what the opposite of (repellant)...gett'em while they last...(disclaimer: must be 21 or older in the state of nevada...all other recreational activities have no age limit...)Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 2003-09-16 : 15:04:21
|
No, actually I need the names of each instance.Billquote:
Originally posted by X002548
Does that mean you just want to count how many instances of sql server your company has?If it is...smells like the cart behind the horse...unless somethings changed (or I'm missing the boat again [splash]aaaeeeehhh[/spash]), you need a license per...And for a mere $49.00 bucks..it'll slice..it'll dice...it'll look like swiss cheese...it'll come with bug (what the opposite of (repellant)...gett'em while they last...(disclaimer: must be 21 or older in the state of nevada...all other recreational activities have no age limit...)Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric!
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-16 : 15:35:44
|
| This information is stored in the registry. You can read the registry using T-SQL with xp_regread. The information is stored here:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstancesYou don't have to use T-SQL to get the info, but that's one way to do it.Tara |
|
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 2003-09-16 : 15:56:00
|
Correct me if I'm wrong but wouldn't that registry location just store info on the installed instances of SQL Server located on a single PC? I'm looking for the names of all SQL Server 2000 instances throughout my company's LAN (w/o having to connect to each server and enumerate the installed instances).quote:
Originally posted by tduggan
This information is stored in the registry. You can read the registry using T-SQL with xp_regread. The information is stored here:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstancesYou don't have to use T-SQL to get the info, but that's one way to do it.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-16 : 16:04:21
|
| Microsoft has a utility called SQL Scan that can scan a network (can do it by subnet if necessary) for installed SQL Servers, including instances. I would download it from their website. You are looking for sqlscan.exe.Tara |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-16 : 16:41:00
|
| OK,Checked out the site...but why do you need to do this?Shops don't keep track? Is that why?I can imagine that in our glass house...any other reason?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-16 : 16:55:52
|
| We had to do this about a year ago because no one had kept track. They didn't used to have DBAs here, so when we came on board, we had to find all of the SQL Servers out there and figure out if we had a license for them and who was currently supporting them.Tara |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-09-16 : 17:51:51
|
quote:
Originally posted by X002548
OK,Checked out the site...but why do you need to do this?Shops don't keep track? Is that why?I can imagine that in our glass house...any other reason?
I'm currently having to do something similar, but I need to find all the instances of MSDE on our network. What a pain. We need this so that we can push out the SP3 patch to get them up to date. AS well as getting a handle on anyone running unlicensed SQL on our network.anyway, my shortcut was to use the NMAP port scanner to scan our entire subnet. I found all the boxes that responded on port 1434 in about 20 minutes.NMAP won't give you the instance name, but it will tell you who has SQL installed and running.-ec |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-09-23 : 09:56:26
|
quote:
but I need to find all the instances of MSDE on our network.
Is it not true that MSDE base install is without netlibraries enabled.Therefore some may go forever unknown. A network auditing tool would be needed; BELARC for example.If MSDE falls in a forest does it make a sound?Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-09-23 : 11:37:30
|
quote:
Originally posted by Sitka
quote:
but I need to find all the instances of MSDE on our network.
Is it not true that MSDE base install is without netlibraries enabled.Therefore some may go forever unknown. A network auditing tool would be needed; BELARC for example.If MSDE falls in a forest does it make a sound?Voted best SQL forum nickname...."Tutorial-D"
I guess if they don't have any netlibraries installed then they are not much of a security threat. If you cannot connect remotely to MSDE, you cannot exploit it either.-ec |
|
|
|
|
Enumerating SQL Server instances