Inventory SQL server?

Is there any way I can correctly identify all instances of SQL server
in my environment? We are using SMS 2003 for inventory.
I need to be able to differentiate between an actual SQL server
serving a DB, a workstation just running admin tools, an MSDE
installation, or a SQL Express installation.
Unfortunately, sqlservr.exe appears to be installed for all these
instances so I can't use that as a flag file. Add Remove programs is
another option, and that appears to identify The SQL Express version,
but does not appear to differentiate between the Server, the admin
tools install, or MSDE. The same goes for the service name.
The purpose of this is to reconcile our license counts in case of
audits. You'd think a large corporation could keep better track of
this stuff, but its kind of the wild west out here, and we're just
starting with putting up the barbed wire. I've worked a couple of
large organizations and haven't found one yet that does a good job of
this.
This it will have to something that can automated to process 100s or
even thousands of servers and workstations in our enterprise,
preferably using SMS to gather the data in some form. If necessary
I could use powershell , WMI or some vbscript, but I need something
to key on.
> Is there any way I can correctly identify all instances of SQL server
> in my environment?
There is no 100% reliable way. Most methods use the same technique to
"poll" workstations / servers in the network to check if SQL Server is
running. Unfortunately, various factors can inhibit the ability to do so...
port 1434 is closed (on individual machines, or network-wide via firewall),
some instances may be marked as hidden, some instances may not respond in
time, etc. etc.
Assuming none of these will actually be serving 24x7 production services, a
surefire way to figure out if a running SQL Server process is required by
users in your network is to take the service offline for 24 hours (or until
someone complains). :-)
Sorry I don't have a more foolproof automated way, but sadly, no such thing
exists.
A
|||Quest has a free tool called Quest Discovery Wizard.
There is another tool called SqlRecon, slow but works.
http://www.specialopssecurity.com/labs/sqlrecon/
Nothing I know will tell the difference between MSDE and regular SQL.
If you know a good VB programmer with WMI knowledge that might help!
The Quest tool will at least give you a base line of what machines to
hit.

Inventory SQL server?

Is there any way I can correctly identify all instances of SQL server
in my environment? We are using SMS 2003 for inventory.
I need to be able to differentiate between an actual SQL server
serving a DB, a workstation just running admin tools, an MSDE
installation, or a SQL Express installation.
Unfortunately, sqlservr.exe appears to be installed for all these
instances so I can't use that as a flag file. Add Remove programs is
another option, and that appears to identify The SQL Express version,
but does not appear to differentiate between the Server, the admin
tools install, or MSDE. The same goes for the service name.
The purpose of this is to reconcile our license counts in case of
audits. You'd think a large corporation could keep better track of
this stuff, but its kind of the wild west out here, and we're just
starting with putting up the barbed wire. I've worked a couple of
large organizations and haven't found one yet that does a good job of
this.
This it will have to something that can automated to process 100s or
even thousands of servers and workstations in our enterprise,
preferably using SMS to gather the data in some form. If necessary
I could use powershell , WMI or some vbscript, but I need something
to key on.> Is there any way I can correctly identify all instances of SQL server
> in my environment?
There is no 100% reliable way. Most methods use the same technique to
"poll" workstations / servers in the network to check if SQL Server is
running. Unfortunately, various factors can inhibit the ability to do so...
port 1434 is closed (on individual machines, or network-wide via firewall),
some instances may be marked as hidden, some instances may not respond in
time, etc. etc.
Assuming none of these will actually be serving 24x7 production services, a
surefire way to figure out if a running SQL Server process is required by
users in your network is to take the service offline for 24 hours (or until
someone complains). :-)
Sorry I don't have a more foolproof automated way, but sadly, no such thing
exists.
A|||Quest has a free tool called Quest Discovery Wizard.
There is another tool called SqlRecon, slow but works.
http://www.specialopssecurity.com/labs/sqlrecon/
Nothing I know will tell the difference between MSDE and regular SQL.
If you know a good VB programmer with WMI knowledge that might help!
The Quest tool will at least give you a base line of what machines to
hit.

Invalid object name in stored procedure

-- In SQL Server 2000
--When run from Query Analyzer I correctly get identification of line
numbers having duplicate values of SKU_NameUsedBySCS:
-- LineNumber1 LineNumber2
-- 2 5
-- but when I try to create a stored procedure having this same code I get:
-- Invalid object name '#x'.
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
PermTable WHERE SCS_ID =@.SCS_ID
go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
gohello steve, did you check previous error messages? is you database Case
Sensitive? If it is, then the select into statement must have the names of
your fields in lower case.
hope this helps.
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Hi
And where do you create the temporary table #x?
John
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||At the time the proc is compiled the #x table does not exist as it's
created at runtime. So when you try to compile the code to get an
execution plan the query optimiser cannot create a plan involving #x
because it doesn't yet exist.
Try creating the temp table explicitly in the proc and then inserting
into it with a normal INSERT statement (rather than SELECT ... INTO).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SteveInSC wrote:

>-- In SQL Server 2000
>--When run from Query Analyzer I correctly get identification of line
>numbers having duplicate values of SKU_NameUsedBySCS:
>-- LineNumber1 LineNumber2
>-- 2 5
>-- but when I try to create a stored procedure having this same code I get:
>-- Invalid object name '#x'.
>
>IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
>declare @.SCS_ID int
>set @.SCS_ID =11
>set nocount on
>select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
>PermTable WHERE SCS_ID =@.SCS_ID
>go
>select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
>from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
>drop table #x
>go
>
>
>|||Hi Steve
If you have simply created the stored procedure by wrapping the SQL below in
a create procedure call then you have a GO in the middle of the declaration.
This will terminate the declaration.
Query Analyser will then try to run the later commands as immediate
commands. As the table is created by the SELECT ... INTO inside the
definition it will not find it for the later SELECT statement.
Try removing the GO statement in the middle of the declaration if there is
one.
As a separate point I would recommend using a Table variable (you know the
structure you want) as the scope is much better defined.
I hope this helps
Alasdair Russell
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Comment out the first "GO" and you will be all set.
Try this:
create procedure sp_abc as
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x
from
PermTable WHERE SCS_ID =@.SCS_ID
-- ############## MySQLServer ############ --go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as
LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
go
SteveInSC wrote:
> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go|||You forgot to remove the "GO" before the "SELECT TOP 40".
Razvan