Finding Duplicates

I have a pretty extensive database system I am querying, though what I am
looking for should be easy to find. I am looking for instances in the
Clients table that appear more than once (duplicate clients). I am using the
following:
Select First_Name + ' ' + Last_Name as Client,
Count(SSNum) as Count
From Clients
Group By First_Name, Last_Name, SSNum
Having Count(SSNum)>=2
Where I am confused is this; if I remove the Group By SSNum I have 126
instances. With the Group By SSNum I have 56. When I link in the account
table to get the site name and worker name I have 14 instances. Is there
another more reliable way to tell what names and ssn's happen more than
once?
TIA"John C Harris" <harris1113@.tampabay.rr.com> wrote in message
news:4416EDF4-0119-45CC-87A9-D23E2EEFB63B@.microsoft.com...
>I have a pretty extensive database system I am querying, though what I am
>looking for should be easy to find. I am looking for instances in the
>Clients table that appear more than once (duplicate clients). I am using
>the following:
> Select First_Name + ' ' + Last_Name as Client,
> Count(SSNum) as Count
> From Clients
> Group By First_Name, Last_Name, SSNum
> Having Count(SSNum)>=2
> Where I am confused is this; if I remove the Group By SSNum I have 126
> instances. With the Group By SSNum I have 56. When I link in the account
> table to get the site name and worker name I have 14 instances. Is there
> another more reliable way to tell what names and ssn's happen more than
> once?
> TIA
>
If you include SSNum in the GROUP BY then you will get one row for every
unique (First_Name, Last_Name, SSNum) that is duplicated.
If you don't include SSNum in the GROUP BY then you will get one row for
every unique (First_Name, Last_Name) that is duplicated.
Both methods are perfectly reliable but they tell you different things. It
all depends on what answer you want.
I guess the problem with the JOIN version is that you are doing an INNER
JOIN that is eliminating some rows. Difficult to say without seeing the
code.
Please post DDL, sample data and required results if you need more help.
--
David Portas|||Ah OK. I think I have it. Does this make sense:
I run the code below and get 56 names and ssns duplicated
I add the Worker ID and get 14 names
I add the Site Name and also get 14 names
So this is basically telling me that I have 56 clients in the table that
match more than once on name and SSN, but by adding the site and worker that
goes down to 14, meaning that I have 14 duplicates with the same name AND
same site/worker. So from that I can assume of the 56 names, 42 of them are
in more than one site, but only once in those sites?
That actually makes sense in looking through the raw data, because it
appears the same client went to more than one site.
"John C Harris" <harris1113@.tampabay.rr.com> wrote in message
news:4416EDF4-0119-45CC-87A9-D23E2EEFB63B@.microsoft.com...
>I have a pretty extensive database system I am querying, though what I am
>looking for should be easy to find. I am looking for instances in the
>Clients table that appear more than once (duplicate clients). I am using
>the following:
> Select First_Name + ' ' + Last_Name as Client,
> Count(SSNum) as Count
> From Clients
> Group By First_Name, Last_Name, SSNum
> Having Count(SSNum)>=2
> Where I am confused is this; if I remove the Group By SSNum I have 126
> instances. With the Group By SSNum I have 56. When I link in the account
> table to get the site name and worker name I have 14 instances. Is there
> another more reliable way to tell what names and ssn's happen more than
> once?
> TIA
>sql

Finding database on virtual servers

C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce

>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>
This is an active/active cluster. All instances of sql need to be running at
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plus
a couple on control databases. I don't want to have the control databases in
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:

> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> going to move my
> Now when my web
> will they find which
> build a connection
> here. I be grateful
>
|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce

>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient[vbcol=seagreen]
>use of my hardware.
>"Bruce de Freitas" wrote:
concurrently[vbcol=seagreen]
active[vbcol=seagreen]
call[vbcol=seagreen]
your[vbcol=seagreen]
instance.[vbcol=seagreen]
to[vbcol=seagreen]
look
>.
>
|||You don't understand my question. The fail-over I'm not worried about and we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>
|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>

Finding database on virtual servers

C, your cluster, is it active/active and your have
multiple SQL SERVER instances that you run concurrently
on BOTH boxes, active/active? or is only one node active
and the other is sitting there just in case
(active/passive)? I'm not clear on what you mean by
multiple instances? SQL instances I guess... Either
way, you'd have a virtual name that your application
would refer to. When the node failover, you still call
the virtual name and the cluster wil get you to the
active side of the cluster. Maybe I'm not getting your
question completly?!? Bruce

