+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: How can I determine installed SQL Server instances and their versions? (/Thread-How-can-I-determine-installed-SQL-Server-instances-and-their-versions)
How can I determine installed SQL Server instances and their versions? - Laury306125 - 07-31-2023
I'm trying to determine what instances of sql server/sql express I have installed (either manually or programmatically) but all of the examples are telling me to run a SQL query to determine this which assumes I'm already connected to a particular instance.
RE: How can I determine installed SQL Server instances and their versions? - dermatic743326 - 07-31-2023
SQL Server Browser Service
[To see links please register here]
RE: How can I determine installed SQL Server instances and their versions? - agilely237557 - 07-31-2023
All of the instances installed should show up in the Services Snap-In in the Microsoft Management Console. To get the instance names, go to Start | Run | type Services.msc and look for all entries with "Sql Server (Instance Name)".
RE: How can I determine installed SQL Server instances and their versions? - unheuristic899717 - 07-31-2023
You could query this registry value to get the SQL version directly:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion
Alternatively you can query your instance name and then use sqlcmd with your instance name that you would like:
To see your instance name:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names
Then execute this:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
[If you are using C++ you can use this code to get the registry information.][1]
[1]:
[To see links please register here]
RE: How can I determine installed SQL Server instances and their versions? - gink408 - 07-31-2023
If you just want to see what's installed on the machine you're currently logged in to, I think the most straightforward manual process is to just open the SQL Server Configuration Manager (from the Start menu), which displays all the SQL Services (and *only* SQL services) on that hardware (running or not). This assumes SQL Server 2005, or greater; [dotnetengineer][1]'s recommendation to use the Services Management Console will show you all services, and should always be available (if you're running earlier versions of SQL Server, for example).
If you're looking for a broader discovery process, however, you might consider third party tools such as SQLRecon and SQLPing, which will scan your network and build a report of all SQL Service instances found on any server to which they have access. It's been a while since I've used tools like this, but I was surprised at what they found (namely, a handful of instances that I didn't know existed). YMMV. You might Google for details, but I believe this page has the relevant downloads: [
[To see links please register here]
][2][1]:
[To see links please register here]
[2]:[To see links please register here]
RE: How can I determine installed SQL Server instances and their versions? - lynnaxwxfykefi - 07-31-2023
I had the same problem. The "osql -L" command displayed only a list of servers but without instance names (only the instance of my local SQL Sever was displayed).
With Wireshark, sqlbrowser.exe (which can by found in the shared folder of your SQL installation) I found a solution for my problem.
The local instance is resolved by registry entry. The remote instances are resolved by UDP broadcast (port 1434) and SMB.
Use "sqlbrowser.exe -c" to list the requests.
My configuration uses 1 physical and 3 virtual network adapters.
If I used the "osql -L" command the sqlbrowser displayed a request from one of the virtual adaptors (which is in another network segment), instead of the physical one.
osql selects the adpater by its metric. You can see the metric with command "route print".
For my configuration the routing table showed a lower metric for teh virtual adapter then for the physical. So I changed the interface metric in the network properties by deselecting automatic metric in the advanced network settings.
osql now uses the physical adapter.
RE: How can I determine installed SQL Server instances and their versions? - hazardousnesses375179 - 07-31-2023
I just installed Sql server 2008, but i was unable to connect to any database instances.
The commands @G Mastros posted listed no active instances.
So i looked in services and found that the SQL server agent was disabled. I fixed it by setting it to automatic and then starting it.
RE: How can I determine installed SQL Server instances and their versions? - nagarajsyjlgwp - 07-31-2023
-- T-SQL Query to find list of Instances Installed on a machine
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
Select InstanceNames from @GetInstances
RE: How can I determine installed SQL Server instances and their versions? - stannoxyluwtmnxc - 07-31-2023
SQL Server permits applications to find SQL Server instances within the current network. The SqlDataSourceEnumerator class exposes this information to the application developer, providing a DataTable containing information about all the visible servers. This returned table contains a list of server instances available on the network that matches the list provided when a user attempts to create a new connection, and expands the drop-down list containing all the available servers on the Connection Properties dialog box. The results displayed are not always complete.
In order to retrieve the table containing information about the available SQL Server instances, you must first retrieve an enumerator, using the shared/static Instance property:
using System.Data.Sql;
class Program
{
static void Main()
{
// Retrieve the enumerator instance and then the data.
SqlDataSourceEnumerator instance =
SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();
// Display the contents of the table.
DisplayData(table);
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}
from msdn
[To see links please register here]
RE: How can I determine installed SQL Server instances and their versions? - chocsckhmw - 07-31-2023
Here is a simple method:
go to
Start then
Programs then
Microsoft SQL Server 2005 then
Configuration Tools then
SQL Server Configuration Manager then
SQL Server 2005 Network Configuration then
Here you can locate all the instance installed onto your machine.