Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..
Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>
sql

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
Matt
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restor
es
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
--
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>

need help writing batch without cursor

i'm trying to write a batch that will perform a complex task using
set-based selects instead of a row-based cursor. let me know if you can
help me figure out how.
description of what i'm trying to do:
there is TABLE1, TABLE2, and TABLE3
i want to select each row from TABLE1, do some analysis on the data of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3
how do i do this in a T-SQL batch?
thanks in advance!What kind of analysis?
AMB
"iaesun@.yahoo.com" wrote:

> i'm trying to write a batch that will perform a complex task using
> set-based selects instead of a row-based cursor. let me know if you can
> help me figure out how.
> description of what i'm trying to do:
> there is TABLE1, TABLE2, and TABLE3
> i want to select each row from TABLE1, do some analysis on the data of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3
> how do i do this in a T-SQL batch?
> thanks in advance!
>|||i'd be curious how to do it even if there were no analysis, since it's
the row-by-row part that i'm not sure how to do in a set-based
solution.
but! in case it helps, here's the analysis i was thinking of (pardon
the psuedo-code for the row-by-row portion)
for each ROW in TABLE1
{
if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
begin
insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
end
set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
}|||>> i want to select each row from TABLE1, do some analysis on the data
of
that row, and then perform an insert of some data into TABLE2, and some
data into TABLE3 <<
Read what you wrote! What kind of spec is that? How do we debug code
which is not here. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity,
datatypes, etc. in your schema are. Sample data is also a good idea,
along with clear specifications.
Frankly, it sounds likeyou are splitting this vague Table1 into two
tables when you should be using a VIEW or a column with whatever the
criteria for this split is. The whole idea of databases was to avoid
redundant data.|||You don't know Joe.
Maybe he's actually normalizing table1.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112986095.913817.293370@.g14g2000cwa.googlegroups.com...
> of
> that row, and then perform an insert of some data into TABLE2, and some
> data into TABLE3 <<
> Read what you wrote! What kind of spec is that? How do we debug code
> which is not here. Please post DDL, so that people do not have to guess
> what the keys, constraints, Declarative Referential Integrity,
> datatypes, etc. in your schema are. Sample data is also a good idea,
> along with clear specifications.
> Frankly, it sounds likeyou are splitting this vague Table1 into two
> tables when you should be using a VIEW or a column with whatever the
> criteria for this split is. The whole idea of databases was to avoid
> redundant data.
>|||Try,
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"iaesun@.yahoo.com" wrote:

> i'd be curious how to do it even if there were no analysis, since it's
> the row-by-row part that i'm not sure how to do in a set-based
> solution.
> but! in case it helps, here's the analysis i was thinking of (pardon
> the psuedo-code for the row-by-row portion)
> for each ROW in TABLE1
> {
> if not exists (select * from TABLE2 where COLUMNA = ROW.COLUMN1)
> begin
> insert into TABLE2 (COLUMNA) values (ROW.COLUMN1)
> end
> set @.table2id = select ID from TABLE2 where COLUMNA = ROW.COLUMN1
> insert into TABLE3 values (ROW.COLUMN2, ROW.COLUMN3, @.table2id)
> }
>|||Correction,
Swith the order of the statements.
insert into t3 (colB, colC)
select col2, col3
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
insert into t2 (colA)
select col1
from t1
where not exists (select * from t2 where t2.colA = t1.col1)
AMB
"Alejandro Mesa" wrote:
> Try,
> insert into t2 (colA)
> select col1
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
> insert into t3 (colB, colC)
> select col2, col3
> from t1
> where not exists (select * from t2 where t2.colA = t1.col1)
>
> AMB
> "iaesun@.yahoo.com" wrote:
>|||well, i'm trying to keep the topic abstract, because i was hoping for a
more general description of how to do row-by-row processing in a
set-based solution. but, if such details are needed in this case, then
let me try to invent some. regarding the latter portion of your
message: this is, in a manner of speaking, splitting table1 into two
tables. however, it is more of a complex tranformation, not redundant
information. table1 is a staging table, and will be dropped after this
process is complete.
first, the three table definitions:
CREATE TABLE [sourcetable] (
[ID] [int] NOT NULL,
[column1] [int] NULL,
[column2] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable1] (
[ID] [int] NOT NULL,
[columnA] [int] NULL,
[columnB] [int] NULL,
) ON [primary]
GO
CREATE TABLE [destinationtable2] (
[ID] [int] NOT NULL,
[columnY] [int] NULL,
[columnZ] [int] NULL,
) ON [primary]
GO
what i would like to do, read [sourcetable] row-by-row. for each row, i
would like to perform the following batch:
IF NOT EXISTS (SELECT * FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1)
begin
INSERT INTO destinationtable1 (columnA) VALUES
(sourcetable.column1)
end
SELECT @.idvariable = ID FROM destinationtable1 WHERE
destinationtable1.columnA = sourcetable.column1
INSERT INTO destinationtable2 (columnY, columnZ) VALUES
(sourcetable.column2, @.table2id)
is that any clearer?|||yes, that is precisely the nature of this task. table1 is an imported
table from an outside system. i'm just splicing it into its logical,
normalized form.|||> what i would like to do, read [sourcetable] row-by-row. for each row,
i
would like to perform the following batch
Wrong. The idea is precisely to AVOID processing anything row-by-row.
Try this:
INSERT INTO destinationtable1 (columna)
SELECT DISTINCT column1
FROM sourcetable
WHERE NOT EXISTS
(SELECT *
FROM destinationtable1
WHERE columna = sourcetable.column1)
INSERT INTO destinationtable2 (columny, columnz)
SELECT S.column2, D.id
FROM sourcetable AS S
JOIN destinationtable1 AS D
ON S.column1 = D.columna
David Portas
SQL Server MVP
--