>--Original Message--
>I have a sql 2000 cluster (multiple instance) and I'm
going to move my
>databases over and spread them out over each instance.
Now when my web
>servers need to read/write info to the databases how
will they find which
>virtual server has the database it needs. Do I have to
build a connection
>string that says, first look here and if not then look
here. I be grateful
>for any ideas or suggestions.
>.
>This is an active/active cluster. All instances of sql need to be running a
t
all times. I am hoping to spread the load (databases) evenly across the
servers. I have 150 databases on various sizes and transaction amounts, plu
s
a couple on control databases. I don't want to have the control databases i
n
one instance and the rest in another, because I will not be making efficient
use of my hardware.
"Bruce de Freitas" wrote:

> C, your cluster, is it active/active and your have
> multiple SQL SERVER instances that you run concurrently
> on BOTH boxes, active/active? or is only one node active
> and the other is sitting there just in case
> (active/passive)? I'm not clear on what you mean by
> multiple instances? SQL instances I guess... Either
> way, you'd have a virtual name that your application
> would refer to. When the node failover, you still call
> the virtual name and the cluster wil get you to the
> active side of the cluster. Maybe I'm not getting your
> question completly?!? Bruce
>
>
> going to move my
> Now when my web
> will they find which
> build a connection
> here. I be grateful
>|||ok cool, active/active with multiple SQL instances...
I've done more active/passive with single instances, much
less complex then what you're trying to do. But, I
believe it works like this. Say you have two nodes and
you want to have 3 SQL instances active on NodeA (SQL1,
SQL2, SQL3) and 2 other SQL instances active on NodeB
(SQL4, SQL5). While you could play a game with switching
SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
it's much cleaner to have 5 instances on each node, so
the 2 on NodeA sit there as a failover for the 2 on NodeB
and 3 on NodeB sit there as failover for the 3 on NodeA.
You'd have 5 virtual names that your app refers to, but
10 SQL instances across the 2 boxes. If NodeB totally
crashed, then all 5 SQL instances on NodeA would be
active, until NodeB came back online. If you're trying
to do somethign along those lines, then I think you're ok
referring to each virtual name, and let the Cluster
Manager worry about directing SQL requests to the right
place. Bruce

>--Original Message--
>This is an active/active cluster. All instances of sql
need to be running at
>all times. I am hoping to spread the load (databases)
evenly across the
>servers. I have 150 databases on various sizes and
transaction amounts, plus
>a couple on control databases. I don't want to have the
control databases in
>one instance and the rest in another, because I will not
be making efficient
>use of my hardware.
>"Bruce de Freitas" wrote:
>
concurrently[vbcol=seagreen]
active[vbcol=seagreen]
call[vbcol=seagreen]
your[vbcol=seagreen]
instance.[vbcol=seagreen]
to[vbcol=seagreen]
look[vbcol=seagreen]
>.
>|||You don't understand my question. The fail-over I'm not worried about and w
e
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>|||For the sake of simplicity, let's say you only have 2 virtual SS instances,
in an Active/Active configuration. Regardless of the current state of the
fail-over configuration--that is, whether or not the current host of the
virtual servers is the same or distributed across the physical nodes--you
would still need to have a connection to each virtual server.
Now, it would be possible to create a third SS virtual instance and make
this sort of the master, virtual server. If you created a control database
for your web server to always connect to, then you could establish linked
servers from this host to the other two. Then, if you always queried this
third server using four-part names, then you could establish queries from
the third sever but against either of the first two.
Also, on the third server, in your control database, you could create the
following VIEW:
CREATE dbo.VIEW ServerDatabases
AS
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER1.master.INFORMATION_SCHEMA.SCHEMATA
UNION ALL
SELECT SERVER_NAME = 'SERVER1', DATABASE_NAME = CATALOG_NAME
FROM SERVER2.master.INFORMATION_SCHEMA.SCHEMATA
GO
Then you could query this for a particular database to determine the server.
Then you would execute your query against SERVER3 but using the four-part
name against either SERVER1 or SERVER2 depending on where the database
lived.
SELECT SERVER_NAME
FROM SERVER3.controldatabase.dbo.ServerDatabases
WHERE DATABASE_NAME = 'MyDatabase'
Now, you do not necessarily need the third instance and could use either of
the first two to create this common control database. However, keep in mind
that if you run all of your queries through a common server, that one will
always be used for resources, even if the subsequent call is redirected to
either of the other two servers.
It is more complex, but you would be better off setting up a seperate
connection for each host you query.
Sincerely,
Anthony Thomas
"C-Ya" <CYa@.discussions.microsoft.com> wrote in message
news:8B94987E-51F0-4219-A9D4-DE351A9BD098@.microsoft.com...
You don't understand my question. The fail-over I'm not worried about and
we
will be making called to virtual servers so it doesn't matter which box the
instances run on. My question is: Do my web servers "have" to know which
instance of SQL the database it needs is in or is there a way for the web
server to query and the instances to find where the database it need is?
"Bruce de Freitas" wrote:

> ok cool, active/active with multiple SQL instances...
> I've done more active/passive with single instances, much
> less complex then what you're trying to do. But, I
> believe it works like this. Say you have two nodes and
> you want to have 3 SQL instances active on NodeA (SQL1,
> SQL2, SQL3) and 2 other SQL instances active on NodeB
> (SQL4, SQL5). While you could play a game with switching
> SOME databases from NodeA SQL1 to NodeB SQL4, I'd think
> it's much cleaner to have 5 instances on each node, so
> the 2 on NodeA sit there as a failover for the 2 on NodeB
> and 3 on NodeB sit there as failover for the 3 on NodeA.
> You'd have 5 virtual names that your app refers to, but
> 10 SQL instances across the 2 boxes. If NodeB totally
> crashed, then all 5 SQL instances on NodeA would be
> active, until NodeB came back online. If you're trying
> to do somethign along those lines, then I think you're ok
> referring to each virtual name, and let the Cluster
> Manager worry about directing SQL requests to the right
> place. Bruce
>
> need to be running at
> evenly across the
> transaction amounts, plus
> control databases in
> be making efficient
> concurrently
> active
> call
> your
> instance.
> to
> look
>sql

Finding BINN directory for Multiple Instances

I am writing an installer that needs to put a dll file with a bunch of
extended stored procedures in the BINN directory. The problem is that
if the machine is running multiple instances of SQL Server, there is
more than one BINN directory. The installer has a dynamically
populated dropdown with the name of the instances, so does anyone know
if this logic is correct and/or reliable assuming SQL Server 2000?

If the instance name = "MSSQLSERVER"
then path to BINN directory = registry value of
HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"

If the instance name != "MSSQLSERVER"
then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"

Thanks in advance for any help."Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0407231210.28d2403c@.posting.google.c om...
> I am writing an installer that needs to put a dll file with a bunch of
> extended stored procedures in the BINN directory. The problem is that
> if the machine is running multiple instances of SQL Server, there is
> more than one BINN directory. The installer has a dynamically
> populated dropdown with the name of the instances, so does anyone know
> if this logic is correct and/or reliable assuming SQL Server 2000?
> If the instance name = "MSSQLSERVER"
> then path to BINN directory = registry value of
> HLM\SOFTWARE\Microsoft\MSSQLServer\Setup\SQLPath + "\BINN"
> If the instance name != "MSSQLSERVER"
> then path to BINN directory = registry value of HLM\SOFTWARE\Microsoft
> SQL Server\<Instance Name>\Setup\SQLPath + "\BINN"
> Thanks in advance for any help.

See questions 12 and 13:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon

Find SQL Instances on Network

Basic question. I'm new to my position. Lots of SQL on the network but no
documentation. How can I identify where SQL server is running and what
version is there?
Found this article on the net
http://www.databasejournal.com/featu...le.php/3397981 that is
great but only addresses one WAN Segment. HAve about 30 segements to review
and was hoping for one solution to identify all the instances at one time.
Thanks
Jody Howard
"JHoward" <jodyhow(removeme)@.hotmail.com> wrote in message
news:156905AB-BF17-4F62-8A55-1F2443AA4C6D@.microsoft.com...
> Basic question. I'm new to my position. Lots of SQL on the network but
no
> documentation. How can I identify where SQL server is running and what
> version is there?
> Found this article on the net
> http://www.databasejournal.com/featu...le.php/3397981 that is
> great but only addresses one WAN Segment. HAve about 30 segements to
review
> and was hoping for one solution to identify all the instances at one time.
Covered this on another post...
Steve

