March 13, 2002 at 8:21 am
I have been playing around with DMO and wanted to see if there is a way I can discover the servers on the network and what they are running. Is there a way to find out if it is MSDE or SQL 7/2K? -JG
-JG
-JG
March 13, 2002 at 11:04 am
Have never tried! ListAvailableServers returns the list of servers that aren't hidden, possibly you could look at the properties of the sqlserver object, maybe in the version info?
Andy
March 14, 2002 at 10:47 am
I think the only true way to find all is to scan every machine on your network checking for SQL server, only those setup in WINS, The same domain as yourself, or in you Client Network settings are known SQL servers.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 3:41 pm
Weird DMO problem... two machines on network, each can see the other. SQL2k running on one as server, SQL2k client tools only on the other. DMO app running on server sees all installed servers (there are two), the same one running on the client sees none, and there's a very weird message... when it runs the ListAvailableSQLServers method, I get:
"QueryInterface for Interface SQLDMO.NameList failed"
Any ideas?
TIA,Chris
March 20, 2002 at 4:04 pm
Maybe a version problem? There are different objects for SQL7 and SQL2K, most of the SQL2K ones have a '2' appended.
Andy
March 20, 2002 at 4:13 pm
Don't think so, Andy... all I've done is:
app = New SQLDMO.Application()
availableServers = app.ListAvailableSQLServers
(Forgot to mention, this is VB.NET using DMO via COM interop).
Both PCs have only ever had Win2k, SQL2k installed.
Cheers,Chris
March 20, 2002 at 5:37 pm
Here's a ref:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_l_9jfo.asp
I also used VB 6, W2K Pro, SQL 2K personal, to get a list of servers with this function:
Public Function ListAllServers(i As Integer)
Dim ServerList As NameList
Dim objDMO As SQLDMO.Application
Set objDMO = New SQLDMO.Application
Set ServerList = objDMO.ListAvailableSQLServers()
For i = 1 To ServerList.Count
lstServers.AddItem (ServerList.Item(i))
Next
ListAllServers = 0
End Function
Steve Jones
April 10, 2002 at 1:45 am
This is my method of doing it.
Private Sub GetServers()
Dim NameList As SQLDMO.NameList
Dim X As Long
Set NameList = SQL.ListAvailableSQLServers
For X = 1 To NameList.Count
cbServers.AddItem NameList.Item(X)
Next
End Sub
cbServers is my ComboBox
Hope this helps
Regards
Gert
April 10, 2002 at 8:30 pm
You could get the info from the versionString property of the SqlServer object
Visual Basic ? Results from my sql server:
? objSqlServer.VersionString
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
MSDE on Windows NT 5.0 (Build 2195: Service Pack 2)
April 23, 2002 at 9:19 am
quote:
There's an easier way. I would create a list of servers, the perform the following t-sql script on them.<BR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC usp_ListSQLInstance(@HostName HOSTNAME)
AS
/***
* Date: 4/23/2002
* Author: <mailto:mikemcw@4segway.biz>
* Project: Detecting SQL Instances
* Location: Any user database
* Permissions: PUBLIC EXECUTE
*
* Description: Returns a list of instances found
* on a machine.
*
* Restrictions: The instance may not be running, could
* parse the results from srvinfo.exe
* SQL Server Only
*
* Requirements: reg.exe from the resource kit (nt or 2k)
*
* History:
*
***/
BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @instance varchar(100)
CREATE TABLE #instance (instance varchar (150))
DECLARE @strSQL VARCHAR(400)
SET @strSQL = 'call reg query "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" \\' + LTRIM(@HostName) + '| findstr /I /L /C:"[" '
INSERT INTO #instance EXECUTE master..xp_cmdshell @strSQL
DELETE FROM #instance where instance IS NULL
DELETE FROM #instance where instance = '[80]' OR instance = '[8.00.000]'
DELETE FROM #instance where charindex(' ', instance) > 0
UPDATE #instance SET instance = REPLACE(instance, '[','')
UPDATE #instance SET instance = REPLACE(instance, ']','')
SELECT * FROM #instance --WHERE RTRIM(LTRIM(instance)) like '%[%'
DROP TABLE #instance
END
GO
GRANT EXECUTE ON usp_ListSQLInstance TO PUBLIC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
quote:
I have been playing around with DMO and wanted to see if there is a way I can discover the servers on the network and what they are running. Is there a way to find out if it is MSDE or SQL 7/2K? -JG-JG
-Mike
-Mike
April 23, 2002 at 1:14 pm
Thats interesting, but how are you getting the list of servers to start with??
Andy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply