Dear all,
According to a date introduce I would need to obtain all the periodDesc of
the following table:
CREATE TABLE [dbo].[tbl_Periods] (
[sinStudyID] [smallint] NOT NULL ,
[strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[boldone] [bit] NOT NULL ,
[sinPeriodID] [smallint] NOT NULL ,
[strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datPeriodBegin] [datetime] NULL ,
[datPeriodEnd] [datetime] NULL ,
)
If I have got as input '2005-01-02' I need obtain this set of rows:
begin end
4 COW 0 203 W2005006 2005-01-30 2005-01-31
4 COW 0 202 W2005005 2005-01-23 2005-01-29
4 COW 0 201 W2005004 2005-01-16 2005-01-22
4 COW 0 196 W2005003 2005-01-09 2005-01-15
4 COW 0 195 W2005002 2005-01-02 2005-01-08
4 COW 0 194 W2005001 2005-01-01 2005-01-01
Any advice will be well received.
Regards,SELECT <column lists> FROM Table
WHERE datPeriodBegin >=@.dt AND datPeriodEnd < dateadd(day,1,@.dt)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||And the date you provide is related to the datPeriodBegin and datPeriodEnd
in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
both?
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||Hi,
Only could be this, nothing else:
2005-01-30
2005-01-23
2005-01-16
2005-01-09
2005-01-02
2005-01-01
Thanks,
"Jacco Schalkwijk" wrote:
> And the date you provide is related to the datPeriodBegin and datPeriodEnd
> in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
> both?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
>
>|||Sorry, these former values are efectively just for datPeriodBegin
Best wishes,
"Enric" wrote:
> Hi,
>
> Only could be this, nothing else:
> 2005-01-30
> 2005-01-23
> 2005-01-16
> 2005-01-09
> 2005-01-02
> 2005-01-01
>
> Thanks,
> "Jacco Schalkwijk" wrote:
>|||And how does that list of dates relate to 2005-01-02? All in the same month?
In that case:
DECLARE @.date DATETIME
SET @.date = '20050102'
SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
[strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
FROM [tbl_Periods]
WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
> Sorry, these former values are efectively just for datPeriodBegin
> Best wishes,
> "Enric" wrote:
>|||It works amazingly.
Thanks a lot man,
"Jacco Schalkwijk" wrote:
> And how does that list of dates relate to 2005-01-02? All in the same mont
h?
> In that case:
> DECLARE @.date DATETIME
> SET @.date = '20050102'
> SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
> [strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
> FROM [tbl_Periods]
> WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
> AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
>
>
Friday, February 24, 2012
Obtaining a value from Openquery?
Hi all,
I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.
My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)
DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)
SET @.NAMEID = (Select Inserted.NameID from Inserted)
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'
EXEC (@.TSQL)
How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.
Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:
DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)
create table #tmp(nar_num <data type>)
select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'
truncate table #tmp
insert into #tmp
EXEC (@.TSQL)
select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end
Please note that I changed things a bit to handle more than one one record.
I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.
My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)
DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)
SET @.NAMEID = (Select Inserted.NameID from Inserted)
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'
EXEC (@.TSQL)
How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.
Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:
DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)
create table #tmp(nar_num <data type>)
select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'
truncate table #tmp
insert into #tmp
EXEC (@.TSQL)
select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end
Please note that I changed things a bit to handle more than one one record.
Obtaining a report with alters of a table
Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
Obtaining a report with alters of a table
Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?
Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?
Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
Obtaining a report with alters of a table
Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?
Obtained an error when performing a dbcc on an sms database
An error occurred while executing the following
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment on what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the full output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment on what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the full output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
Obtained an error when performing a dbcc on an sms database
An error occurred while executing the following
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return
error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment o
n what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the ful
l output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
query: "DBCC CHECKCATALOG([sms_055])".
SQL error number: "09E8".
SQL error message: "DBCC results for 'sms_055'.
".
Thanks for your help in advance!!It seems you didn't execute the DBCC from Query Analyzer (QA doesn't return
error number in hex).
The actual error from SQL Server isn't included here, so we cannot comment o
n what the problem might
be. I suggest that you execute the DBCC from Query Analyzer and post the ful
l output here. Pls add
WITH NO_INFOMSGS so you don't get all those informational messages.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Maria Garcia" <garcim@.miamidade.gov> wrote in message
news:472801c3e438$9778be40$a001280a@.phx.gbl...
quote:
> An error occurred while executing the following
> query: "DBCC CHECKCATALOG([sms_055])".
> SQL error number: "09E8".
> SQL error message: "DBCC results for 'sms_055'.
> ".
> Thanks for your help in advance!!
Obtain XML from URL with SQL Server 2005?
Hi guys,
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!
You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!
|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
don't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few weeks I'll have some time to work on it. =/
Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few weeks I'll have some time to work on it. =/
> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!
You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!
|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
don't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few weeks I'll have some time to work on it. =/
Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few weeks I'll have some time to work on it. =/
> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
Obtain XML from URL with SQL Server 2005?
Hi guys,
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the w
end. What a @.$#)@.( =P Specially since they
don't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few ws I'll have some time to work on it. =/
Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the wend. What a @.$#)@.( =P Specially since they
> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few ws I'll have some time to work on it. =/
> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the w
end. What a @.$#)@.( =P Specially since theydon't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few w
s I'll have some time to work on it. =/Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the w
end. What a @.$#)@.( =P Specially since they> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few w
s I'll have some time to work on it. =/> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
Obtain values from different tables
Table processes_user
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.
Obtain unit percent with unit count divided by total count in query
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @.Date datetime
set @.date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@.Date) and @.Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
I figured out my solution - The top integer needs to be cast as a decimal:
(CAST(Count(substring(m.PTNumber,3,3)) as Decimal(15,5))/
(select Count(substring(m1.PTNumber,3,3)) from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) * 100 as AUPct
Obtain the sql server properties by code...
Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t
.
thanks in advance...
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blog...er.ht
ml
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t
.
thanks in advance...
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blog...er.ht
ml
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy
Obtain the sql server properties by code...
Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t.
thanks in advance...
Chevy
Those settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy
|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t.
thanks in advance...
Chevy
Those settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy
|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy
Obtain the sql server properties by code...
Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can´t.
thanks in advance...
--
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
--
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can´t.
thanks in advance...
--
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
--
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy
obtain the result of dynamic query with openrowset
im running a dynamic query with open rowset in it
pseudocode:
@.CMD=declare @. RETURN SELECT @.RETURN =SUM(X) FROM OPENROWSET(....) SELECT @.RETURN
EXEC @.CMD
This pseudocode dipplay the result of @.return
the problem:
capture @.return into @.myvalue outside the dynamic sql scope
something like
Select @.myvalue=exec(@.cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@.cmd)
thanks,
joey
Well, in 2005, you can direct EXEC to execute on a different server, but I think what you want to do is to use sp_executeSQL:
You will have to configure linked servers, (as well as possibly MSDTC for the servers,) but that would be the direction I would head. If you just want a a single row of values, you can use the following type of syntax:
declare @.name sysname
exec [.\sqlexpress].master.dbo.sp_executesql N'select @.name = @.@.servername ',N'@.name sysname output',@.name output
select @.name
I did it with insert into tableName... and it wanted MSDTC to be on.
|||hi louis,
i'm using sql server 2000
calling sql2k5
regards,
joey
|||sp_executeSQL existed in 2000 in the same manner.|||thanks
how can i reuse the output parameter
it is within a loop.
got this error
The variable name '@.RESULT' has already been declared. Variable names must be unique within a query batch or stored procedure.
|||
Just declare it once and set it to NULL before you execute the command...
If I am missing the point, can you post the code?
|||got it.
i have a declare within @.cmd
thanks
Obtain the query plan of a running process
Hi,
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
Thanks in advance,
Thyagu.DOne way to do this task is: Set up a Profile Trace and add Show Plan
event , save the info , and filter with the SPID
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"Thyagu" <tdelli@.gmail.comwrote in message
news:1158834419.604983.220140@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
Hi,
>
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
>
Thanks in advance,
Thyagu.D
>
obtain the next Identity seed?
Hi,
I'm using identity seed (increment by 1) as primary key in my tables.
Is there any way to determine what will be the next available identity for a specified table?
Thanks for the info
GauthierHi,
In 2000 you can use IDENT_CURRENT('table name ') + 1 to get next possible value. but in multi user env , need to check ...
best of luck|||that's it!
thanks alot.
Gauthier
I'm using identity seed (increment by 1) as primary key in my tables.
Is there any way to determine what will be the next available identity for a specified table?
Thanks for the info
GauthierHi,
In 2000 you can use IDENT_CURRENT('table name ') + 1 to get next possible value. but in multi user env , need to check ...
best of luck|||that's it!
thanks alot.
Gauthier
obtain the name and path of a report
how to save the name and the path of a report on my database when the user click save report
Does this cover what you need?
http://msdn2.microsoft.com/en-us/ms155836.aspx
||| I need something that doesn't depend on a stored procedure or manual procedure.
I need to execute something when the user saves the report; something that returns the path for this report.
I need to generate in my website all paths for reports recently saved.
thanks again and I′ll be waiting for your answer
Regards
|||What is "recently saved?" Is it time-bound? Why not use the SOAP API ListItems() and get the created date for every report in the catalog, and then filter for the ones which were recently created?
There is no supported way to inject your own code into the CreateReport() API which is used to publish reports.
|||Let me try to be a little more specific. What I want is to be able to "catch" the event where the user saves or closes the report, so that I can create the related class, and it has to be right when it happens, it can′t be later. It would also be useful if the event could identify the name and path of the report I just created. I am sorry if I am not able to be a little more clear regarding this subject, but I hope you understand what I want, and if there is no way to do it just let me know either way.
|||There is no built-in way of doing this via reporting services.
|||and with reporting services extensions?
or is there another possible way to do this?
|||Though you can't do this generically, you can provide a custom user interface for your users to access reports. In this way, you can determine when users click on reports or when they navigate away from them. The RS SOAP API allows you to expose all of the same functionality as you see in SQL Server Management Studio or Report Manager.
RS Extensions do not help you in this respect.
Not entirely sure what you want this kind of behavior for...
-Lukasz
Obtain the last NEWSEQUENTIALID()
I'm using sql server 2005, I created a customer table and set the customer_id as an uniqueidentifier that is a NEWSEQUENTIALID()...when a user is created I want to obtain the last customer_id so that I can insert it in another table that has a one to many relationship with the customer table. Please help...how do i do this?.
Thanx
YOu can issue this query after you insert: SELECT scope_identity();
and it will give you back your last inserted record. Take a look at this link for more details.
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
bdotjones:
I thought the SELECT scope_identity() on worked for identity fields does it also work for uniqueidentifier fields?
Try this for more info. Hope this helps.
http://sqljunkies.com/Article/4067A1B1-C31C-4EAF-86C3-80513451FC03.scuk
Obtain sqlceresultset table schema with GetSchemaTable
I have a SqlCeResult pointing to a table :
Public conexion As Data.SqlServerCe.SqlCeConnection = New Data.SqlServerCe.SqlCeConnection("Data Source=iPAQ File Store\GestionAlmacenN.sdf")
Public ComandoCargaProductos As Data.SqlServerCe.SqlCeCommand = conexion.CreateCommand
Public ResultSetProductos As Data.SqlServerCe.SqlCeResultSet
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
conexion.Open()
'**********************************************************
'************Conexion con Tabla Productos******************
'ComandoCargaProductos.CommandText = "SELECT * from [Productos]"
ComandoCargaProductos.CommandType = Data.CommandType.TableDirect
ComandoCargaProductos.IndexName = "PK__Productos__0000000000000041"
ComandoCargaProductos.CommandText = "Productos"
ResultSetProductos = ComandoCargaProductos.ExecuteResultSet(Data.SqlServerCe.ResultSetOptions.Scrollable Or Data.SqlServerCe.ResultSetOptions.Updatable)
End Sub
And I want to obtain the schema of the table which my resultset is pointing
Dim tabla As Data.DataTable
tabla = Me.ResultSetProductos.GetSchemaTable
But the datatable that i obtain don′t seem to have the schema that i need, for example don′t have the primary keys i have define and also don′t have any constrains......
Does anybody know the problem?
Thanks for your help
To get contraints like primary key, you can use IsUnique and IsKey property which you get in SchemaTable. To get the contraints you can get by querying the INFORMATION_SCHEMA.TABLE_CONSTRAINTS. Get more help at http://msdn2.microsoft.com/en-us/library/ms181757.aspxobtain sql server login name..
i'm updating an Access 2000 db thats linked to SQL server 2000, and want to
create separate id/passwords for each user, and create an audit trail for
data modifications.
Any suggestions for an audit trail?
How can Access obtain the sql server login for the current user to store on
table?
Thanks!"select system_user" will return the current user.
To track data modification, you either need to have history tables with
triggers on the main tables, or have a trace running. You can research both
in Books Online. Generally, people will maintain a history table with who,
when, and from what system the modification occurred.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"nycdon" wrote:
> i'm updating an Access 2000 db thats linked to SQL server 2000, and want t
o
> create separate id/passwords for each user, and create an audit trail for
> data modifications.
> Any suggestions for an audit trail?
> How can Access obtain the sql server login for the current user to store o
n
> table?
> Thanks!
>
create separate id/passwords for each user, and create an audit trail for
data modifications.
Any suggestions for an audit trail?
How can Access obtain the sql server login for the current user to store on
table?
Thanks!"select system_user" will return the current user.
To track data modification, you either need to have history tables with
triggers on the main tables, or have a trace running. You can research both
in Books Online. Generally, people will maintain a history table with who,
when, and from what system the modification occurred.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"nycdon" wrote:
> i'm updating an Access 2000 db thats linked to SQL server 2000, and want t
o
> create separate id/passwords for each user, and create an audit trail for
> data modifications.
> Any suggestions for an audit trail?
> How can Access obtain the sql server login for the current user to store o
n
> table?
> Thanks!
>
Obtain msinfo32 info + am I SAN attached ?
I like the first screen shot of the msinfo32.exe that talks about OS
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
ThanksHi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attached
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>> I like the first screen shot of the msinfo32.exe that talks about OS
>> version, physical memory,Model,etc. I dont need all the info that it
>> provides such as hardware resources,components...
>> I just need that first opening page contents
>> Also, how can I find out if my server is SAN attached or has direct
>> attached
>> storage programatically ?
>> I would prefer any TSQL way of obtaining the info
>> Thanks
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
ThanksHi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attached
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>> I like the first screen shot of the msinfo32.exe that talks about OS
>> version, physical memory,Model,etc. I dont need all the info that it
>> provides such as hardware resources,components...
>> I just need that first opening page contents
>> Also, how can I find out if my server is SAN attached or has direct
>> attached
>> storage programatically ?
>> I would prefer any TSQL way of obtaining the info
>> Thanks
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
Obtain msinfo32 info + am I SAN attached ?
I like the first screen shot of the msinfo32.exe that talks about OS
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
ThanksHi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attach
ed
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
ThanksHi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attach
ed
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
Obtain msinfo32 info + am I SAN attached ?
I like the first screen shot of the msinfo32.exe that talks about OS
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
Thanks
Hi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attached
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John
|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
version, physical memory,Model,etc. I dont need all the info that it
provides such as hardware resources,components...
I just need that first opening page contents
Also, how can I find out if my server is SAN attached or has direct attached
storage programatically ?
I would prefer any TSQL way of obtaining the info
Thanks
Hi Hassan
"Hassan" wrote:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
> Also, how can I find out if my server is SAN attached or has direct attached
> storage programatically ?
> I would prefer any TSQL way of obtaining the info
> Thanks
>
I have pointed you to the solution for this in your previous posts. It
doesn't really matter if you return more information than needed from
MSINFO32 as you can just ignore then when loading/loaded
For example:
USE TEMPDB
GO
EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
C:\temp\system.txt'
GO
-- This will create a single record with the systeminfo as content which you
can
-- pattern match on
SELECT *
FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
GO
-- Alternatively it may be easier to return each line as a separate row
CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
GO
BULK INSERT tempdb..systeminfo
FROM 'C:\temp\system.txt'
WITH
(
DATAFILETYPE = 'widechar',
ROWTERMINATOR = '\n'
)
GO
SELECT * FROM dbo.systeminfo
DROP TABLE dbo.systeminfo
John
|||Hassan (hassan@.hotmail.com) writes:
> I like the first screen shot of the msinfo32.exe that talks about OS
> version, physical memory,Model,etc. I dont need all the info that it
> provides such as hardware resources,components...
> I just need that first opening page contents
The extended stored procedure xp_msver has some of that information.
> Also, how can I find out if my server is SAN attached or has direct
> attached storage programatically ?
Beats me. :-(
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Cool. Thanks John.
Do you know how I could put those values in 2 columns for item and value ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BF35DE06-0415-41A8-A5C2-83A03CD09374@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
> I have pointed you to the solution for this in your previous posts. It
> doesn't really matter if you return more information than needed from
> MSINFO32 as you can just ignore then when loading/loaded
> For example:
> USE TEMPDB
> GO
> EXEC xp_cmdshell '"C:\Program Files\Common Files\Microsoft
> Shared\MSInfo\msinfo32.exe" /categories +ComponentsStorageDisks /report
> C:\temp\system.txt'
> GO
> -- This will create a single record with the systeminfo as content which
> you
> can
> -- pattern match on
> SELECT *
> FROM OPENROWSET(BULK N'C:\temp\system.txt', SINGLE_NCLOB) AS Sysinfo
> GO
> -- Alternatively it may be easier to return each line as a separate row
> CREATE TABLE dbo.systeminfo ( sysinfo varchar(max))
> GO
> BULK INSERT tempdb..systeminfo
> FROM 'C:\temp\system.txt'
> WITH
> (
> DATAFILETYPE = 'widechar',
> ROWTERMINATOR = '\n'
> )
> GO
> SELECT * FROM dbo.systeminfo
> DROP TABLE dbo.systeminfo
> John
Obtain machine name from Sql server instance name
does any one know how to get the machine name in the LAN by reading the SQL server instance name installed on it?
You can obtain the machine name using the system function SERVERPROPERTY('MachineName'). Using SMO, you can find the same using Server.Information.NetName .
Thanks,
Kuntal
Is it possible to retrieve list of Reporting services server that running on the LAN?
Obtain list of backup-able databases
I am currently running a nightly script which obtains the list of databases
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanksSelect * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> >
> > I am currently running a nightly script which obtains the list of
> > databases
> > from master..sysdatabases and runs DBCC & BACKUP on each of them
> > (including
> > master/model/msdb)
> >
> > I have hardcoded the where clause to not select the tempdb, and one
> > database
> > which I have set as "offline"..
> >
> > Is it possible to obtain the list of "backupable" databases a little
more
> > efficiently? The database "status" doesn't exactly do the trick, as
tempdb
> > is the same as the other normal db's.
> >
> > Basically I want to list master/msdb/model & all other ONLINE
databases...
> >
> > Many thanks
> >
> >
>|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanksSelect * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> >
> > I am currently running a nightly script which obtains the list of
> > databases
> > from master..sysdatabases and runs DBCC & BACKUP on each of them
> > (including
> > master/model/msdb)
> >
> > I have hardcoded the where clause to not select the tempdb, and one
> > database
> > which I have set as "offline"..
> >
> > Is it possible to obtain the list of "backupable" databases a little
more
> > efficiently? The database "status" doesn't exactly do the trick, as
tempdb
> > is the same as the other normal db's.
> >
> > Basically I want to list master/msdb/model & all other ONLINE
databases...
> >
> > Many thanks
> >
> >
>|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
Obtain list of backup-able databases
I am currently running a nightly script which obtains the list of databases
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanksSelect * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
tempdb[vbcol=seagreen]
databases...[vbcol=seagreen]
>|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanksSelect * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
tempdb[vbcol=seagreen]
databases...[vbcol=seagreen]
>|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
Obtain list of backup-able databases
I am currently running a nightly script which obtains the list of databases
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanks
Select * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
tempdb[vbcol=seagreen]
databases...
>
|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanks
Select * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
tempdb[vbcol=seagreen]
databases...
>
|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>
Obtain Info
Hi
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
Obtain Info
Hi
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
Obtain Info
Hi
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia Pupo
Hi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>
|||Hi,
How to Enable the Audit for Logins:-
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know
>
|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server
> know
>
|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>
|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
Obtain Info
Hi
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia PupoHi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obta
in
> major information from log file, i.e. if a user loggin to db i want know t
he
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>|||Hi,
How to Enable the Audit for Logins:-
--
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
>|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know[vbcol=seagreen]
>|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server[vbcol=seagreen]
> know
>|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia PupoHi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obta
in
> major information from log file, i.e. if a user loggin to db i want know t
he
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>|||Hi,
How to Enable the Audit for Logins:-
--
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...[vbcol=seagreen]
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
>|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
know[vbcol=seagreen]
>|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
Server[vbcol=seagreen]
> know
>|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> know
>|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>
Obtain Info
Hi
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia PupoHi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>|||Hi,
How to Enable the Audit for Logins:-
--
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
>> Hi
>> I have SQL 2000 server Sp3 on my network, i want know if is possible
>> obtain
>> major information from log file, i.e. if a user loggin to db i want know
>> the
>> time/date to login, operation excute etc.
>> Can help me please
>> Tia Pupo
>>|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> > Hi
> >
> > Logins/Logout can be logged into the SQL Event log, but what the user
> > executed, you need to use Profiler for that.
> >
> > Generally, for SOX compliance, people run Profiler to capture all the
> > events
> > and use that to report on.
> >
> > Regards
> > Mike
> >
> > "Pupo" wrote:
> >
> >> Hi
> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> >> obtain
> >> major information from log file, i.e. if a user loggin to db i want
know
> >> the
> >> time/date to login, operation excute etc.
> >> Can help me please
> >> Tia Pupo
> >>
> >>
> >>
>|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > How to Enable the Audit for Logins:-
> > --
> > SQL 2000 - Open enterprise manager -- Right click on server and select
> > properties and then choose the Security tab and
> >
> > select Audit level "ALL" and click Ok and restart SQL server service.
> >
> >
> > Operation excuted: These information can not be looged into the SQL
Server
> > error log. But you could create the trace and schedule it to
> >
> > run and log to a file. See the commands
> >
> > sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> >
> > Thanks
> >
> > Hari
> >
> > SQL Server MVP
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> > > Hi
> > >
> > > Logins/Logout can be logged into the SQL Event log, but what the user
> > > executed, you need to use Profiler for that.
> > >
> > > Generally, for SOX compliance, people run Profiler to capture all the
> > > events
> > > and use that to report on.
> > >
> > > Regards
> > > Mike
> > >
> > > "Pupo" wrote:
> > >
> > >> Hi
> > >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> > >> obtain
> > >> major information from log file, i.e. if a user loggin to db i want
> know
> > >> the
> > >> time/date to login, operation excute etc.
> > >> Can help me please
> > >> Tia Pupo
> > >>
> > >>
> > >>
> >
> >
>|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi,
>> How to Enable the Audit for Logins:-
>> --
>> SQL 2000 - Open enterprise manager -- Right click on server and select
>> properties and then choose the Security tab and
>> select Audit level "ALL" and click Ok and restart SQL server service.
>>
>> Operation excuted: These information can not be looged into the SQL
>> Server
>> error log. But you could create the trace and schedule it to
>> run and log to a file. See the commands
>> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
>> > Hi
>> >
>> > Logins/Logout can be logged into the SQL Event log, but what the user
>> > executed, you need to use Profiler for that.
>> >
>> > Generally, for SOX compliance, people run Profiler to capture all the
>> > events
>> > and use that to report on.
>> >
>> > Regards
>> > Mike
>> >
>> > "Pupo" wrote:
>> >
>> >> Hi
>> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
>> >> obtain
>> >> major information from log file, i.e. if a user loggin to db i want
> know
>> >> the
>> >> time/date to login, operation excute etc.
>> >> Can help me please
>> >> Tia Pupo
>> >>
>> >>
>> >>
>>
>|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Thanks i try it, in others newsgroup tell me about specific programm to
> > monitorate log as "log explorer" someone have use it before?
> > Tia
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> >> Hi,
> >>
> >> How to Enable the Audit for Logins:-
> >> --
> >> SQL 2000 - Open enterprise manager -- Right click on server and select
> >> properties and then choose the Security tab and
> >>
> >> select Audit level "ALL" and click Ok and restart SQL server service.
> >>
> >>
> >> Operation excuted: These information can not be looged into the SQL
> >> Server
> >> error log. But you could create the trace and schedule it to
> >>
> >> run and log to a file. See the commands
> >>
> >> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> >>
> >> Thanks
> >>
> >> Hari
> >>
> >> SQL Server MVP
> >>
> >>
> >> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> >> > Hi
> >> >
> >> > Logins/Logout can be logged into the SQL Event log, but what the user
> >> > executed, you need to use Profiler for that.
> >> >
> >> > Generally, for SOX compliance, people run Profiler to capture all the
> >> > events
> >> > and use that to report on.
> >> >
> >> > Regards
> >> > Mike
> >> >
> >> > "Pupo" wrote:
> >> >
> >> >> Hi
> >> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> >> >> obtain
> >> >> major information from log file, i.e. if a user loggin to db i want
> > know
> >> >> the
> >> >> time/date to login, operation excute etc.
> >> >> Can help me please
> >> >> Tia Pupo
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>
I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
major information from log file, i.e. if a user loggin to db i want know the
time/date to login, operation excute etc.
Can help me please
Tia PupoHi
Logins/Logout can be logged into the SQL Event log, but what the user
executed, you need to use Profiler for that.
Generally, for SOX compliance, people run Profiler to capture all the events
and use that to report on.
Regards
Mike
"Pupo" wrote:
> Hi
> I have SQL 2000 server Sp3 on my network, i want know if is possible obtain
> major information from log file, i.e. if a user loggin to db i want know the
> time/date to login, operation excute etc.
> Can help me please
> Tia Pupo
>
>|||Hi,
How to Enable the Audit for Logins:-
--
SQL 2000 - Open enterprise manager -- Right click on server and select
properties and then choose the Security tab and
select Audit level "ALL" and click Ok and restart SQL server service.
Operation excuted: These information can not be looged into the SQL Server
error log. But you could create the trace and schedule it to
run and log to a file. See the commands
sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> Hi
> Logins/Logout can be logged into the SQL Event log, but what the user
> executed, you need to use Profiler for that.
> Generally, for SOX compliance, people run Profiler to capture all the
> events
> and use that to report on.
> Regards
> Mike
> "Pupo" wrote:
>> Hi
>> I have SQL 2000 server Sp3 on my network, i want know if is possible
>> obtain
>> major information from log file, i.e. if a user loggin to db i want know
>> the
>> time/date to login, operation excute etc.
>> Can help me please
>> Tia Pupo
>>|||Thanks i try it, in others newsgroup tell me about specific programm to
monitorate log as "log explorer" someone have use it before?
Tia
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> How to Enable the Audit for Logins:-
> --
> SQL 2000 - Open enterprise manager -- Right click on server and select
> properties and then choose the Security tab and
> select Audit level "ALL" and click Ok and restart SQL server service.
>
> Operation excuted: These information can not be looged into the SQL Server
> error log. But you could create the trace and schedule it to
> run and log to a file. See the commands
> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> Thanks
> Hari
> SQL Server MVP
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> > Hi
> >
> > Logins/Logout can be logged into the SQL Event log, but what the user
> > executed, you need to use Profiler for that.
> >
> > Generally, for SOX compliance, people run Profiler to capture all the
> > events
> > and use that to report on.
> >
> > Regards
> > Mike
> >
> > "Pupo" wrote:
> >
> >> Hi
> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> >> obtain
> >> major information from log file, i.e. if a user loggin to db i want
know
> >> the
> >> time/date to login, operation excute etc.
> >> Can help me please
> >> Tia Pupo
> >>
> >>
> >>
>|||Sounds like you are asking about this product:
http://www.lumigent.com/products/le_sql.html
It works quite well.
Steve
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > How to Enable the Audit for Logins:-
> > --
> > SQL 2000 - Open enterprise manager -- Right click on server and select
> > properties and then choose the Security tab and
> >
> > select Audit level "ALL" and click Ok and restart SQL server service.
> >
> >
> > Operation excuted: These information can not be looged into the SQL
Server
> > error log. But you could create the trace and schedule it to
> >
> > run and log to a file. See the commands
> >
> > sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> >
> > Thanks
> >
> > Hari
> >
> > SQL Server MVP
> >
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> > > Hi
> > >
> > > Logins/Logout can be logged into the SQL Event log, but what the user
> > > executed, you need to use Profiler for that.
> > >
> > > Generally, for SOX compliance, people run Profiler to capture all the
> > > events
> > > and use that to report on.
> > >
> > > Regards
> > > Mike
> > >
> > > "Pupo" wrote:
> > >
> > >> Hi
> > >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> > >> obtain
> > >> major information from log file, i.e. if a user loggin to db i want
> know
> > >> the
> > >> time/date to login, operation excute etc.
> > >> Can help me please
> > >> Tia Pupo
> > >>
> > >>
> > >>
> >
> >
>|||Hi,
You can also use Log explorer from Lumigent. Great tool to read the
transaction logs. Try it out from:-
www.Lumigent.com
Thanks
Hari
SQL Server MVP
"Pupo" <123star@.libero.it> wrote in message
news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Thanks i try it, in others newsgroup tell me about specific programm to
> monitorate log as "log explorer" someone have use it before?
> Tia
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi,
>> How to Enable the Audit for Logins:-
>> --
>> SQL 2000 - Open enterprise manager -- Right click on server and select
>> properties and then choose the Security tab and
>> select Audit level "ALL" and click Ok and restart SQL server service.
>>
>> Operation excuted: These information can not be looged into the SQL
>> Server
>> error log. But you could create the trace and schedule it to
>> run and log to a file. See the commands
>> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
>> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
>> > Hi
>> >
>> > Logins/Logout can be logged into the SQL Event log, but what the user
>> > executed, you need to use Profiler for that.
>> >
>> > Generally, for SOX compliance, people run Profiler to capture all the
>> > events
>> > and use that to report on.
>> >
>> > Regards
>> > Mike
>> >
>> > "Pupo" wrote:
>> >
>> >> Hi
>> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
>> >> obtain
>> >> major information from log file, i.e. if a user loggin to db i want
> know
>> >> the
>> >> time/date to login, operation excute etc.
>> >> Can help me please
>> >> Tia Pupo
>> >>
>> >>
>> >>
>>
>|||Hi
Those log reader tools only can report on what was changed. If someone ran a
SELECT, it is not logged as non-data monification statements are not logged
into the transaction log.
SQL Server Profiler/Trace is the only tool that will report on everything
that is going in and out the server.
Regards
Mike
"Hari Prasad" wrote:
> Hi,
> You can also use Log explorer from Lumigent. Great tool to read the
> transaction logs. Try it out from:-
> www.Lumigent.com
> Thanks
> Hari
> SQL Server MVP
> "Pupo" <123star@.libero.it> wrote in message
> news:OBzC7VpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> > Thanks i try it, in others newsgroup tell me about specific programm to
> > monitorate log as "log explorer" someone have use it before?
> > Tia
> >
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23EWsVRpPFHA.3788@.tk2msftngp13.phx.gbl...
> >> Hi,
> >>
> >> How to Enable the Audit for Logins:-
> >> --
> >> SQL 2000 - Open enterprise manager -- Right click on server and select
> >> properties and then choose the Security tab and
> >>
> >> select Audit level "ALL" and click Ok and restart SQL server service.
> >>
> >>
> >> Operation excuted: These information can not be looged into the SQL
> >> Server
> >> error log. But you could create the trace and schedule it to
> >>
> >> run and log to a file. See the commands
> >>
> >> sp_trace_create, sp_trace_setevent, sp_trace_setstatus in books online.
> >>
> >> Thanks
> >>
> >> Hari
> >>
> >> SQL Server MVP
> >>
> >>
> >> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> >> news:E2A4F98E-5C4C-46B0-BE40-EDB530C6162E@.microsoft.com...
> >> > Hi
> >> >
> >> > Logins/Logout can be logged into the SQL Event log, but what the user
> >> > executed, you need to use Profiler for that.
> >> >
> >> > Generally, for SOX compliance, people run Profiler to capture all the
> >> > events
> >> > and use that to report on.
> >> >
> >> > Regards
> >> > Mike
> >> >
> >> > "Pupo" wrote:
> >> >
> >> >> Hi
> >> >> I have SQL 2000 server Sp3 on my network, i want know if is possible
> >> >> obtain
> >> >> major information from log file, i.e. if a user loggin to db i want
> > know
> >> >> the
> >> >> time/date to login, operation excute etc.
> >> >> Can help me please
> >> >> Tia Pupo
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>
>
Subscribe to:
Posts (Atom)