Find servers runnign reporting services

How can I enumerate only those servers running reporting services in
c#.
I can get a list of all SQL Server instances on the local network
using:
SmoApplication.EnumAvailableSqlServers(false)
How can i filter this list to include only those instances where
Reporting Services is running?
Thanks in advance
Dave ArkleyOn Sep 14, 7:06 am, daveark...@.wildair.net wrote:
> How can I enumerate only those servers running reporting services in
> c#.
> I can get a list of all SQL Server instances on the local network
> using:
> SmoApplication.EnumAvailableSqlServers(false)
> How can i filter this list to include only those instances where
> Reporting Services is running?
> Thanks in advance
> Dave Arkley
You can loop through the list and run a query on each SQL Server
Instance similar to the following to determine if SSRS is installed
(at least for the most part):
SELECT COUNT(*) FROM SYS.DATABASES WHERE [NAME] IN
('REPORTSERVER','REPORTSERVERTEMPDB');
If the query comes back w/a result of 2, then Reporting Services is
installed. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Find instances of sql running

Is it possible to do the following. If yes then how ?
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-FatemaFatema
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
Read up in the BOL chapter "Viewing the SQL Server Error Log"
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Thanks for the reply.
On executing this procedure it gives me an error:
Login Failed. Reason: not associated with a trusted SQL server connection.
What is the problem ?
TIA,
-Fatema
"Uri Dimant" wrote:

> Fatema
> CREATE PROCEDURE dbo.ListLocalServers
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #servers(sname VARCHAR(255))
> INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
> DELETE #servers WHERE sname='Servers:'
> SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
> DROP TABLE #servers
> END
>
> Read up in the BOL chapter "Viewing the SQL Server Error Log"
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||Fatema
Change (SQL Server Properties)
under Security Tab -Authentication to SQL Server and Windows
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> Thanks for the reply.
> On executing this procedure it gives me an error:
> Login Failed. Reason: not associated with a trusted SQL server connection.
> What is the problem ?
> TIA,
> -Fatema
> "Uri Dimant" wrote:
>
running ?|||I am getting the following o/p :
Name (column)
--
Password
1>
WHat does this mean ?
-Fatema
"Uri Dimant" wrote:

> Fatema
> Change (SQL Server Properties)
> under Security Tab -Authentication to SQL Server and Windows
>
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> running ?
>
>|||1. Not in a reliable way. Uri posted a method, but please be aware that all
methods (all are based
on API calls in Windows) are based on browser service in Windows and it is n
ot guaranteed to show
all servers, also servers can be in the browser list after it was shut down.
2. Can you define what you mean by alerts?
3. Can you define what you mean by "logs"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Can you please tell me how is it possible to programmatically find out all
instances of sql server that are running ?
By logs I mean the logs that sql server generates during its course of
execution.
Thanks,
-fatema
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
As I already replied: Not reliably. Search the newsgroup archives and you wi
ll probably find posts
from Gert Drapers specifying the technicalities behind this. If you want to
list the servers anyhow
(unreliably), you have classes for that in, for instance, SQLDMO. See www.sqldev.n
et for examples.

> By logs I mean the logs that sql server generates during its course of
> execution.
Errorlog? Transaction log? Entries the in the eventlog? Do you want to get t
o this using TSQL or a
client programming language, like VB.NET?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:353E84EC-5811-4D21-86A4-19B8F5C3A69E@.microsoft.com...
> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
> By logs I mean the logs that sql server generates during its course of
> execution.
> Thanks,
> -fatema
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Another problem with OSQL -L is that it list whatever alias you create with
"Client Network Utility" even though there is not a physical server
associated to this alias.
AMB
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>

Find all instances of MS SQL

How can I find all instances of MS SQL running in my organization? My task to is to audit all instances and stop unautorised or unlicensed instances.
Any help will be appreciated.
Thanks,Originally posted by azam7
How can I find all instances of MS SQL running in my organization? My task to is to audit all instances and stop unautorised or unlicensed instances.

Any help will be appreciated.
Thanks,

You can use OSQL with the -L switch to list servers on you network. I don't know off hand if it will list any across subnets, etc. The command is: isql -L

Mike|||From what I remember it will only list sql servers within that domain - even then it may not be complete.|||Thanks a lot guys...isql -l works
:-)