How to get logfile of SQL statements?

Hi,
on SQL Server Version 8.0, is it possible to get a logfile of the SQL
statements for a certain database? If not built in, is there a tool?
Thank you
AdrianI guess you want to get a trace of what SQL statements have been run against
a particular database. For that, you can use SQL Profiler (a GUI tool) or SQ
L
Trace without GUI.
Linchi
"Adrian Schmitt" wrote:

> Hi,
> on SQL Server Version 8.0, is it possible to get a logfile of the SQL
> statements for a certain database? If not built in, is there a tool?
> Thank you
> Adrian
>|||You could use the way Linchi recommended, but I suggest you execute the SQL
Trace or Profiler only when required.
Thanks
Hari
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:74E8236E-70F9-4237-B9D4-EB6C8B0D439D@.microsoft.com...[vbcol=seagreen]
>I guess you want to get a trace of what SQL statements have been run
>against
> a particular database. For that, you can use SQL Profiler (a GUI tool) or
> SQL
> Trace without GUI.
> Linchi
> "Adrian Schmitt" wrote:
>|||On 22 Jun., 18:21, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I guess you want to get a trace of what SQL statements have been run again
st
> a particular database. For that, you can use SQL Profiler (a GUI tool)
Thank you Linchi, thank you Hari.
I created a new database user in the end and assigned the
"sysadmin" server role to this user. Then I was able to log the
requests
that the client application was doing (but as a different user) with
the Profiler.
I could understand some parts of what this logging shows, like SELECT
and
UPDATE commands, because I recognized also the fields and table-names,
although this is not in the form of SQL like I have seen it before.
Can someone tell me what is the name of this language and is there a
reference
manual online?
This is just a few lines from it that I did not understand:
exec sp_cursorexecute 41, @.P1 output, @.P2 output, @.P3 output, @.P4
output, 4, 'address'
select @.P1, @.P2, @.P3, @.P4
go
exec sp_cursorfetch 180150096, 2, 1, 1
go
declare @.P1 int
set @.P1=42
declare @.P2 int
set @.P2=180150097
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=1
The long numbers that start with 180... seem like some refence to
database cursors or statements,
and statements are maybe prepared here over several lines and stuffed
with parameters like it seems?
Maybe someone can point me to some documentation?
Thank you
Adrian|||Hi
You may want to take a look into Notifications or/and DDL Triggers in the
BOL
"Adrian Schmitt" <Adrian.Schmitt@.googlemail.com> wrote in message
news:1182649654.408598.172770@.g4g2000hsf.googlegroups.com...
> On 22 Jun., 18:21, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> Thank you Linchi, thank you Hari.
> I created a new database user in the end and assigned the
> "sysadmin" server role to this user. Then I was able to log the
> requests
> that the client application was doing (but as a different user) with
> the Profiler.
> I could understand some parts of what this logging shows, like SELECT
> and
> UPDATE commands, because I recognized also the fields and table-names,
> although this is not in the form of SQL like I have seen it before.
> Can someone tell me what is the name of this language and is there a
> reference
> manual online?
> This is just a few lines from it that I did not understand:
> --
> exec sp_cursorexecute 41, @.P1 output, @.P2 output, @.P3 output, @.P4
> output, 4, 'address'
> select @.P1, @.P2, @.P3, @.P4
> go
> exec sp_cursorfetch 180150096, 2, 1, 1
> go
> declare @.P1 int
> set @.P1=42
> declare @.P2 int
> set @.P2=180150097
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=1
> --
> The long numbers that start with 180... seem like some refence to
> database cursors or statements,
> and statements are maybe prepared here over several lines and stuffed
> with parameters like it seems?
> Maybe someone can point me to some documentation?
> Thank you
> Adrian
>|||> Adrian wrote
On 24 Jun., 09:35, Uri Dimant wrote:[vbcol=seagreen]
> You may want to take a look into Notifications or/and DDL
> Triggers in the BOL
Thank you, Uri. I download Books Online from Microsoft.
The content of the Profiler-Logfile is specific to how the
connection is made, in my case ODBC.
Additionally I found these threads on the subject:
http://webservertalk.com/showpost.p...627&postcount=5
http://tek-tips.com/viewthread.cfm?qid=832355
Cheers
Adrian

How to get logfile of SQL statements?

Hi,
on SQL Server Version 8.0, is it possible to get a logfile of the SQL
statements for a certain database? If not built in, is there a tool?
Thank you
AdrianI guess you want to get a trace of what SQL statements have been run against
a particular database. For that, you can use SQL Profiler (a GUI tool) or SQL
Trace without GUI.
Linchi
"Adrian Schmitt" wrote:
> Hi,
> on SQL Server Version 8.0, is it possible to get a logfile of the SQL
> statements for a certain database? If not built in, is there a tool?
> Thank you
> Adrian
>|||You could use the way Linchi recommended, but I suggest you execute the SQL
Trace or Profiler only when required.
Thanks
Hari
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:74E8236E-70F9-4237-B9D4-EB6C8B0D439D@.microsoft.com...
>I guess you want to get a trace of what SQL statements have been run
>against
> a particular database. For that, you can use SQL Profiler (a GUI tool) or
> SQL
> Trace without GUI.
> Linchi
> "Adrian Schmitt" wrote:
>> Hi,
>> on SQL Server Version 8.0, is it possible to get a logfile of the SQL
>> statements for a certain database? If not built in, is there a tool?
>> Thank you
>> Adrian
>>|||On 22 Jun., 18:21, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> I guess you want to get a trace of what SQL statements have been run against
> a particular database. For that, you can use SQL Profiler (a GUI tool)
Thank you Linchi, thank you Hari.
I created a new database user in the end and assigned the
"sysadmin" server role to this user. Then I was able to log the
requests
that the client application was doing (but as a different user) with
the Profiler.
I could understand some parts of what this logging shows, like SELECT
and
UPDATE commands, because I recognized also the fields and table-names,
although this is not in the form of SQL like I have seen it before.
Can someone tell me what is the name of this language and is there a
reference
manual online?
This is just a few lines from it that I did not understand:
--
exec sp_cursorexecute 41, @.P1 output, @.P2 output, @.P3 output, @.P4
output, 4, 'address'
select @.P1, @.P2, @.P3, @.P4
go
exec sp_cursorfetch 180150096, 2, 1, 1
go
declare @.P1 int
set @.P1=42
declare @.P2 int
set @.P2=180150097
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=1
--
The long numbers that start with 180... seem like some refence to
database cursors or statements,
and statements are maybe prepared here over several lines and stuffed
with parameters like it seems?
Maybe someone can point me to some documentation?
Thank you
Adrian|||Hi
You may want to take a look into Notifications or/and DDL Triggers in the
BOL
"Adrian Schmitt" <Adrian.Schmitt@.googlemail.com> wrote in message
news:1182649654.408598.172770@.g4g2000hsf.googlegroups.com...
> On 22 Jun., 18:21, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>> I guess you want to get a trace of what SQL statements have been run
>> against
>> a particular database. For that, you can use SQL Profiler (a GUI tool)
> Thank you Linchi, thank you Hari.
> I created a new database user in the end and assigned the
> "sysadmin" server role to this user. Then I was able to log the
> requests
> that the client application was doing (but as a different user) with
> the Profiler.
> I could understand some parts of what this logging shows, like SELECT
> and
> UPDATE commands, because I recognized also the fields and table-names,
> although this is not in the form of SQL like I have seen it before.
> Can someone tell me what is the name of this language and is there a
> reference
> manual online?
> This is just a few lines from it that I did not understand:
> --
> exec sp_cursorexecute 41, @.P1 output, @.P2 output, @.P3 output, @.P4
> output, 4, 'address'
> select @.P1, @.P2, @.P3, @.P4
> go
> exec sp_cursorfetch 180150096, 2, 1, 1
> go
> declare @.P1 int
> set @.P1=42
> declare @.P2 int
> set @.P2=180150097
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=1
> --
> The long numbers that start with 180... seem like some refence to
> database cursors or statements,
> and statements are maybe prepared here over several lines and stuffed
> with parameters like it seems?
> Maybe someone can point me to some documentation?
> Thank you
> Adrian
>|||> Adrian wrote
> > Can someone tell me what is the name of this language
> > and is there a reference manual online?
On 24 Jun., 09:35, Uri Dimant wrote:
> You may want to take a look into Notifications or/and DDL
> Triggers in the BOL
Thank you, Uri. I download Books Online from Microsoft.
The content of the Profiler-Logfile is specific to how the
connection is made, in my case ODBC.
Additionally I found these threads on the subject:
http://dbforums.com/showpost.php?p=1324627&postcount=5
http://tek-tips.com/viewthread.cfm?qid=832355
Cheers
Adrian

How to get log shipping to work with log backups?

How exactly are you supposed to get log shipping to work
with log backups? I have the production server which
backups up logs every two hours on the hour. I have
another server which I am log shipping to. I set it up to
log ship every 55 minutes. The second time around it
failed. I don't get it how are you supposed to set this up.
?
I'm not too sure what error you received but this should work OK. Try
manually restoring the logs and then adjust the copy and load frequencies to
be every 15 minutes. This will definitely avoid any issues with backlogs. If
this doesn't work then something else is wrong in the setup and in this case
please post up any error messages , info from the monitor, info about whick
logs have been copied over etc.
Regards,
Paul Ibison
|||It says that the log sequence number is not in synch that
it needs an earlier transaction log. When I copy over the
backup of the transaction log that was made from the other
maintenance plan for transaction logs I was able to apply
it no problem.
I just want to get rid of the other maintence plan
altoghter. What I am worried about is will log shipping
truncate the log when it's done. I don't want the
transaction log to grow indefinitely.
Or if I am totally wrong in my thinking here please advise.
Thanks,
Bryan
>--Original Message--
>I'm not too sure what error you received but this should
work OK. Try
>manually restoring the logs and then adjust the copy and
load frequencies to
>be every 15 minutes. This will definitely avoid any
issues with backlogs. If
>this doesn't work then something else is wrong in the
setup and in this case
>please post up any error messages , info from the
monitor, info about whick
>logs have been copied over etc.
>Regards,
>Paul Ibison
>
>.
>
|||Bryan,
backup of the log truncates it unless NO TRUNCATE is specified, and this is
not the default on log shipping, so Yes, it will truncate. Having 2
maintenance plans to backup the logs, one of which is used for log shipping
is not something I've ever tested, but logically it looks sure to fail due
to non-contiguous LSNs. In your case I'd remove both plans, and recreate the
log-shipping one.
HTH,
Paul Ibison

How to get list of tables in a database

Hello

How I get the list of tables in a database. I'm using sql server 2000.

Thanksquery the sysobjects system table.

I don't recall off the top of my head which criteria signifies 'table', but a quick look in the table should sort that out rapidly ;-)|||Or better yet, make use of the INFORMATION_SCHEMA fields, the TABLES view in particular.

For more information, check out this recent post:view post 449573, or Books Online (as always).

Terri|||Thanks a lot!!!

I got the answer

How to get list of item in Report Server

hi,
I'm new to reporting services, and i'm using custom authorization.
I need to create a windows application which will connect to the report
server and gets list of all files and folders from the root directory and set
policies.
I want to know how to retrieve all the folders and files from root directory.
I included the reporting service component and created an object for that,
after that how i should retrieve the list of folders from the report server.
I would like to know how to resolve this, send me the way how to retrieve
either in VB or in C#.There are 2 techniques available:
1) There is a List method (List...). Call it from the root path "/" and
then recurse each to build the tree. Each item has an item type (Folder,
DataSource, Report, etc)
a. I'm going from memory, but I think it's either ListChildren or
ListItems
2) FindItems from the root path "/" and assemble from there.
a. Again, I'm going from memory.
Look to MSDN or SQL Server BOL for the methods.
BTW, you wouldn't be the same Uday that worked for a company called
PowerCerv years ago would you? I know the name is common, but I'm curious.
-Tim
"uday" <uday@.discussions.microsoft.com> wrote in message
news:D5BE2C9D-7617-42B2-B915-467F8B706948@.microsoft.com...
> hi,
> I'm new to reporting services, and i'm using custom authorization.
> I need to create a windows application which will connect to the report
> server and gets list of all files and folders from the root directory and
> set
> policies.
> I want to know how to retrieve all the folders and files from root
> directory.
> I included the reporting service component and created an object for that,
> after that how i should retrieve the list of folders from the report
> server.
> I would like to know how to resolve this, send me the way how to retrieve
> either in VB or in C#.
>
>

how to get LIST of fields in a report (designer)

Hi All,

When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?

Ideally a fields map with links to these fields.

thanks

Sonny

Hello Sonny,

If you press F4, it will open your Properties window; from here, you can select the report object you are looking for and it will select that object in your layout.

Hope this helps.

Jarret

How to get list of EventClasses in MSSQLServer2000

Hi,
I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
In MSSQL 2005 I am using built in system table like sys.trace_events I am
able to see list of events. When executing the following query.
SELECT * FROM sys.trace_events;
Is there any equal lent table available on MSSQLServer 2000. I tried a
lot, I am unable to get. Please any body help on this issue.
Thanks & Regards
-SomaSekharHi
I'm afraid you cannot get it in SQL Server 2000
sp_trace_setevent stored procedure has a parameter EventId and there is a
list of event numbers (See BOL)
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
> Hi,
> I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
> In MSSQL 2005 I am using built in system table like sys.trace_events I am
> able to see list of events. When executing the following query.
> SELECT * FROM sys.trace_events;
> Is there any equal lent table available on MSSQLServer 2000. I tried a
> lot, I am unable to get. Please any body help on this issue.
> Thanks & Regards
> -SomaSekhar|||Hi Uri,
What is the better solution for this. How to get list of EventId's and
EventClass name on SQL Server 2000. Because i am using
fn_trace_gettrable('trace file name', default), it's giving in EventClass as
a EventID. Insted of EventID i need EventClass name. How achive this in SQL
Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
sys.trace_events i am comparing both event ID's i am getiting EventClass
name. Please check this query,
--This will work on MS SQL Server 2005
SELECT TextData,trace_event_id,category_id,name
FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
EventID.trace_event_id
NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
give Ur trace file location"
What is the equallent Query in MS SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:

> Hi
> I'm afraid you cannot get it in SQL Server 2000
> sp_trace_setevent stored procedure has a parameter EventId and there is
a
> list of event numbers (See BOL)
>
>
> "SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
> news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
>
>|||Hi
Taken fro Vyas's web site
CREATE TABLE [dbo].[Events] (
[EventClass] [smallint] NOT NULL ,
[EventName] [varchar] (50) NOT NULL ,
[EventDescription] [varchar] (300) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
[dbo].[Events]([EventClass]) ON [PRIMARY]
GO
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(44,'SP:Stmt
S
tarting','SQL
statement inside a stored procedure is starting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(45,'SP:Stmt
C
ompleted','SQL
statement inside a stored procedure has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(46,'Object:
C
reated','Indicates
that an object has been created, such as for CREATE INDEX, CREATE TABLE, and
CREATE DATABASE statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(47,'Object:
D
eleted','Indicates
that an object has been deleted, such as in DROP INDEX and DROP TABLE
statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(48,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(49,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
TRANSACTION statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(51,'Scan:St
a
rted','Indicates
when a table or index scan has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(52,'Scan:St
o
pped','Indicates
when a table or index scan has stopped.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(53,'CursorO
p
en','Indicates
when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(54,'Transac
t
ion
Log','Tracks when transactions are written to the transaction log.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
Warning','Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a buffer
partition has reverted to an alternate plan. This can occur because of
recursion depth, data skew, trace flags, or bit counting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(56,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(57,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(58,'Auto Upd
ate
Stats','Indicates an automatic updating of index statistics has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(59,'Lock:De
a
dlock
Chain','Produced for each of the events leading up to the deadlock.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(60,'Lock:Es
c
alation','Indicates
that a finer-grained lock has been converted to a coarser-grained lock (for
example, a row lock escalated or converted to a page lock).')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
Errors','Indicates that an OLE DB error has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(62,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(63,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(64,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(65,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(66,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(67,'Executi
o
n
Warnings','Indicates any warnings that occurred during the execution of a
SQL Server statement or stored procedure.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(68,'Executi
o
n
Plan','Displays the plan tree of the Transact-SQL statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
Warnings','Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(70,'CursorP
r
epare','Indicates
when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(71,'Prepare
SQL','ODBC,
OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
for use.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(72,'Exec Pre
pared
SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(73,'Unprepa
r
e
SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
Transact-SQL statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(74,'CursorE
x
ecute','A
cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(75,'CursorR
e
compile','A
cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
recompiled either directly or due to a schema change.Triggered for ANSI and
non-ANSI cursors.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(76,'CursorI
m
plicitConversion','A
cursor on a Transact-SQL statement is converted by SQL Server from one type
to another.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(77,'CursorU
n
prepare','A
prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC,
OLE DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(78,'CursorC
l
ose','A
cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is closed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(79,'Missing
Column
Statistics','Column statistics that could have been useful for the optimizer
are not available.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(80,'Missing
Join
Predicate','Query that has no join predicate is being executed. This could
result in a long-running query.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(81,'Server M
emory
Change','Microsoft SQL Server memory usage has increased or decreased by
either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever
is greater.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(82,'User Con
figurable
(0)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(83,'User Con
figurable
(1)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(84,'User Con
figurable
(2)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(85,'User Con
figurable
(3)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(86,'User Con
figurable
(4)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(87,'User Con
figurable
(5)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(88,'User Con
figurable
(6)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(89,'User Con
figurable
(7)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(90,'User Con
figurable
(8)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(91,'User Con
figurable
(9)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(92,'Data Fil
e Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(93,'Log File
Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(94,'Data Fil
e Auto
Shrink','Indicates that a data file was shrunk automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(95,'Log File
Auto
Shrink','Indicates that a log file was shrunk automatically by the server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(96,'Show Pla
n
Text','Displays the query plan tree of the SQL statement from the query
optimizer.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(97,'Show Pla
n
ALL','Displays the query plan with full compile-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(98,'Show Pla
n
Statistics','Displays the query plan with full run-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(99,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(100,'RPC Out
put
Parameter','Produces output values of the parameters for every RPC.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(101,'Reserv
e
d','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(102,'Audit S
tatement
GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(103,'Audit O
bject
GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(104,'Audit A
dd/Drop
Login','Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(105,'Audit L
ogin
GDR','Occurs when a Microsoft Windows® login right is added or removed;
for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(106,'Audit L
ogin Change
Property','Occurs when a property of a login, except passwords, is modified;
for sp_defaultdb and sp_defaultlanguage.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(107,'Audit L
ogin Change
Password','Occurs when a SQL Server login password is changed.Passwords are
not recorded.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(108,'Audit A
dd Login to
Server Role','Occurs when a login is added or removed from a fixed server
role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(109,'Audit A
dd DB
User','Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(110,'Audit A
dd Member to
DB','Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
sp_changegroup.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(111,'Audit A
dd/Drop
Role','Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(112,'App Rol
e Pass
Change','Occurs when a password of an application role is changed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(113,'Audit S
tatement
Permission','Occurs when a statement permission (such as CREATE TABLE) is
used.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(114,'Audit O
bject
Permission','Occurs when an object permission (such as SELECT) is used, both
successfully or unsuccessfully.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(116,'Audit D
BCC','Occurs
when DBCC commands are issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(117,'Audit C
hange
Audit','Occurs when audit trace modifications are made.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(118,'Audit O
bject
Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
are issued.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(0,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(1,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(2,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(3,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(4,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(5,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(6,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(7,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(8,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(9,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(10,'RPC:Com
p
leted','Occurs
when a remote procedure call (RPC) has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(11,'RPC:Sta
r
ting','Occurs
when an RPC has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(12,'SQL:Bat
c
hCompleted','Occurs
when a Transact-SQL batch has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(13,'SQL:Bat
c
hStarting','Occurs
when a Transact-SQL batch has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(14,'Login',
'
Occurs when
a user successfully logs in to SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(15,'Logout'
,
'Occurs when
a user logs out of SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(16,'Attenti
o
n','Occurs
when attention events, such as client-interrupt requests or broken client
connections, happen.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(17,'Existin
g
Connection','Detects
all activity by users connected to SQL Server before the trace started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(18,'Service
C
ontrol','Occurs
when the SQL Server service state is modified.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(19,'DTCTran
s
action','Tracks
Microsoft Distributed Transaction Coordinator (MS DTC) coordinated
transactions between two or more databases.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(20,'Login
Failed','Indicates that a login attempt to SQL Server from a client
failed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(21,'EventLo
g
','Indicates
that events have been logged in the Microsoft Windows NT® application
log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(22,'ErrorLo
g
','Indicates
that error events have been logged in the SQL Server error log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(23,'Lock:Re
l
eased','Indicates
that a lock on a resource, such as a page, has been released.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(24,'Lock:Ac
q
uired','Indicates
acquisition of a lock on a resource, such as a data page.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(25,'Lock:De
a
dlock','Indicates
that two concurrent transactions have deadlocked each other by trying to
obtain incompatible locks on resources the other transaction owns.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(26,'Lock:Ca
n
cel','Indicates
that the acquisition of a lock on a resource has been canceled (for example,
due to a deadlock).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(27,'Lock:Ti
m
eout','Indicates
that a request for a lock on a resource, such as a page, has timed out due
to another transaction holding a blocking lock on the required resource.
Time-out is determined by the @.@.LOCK_TIMEOUT function, and can be set with
the SET LOCK_TIMEOUT statement.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(28,'DOP Even
t','Occurs
before a SELECT, INSERT, or UPDATE statement is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(29,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(30,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(31,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(32,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(33,'Excepti
o
n','Indicates
that an exception has occurred in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(34,'SP:Cach
e
Miss','Indicates
when a stored procedure is not found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(35,'SP:Cach
e
Insert','Indicates
when an item is inserted into the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(36,'SP:Cach
e
Remove','Indicates
when an item is removed from the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(37,'SP:Reco
m
pile','Indicates
that a stored procedure was recompiled.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(38,'SP:Cach
e
Hit','Indicates
when a stored procedure is found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(39,'SP:Exec
C
ontextHit','Indicates
when the execution version of a stored procedure has been found in the
procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(40,'SQL:Stm
t
Starting','Occurs
when the Transact-SQL statement has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(41,'SQL:Stm
t
Completed','Occurs
when the Transact-SQL statement has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(42,'SP:Star
t
ing','Indicates
when the stored procedure has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(43,'SP:Comp
l
eted','Indicates
when the stored procedure has completed.')
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:1F3FC9E1-32F3-4B9E-A2A4-1C3FF2B30588@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> What is the better solution for this. How to get list of EventId's and
> EventClass name on SQL Server 2000. Because i am using
> fn_trace_gettrable('trace file name', default), it's giving in EventClass
> as
> a EventID. Insted of EventID i need EventClass name. How achive this in
> SQL
> Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
> sys.trace_events i am comparing both event ID's i am getiting EventClass
> name. Please check this query,
> --This will work on MS SQL Server 2005
> SELECT TextData,trace_event_id,category_id,name
> FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
> INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
> EventID.trace_event_id
> NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
> give Ur trace file location"
> What is the equallent Query in MS SQL Server 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>|||Hi Uri,
Thank You very much. I will try this, but i want to know one thig with
out using this procedure any system tables is available on SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:

> Hi
> Taken fro Vyas's web site
> CREATE TABLE [dbo].[Events] (
> [EventClass] [smallint] NOT NULL ,
> [EventName] [varchar] (50) NOT NULL ,
> [EventDescription] [varchar] (300) NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
> [dbo].[Events]([EventClass]) ON [PRIMARY]
> GO
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(44,'SP:Stm
tStarting','SQL
> statement inside a stored procedure is starting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(45,'SP:Stm
tCompleted','SQL
> statement inside a stored procedure has completed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(46,'Object
:Created','Indicates
> that an object has been created, such as for CREATE INDEX, CREATE TABLE, a
nd
> CREATE DATABASE statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(47,'Object
:Deleted','Indicates
> that an object has been deleted, such as in DROP INDEX and DROP TABLE
> statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(48,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(49,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
> Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
> TRANSACTION statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(51,'Scan:S
tarted','Indicates
> when a table or index scan has started.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(52,'Scan:S
topped','Indicates
> when a table or index scan has stopped.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(53,'Cursor
Open','Indicates
> when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(54,'Transa
ction
> Log','Tracks when transactions are written to the transaction log.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
> Warning','Indicates that a hashing operation (for example, hash join, hash
> aggregate, hash union, and hash distinct) that is not processing on a buff
er
> partition has reverted to an alternate plan. This can occur because of
> recursion depth, data skew, trace flags, or bit counting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(56,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(57,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(58,'Auto U
pdate
> Stats','Indicates an automatic updating of index statistics has occurred.'
)
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(59,'Lock:D
eadlock
> Chain','Produced for each of the events leading up to the deadlock.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(60,'Lock:E
scalation','Indicates
> that a finer-grained lock has been converted to a coarser-grained lock (fo
r
> example, a row lock escalated or converted to a page lock).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
> Errors','Indicates that an OLE DB error has occurred.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(62,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(63,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(64,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(65,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(66,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(67,'Execut
ion
> Warnings','Indicates any warnings that occurred during the execution of a
> SQL Server statement or stored procedure.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(68,'Execut
ion
> Plan','Displays the plan tree of the Transact-SQL statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
> Warnings','Indicates sort operations that do not fit into memory. Does not
> include sort operations involving the creating of indexes; only sort
> operations within a query (such as an ORDER BY clause used in a SELECT
> statement).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(70,'Cursor
Prepare','Indicates
> when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
> DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(71,'Prepar
e SQL','ODBC,
> OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
> for use.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(72,'Exec P
repared
> SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
> statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(73,'Unprep
are
> SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
> Transact-SQL statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(74,'Cursor
Execute','A
> cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(75,'Cursor
Recompile','A
> cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
> recompiled either directly or due to a schema change.Triggered for ANSI an
d
> non-ANSI cursors.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(76,'Cursor
ImplicitConversion','A
> cursor on a Transact-SQL statement is converted by SQL Server from one typ
e
> to another.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(77,'Cursor
Unprepare','A
> prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODB
C,
> OLE DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(78,'Cursor
Close','A
> cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is closed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(79,'Missin
g Column
> Statistics','Column statistics that could have been useful for the optimiz
er
> are not available.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(80,'Missin
g Join
> Predicate','Query that has no join predicate is being executed. This could
> result in a long-running query.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(81,'Server
Memory
> Change','Microsoft SQL Server memory usage has increased or decreased by
> either 1 megabyte (MB) or 5 percent of the maximum server memory, whicheve
r
> is greater.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(82,'User C
onfigurable
> (0)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(83,'User C
onfigurable
> (1)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(84,'User C
onfigurable
> (2)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(85,'User C
onfigurable
> (3)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(86,'User C
onfigurable
> (4)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(87,'User C
onfigurable
> (5)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(88,'User C
onfigurable
> (6)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(89,'User C
onfigurable
> (7)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(90,'User C
onfigurable
> (8)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(91,'User C
onfigurable
> (9)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(92,'Data F
ile Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(93,'Log Fi
le Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(94,'Data F
ile Auto
> Shrink','Indicates that a data file was shrunk automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(95,'Log Fi
le Auto
> Shrink','Indicates that a log file was shrunk automatically by the server.
')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(96,'Show P
lan
> Text','Displays the query plan tree of the SQL statement from the query
> optimizer.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(97,'Show P
lan
> ALL','Displays the query plan with full compile-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(98,'Show P
lan
> Statistics','Displays the query plan with full run-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(99,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(100,'RPC O
utput
> Parameter','Produces output values of the parameters for every RPC.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(101,'Reser
ved','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(102,'Audit
Statement
> GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission i
s
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(103,'Audit
Object
> GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(104,'Audit
Add/Drop
> Login','Occurs when a SQL Server login is added or removed; for sp_addlogi
n
> and sp_droplogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(105,'Audit
Login
> GDR','Occurs when a Microsoft Windows? login right is added or removed;
> for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(106,'Audit
Login Change
> Property','Occurs when a property of a login, except passwords, is modifie
d;
> for sp_defaultdb and sp_defaultlanguage.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(107,'Audit
Login Change
> Password','Occurs when a SQL Server login password is changed.Passwords ar
e
> not recorded.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(108,'Audit
Add Login to
> Server Role','Occurs when a login is added or removed from a fixed server
> role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(109,'Audit
Add DB
> User','Occurs when a login is added or removed as a database user (Windows
> or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
> sp_adduser, and sp_dropuser.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(110,'Audit
Add Member to
> DB','Occurs when a login is added or removed as a database user (fixed or
> user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
> sp_changegroup.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(111,'Audit
Add/Drop
> Role','Occurs when a login is added or removed as a database user to a
> database; for sp_addrole and sp_droprole.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(112,'App R
ole Pass
> Change','Occurs when a password of an application role is changed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(113,'Audit
Statement
> Permission','Occurs when a statement permission (such as CREATE TABLE) is
> used.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(114,'Audit
Object
> Permission','Occurs when an object permission (such as SELECT) is used, bo
th
> successfully or unsuccessfully.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
> Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(116,'Audit
DBCC','Occurs
> when DBCC commands are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(117,'Audit
Change
> Audit','Occurs when audit trace modifications are made.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(118,'Audit
Object
> Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
> are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(0,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(1,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(2,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(3,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(4,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(5,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(6,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(7,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(8,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(9,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(10,'RPC:Co
mpleted','Occurs
> when a remote procedure call (RPC) has completed.')
> INSERT INTO [events]|||Soma
I'm not aware of it
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:695531DD-50FE-4A48-9578-B070055C0DD3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> Thank You very much. I will try this, but i want to know one thig with
> out using this procedure any system tables is available on SQL Server
> 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>

How to get list of database names and list of table names?

I want to get list of database names under a server,
and list of table names under a database.
How can I do this with query?
Probably a better way of doing this through InformationSchema but...
SELECT Name from master.dbo.sysdatabases
In each database
SELECT Name from sysobjects WHERE Type = 'U'
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
> I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>
|||-- All databases
SELECT name
FROM master..sysdatabases
-- All tables in a database
select name, *
FROM Northwind..sysobjects
where xtype = 'U'
Greetings, John
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
> I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>
|||You can also use the ANSI-standard INFORMATION_SCHEMA views:
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
>I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>

How to get list of database names and list of table names?

I want to get list of database names under a server,
and list of table names under a database.
How can I do this with query?Probably a better way of doing this through InformationSchema but...
SELECT Name from master.dbo.sysdatabases
In each database
SELECT Name from sysobjects WHERE Type = 'U'
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
> I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>|||-- All databases
SELECT name
FROM master..sysdatabases
-- All tables in a database
select name, *
FROM Northwind..sysobjects
where xtype = 'U'
Greetings, John
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
> I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>|||You can also use the ANSI-standard INFORMATION_SCHEMA views:
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GoodMorningSky" <sky@.forever21.com> wrote in message
news:%23oKl4ZJrEHA.516@.TK2MSFTNGP09.phx.gbl...
>I want to get list of database names under a server,
> and list of table names under a database.
> How can I do this with query?
>

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
Tram
Msdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegr oups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
TramMsdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegroups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

How to get list of backup log files from msdb of standby server?

How do we retrieve log file names of primary backup from msdb of
standby database?
In case of primary server unavailable, I need to get the logfile names
from standby server for disater recovery test.
Thanks in advance
TramMsdb..backupfile gives you a list of data/log files that are backed up.
For logshipping, take a look at msdb..log_shipping_plan_databases.
--
-oj
"tram" <tram_e@.hotmail.com> wrote in message
news:1120079449.879078.169120@.o13g2000cwo.googlegroups.com...
> How do we retrieve log file names of primary backup from msdb of
> standby database?
> In case of primary server unavailable, I need to get the logfile names
> from standby server for disater recovery test.
> Thanks in advance
> Tram
>

How to get list of all Sql servers on network

Hi,
Is there any function/API in vb to retrieve all the sql servers that are avaiable on a network? Basically, i wanted to implement this facility in my application where users can select a server from list of all avaiable servers on LAN.

Thanks!
Asif.Net, Im having trouble with a couple of the TYPES that are used in the functions.

Have you got this figured out in .NET yet?

Please let me know if you have a resolution.

Thanks,
Malcolm Phillips
Malcolm.Phillps@.prgx.com|||If you get GFI lanscan you'll find there's a script there that enumerates SQL Servers on the network. Its basically VB script but I don;'t have it here at home.

To do it in asp.net I think you'd need to import the SQLDMO namespace|||How do I import the SQLDMO namespace? Please|||You add the Microsoft SQLDMO Object Library as a (COM) Reference to your project, then you add

<%@. Import Namespace="SQLDMO"%>

to your ASP.NET page.

Terri|||Take a look at http://www.extremeexperts.com/sql/faq/SQLDMO-ListServers.aspx for an example ... This works in VB also ...|||Dim mDMOApp As New SQLDMO.Application Dim mNames As SQLDMO.NameList Dim t As Integer mDMOApp = New SQLDMO.Application mNames = mDMOApp.ListAvailableSQLServers() lstServers.Items.Clear() For t = 1 To mNames.Count lstServers.Items.Add(mNames.Item(t)) Next

How to get list (text) of all tables and columns?

Is there a way using MS SQL Server and Enterprise Manager to get a text
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?

--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal

It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.

Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?

Hello,

To get a list of user tables use this query:

select name from ssysobjects where type = 'u'

And for a list of columns related for all objects (tables,views,sps)

select name, object_name(id) from sysindexes

The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.

Regards,
-Manoj

how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.

how to get latest file

how to get the name of the latest file of a particular directory
thru query
i.e
i have files in a directory
1130am.txt
11.45am.txt
1200am.txt
my query should return 1200am.txtOriginally posted by vrsrinivas
how to get the name of the latest file of a particular directory
thru query

i.e
i have files in a directory

1130am.txt
11.45am.txt
1200am.txt

my query should return 1200am.txt

Assuming that all your file names are prefixed with a time stamp then you should be able to use the MAX() function to return the desired record.

create table #test (theFileName varchar(20))

insert into #test (theFileName) values ('1130am.txt')
insert into #test (theFileName) values ('11.45am.txt')
insert into #test (theFileName) values ('1200am.txt')

select max(theFileName) from #test|||I'm thinking the hard part is getting the info from the directory...

Delete From Ledger_Folder

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

Delete From Ledger_Folder_Parsed

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

how to get last year revenue up to a specific month

Please help

i have a office web component pivot table that pulls revenue for 2005 ([Measures].[Rev],[Date].[2005]). This shows all revenue to May 2005. I also want to display all revenue for 2004 (up to may).

I was trying to hard code the month but was unsuccessful. Here's what I tried:

([Measures].[Rev], ([Date].[2004].[Quarter 1].[January],[Date].[2004].[Quarter 1].[February],[Date].[2004].[Quarter 1].[March],[Date].[2004].[Quarter 2].[April],[Date].[2004].[Quarter 2].[May]))

..

|||You have to make a calculated member.

Try this
MEMBER [Measures].[Rev To May 2004] AS ' SUM({[Date].[2004].[January]:[Date].[2004].[May]}, [Measures].[Rev]) '
If you want is to always show the same period you should make something like this.
MEMBER [Measures].[Rev To May 2004] AS ' SUM({PARALLELPERIOD([Date].[Year], 1, [Date].CURRENTMEMBER.FIRSTCHILD.FIRSTCHILD : PARALLELPERIOD([Date].[Year], 1, [Date].CURRENTMEMBER}, [Measures].[Rev]) '

(This works if your dimension has these levels year, quarter, month, day
I hope it helps you.

how to get last updated date

Hi,
I am looking for a function (or something else) that shows me, when a row in MS SQL Server was updated the last time. is it possible?
thanks
deviant69How about trying out DBCC SHOW_STATISTICS, check this link for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_6jlf.asp|||thank you. i am going to try it.
deviant69

How to get last records in grouped query.

Hi,
I want to create query where I could group records by quarters, and get
the last record in each group.

e.g
Create Table MyTable
(
Value [float],
date[datetime]
)
Insert into MyTable (value, [date]) values (100, '1-1-2000')
Insert into MyTable (value, [date]) values (110, '1-2-2000')
Insert into MyTable (value, [date]) values (120, '1-3-2000')
Insert into MyTable (value, [date]) values (130, '1-4-2000')
Insert into MyTable (value, [date]) values (140, '1-5-2000')
Insert into MyTable (value, [date]) values (150, '1-6-2000')
Insert into MyTable (value, [date]) values (160, '1-7-2000')

Now I would like to get this data grouped by quarter, and get the last
value from each quarter. So here I would like to get result like this
(120, q1 -2000)
(150, q2 -2000)
(160, q3 -2000)
I know how to create aggregate functions but I have problem with getting
that last record from each group.

*** Sent via Developersdex http://www.developersdex.com ***I have solution unless somebody has better one:
Create temp table with dates only, grouped by quarter and do right join
query on MyTable.

Create Table #temp1
(
IdTemp1 int not null Primary key identity ,
[date] datetime
)
Insert into #temp1
(
[date]
)

Select
Max(date)
from MyTable
group by Year([date]), DatePart (quarter,[date])
order by Year([date]), DatePart (quarter,[date])

select * from MyTable right join #temp1 on mytable.date = #temp1.date

*** Sent via Developersdex http://www.developersdex.com ***|||SELECT A.Value, B.Yr, B.Qtr
FROM MyTable as A
JOIN (SELECT Yr = datepart(year, date),
Qtr = datepart(quarter, date),
max(date) as LastDate
FROM MyTable
GROUP BY datepart(year, date),
datepart(quarter, date)) as B
ON A.date = B.LastDate

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopawrote:

Quote:

Originally Posted by

>Hi,
>I want to create query where I could group records by quarters, and get
>the last record in each group.
>
>e.g
>Create Table MyTable
>(
>Value [float],
>date[datetime]
>)
>Insert into MyTable (value, [date]) values (100, '1-1-2000')
>Insert into MyTable (value, [date]) values (110, '1-2-2000')
>Insert into MyTable (value, [date]) values (120, '1-3-2000')
>Insert into MyTable (value, [date]) values (130, '1-4-2000')
>Insert into MyTable (value, [date]) values (140, '1-5-2000')
>Insert into MyTable (value, [date]) values (150, '1-6-2000')
>Insert into MyTable (value, [date]) values (160, '1-7-2000')
>
>Now I would like to get this data grouped by quarter, and get the last
>value from each quarter. So here I would like to get result like this
>(120, q1 -2000)
>(150, q2 -2000)
>(160, q3 -2000)
>I know how to create aggregate functions but I have problem with getting
>that last record from each group.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

How to get last record ?

Dear all,
I am writing a store procedure which should return the last entry whcih has
been written in a database table.
That store procedure will always return the last recorded entry from a table
How to do that ?
regards
sergeHi Serge,
What is the "last" record in your case ?
SQL Server doesn=B4t care about the order of data unless you specify an
Order in a Select query or you can determine the last entry via an
identity column or a column which store the modified date.
HTH, Jens Suessmeyer.|||You need to add datetime column and insert the value when you add a record.
Then you can select the most recent record in the table.
something like
select top 1 <column list>
from table
order by Time_inserted DESC
Thats offcourse if you mean 'last' by time of insert. If you want to handle
updates ('LastChanged') read about timestamp in the BOL.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
> Dear all,
> I am writing a store procedure which should return the last entry whcih
> has
> been written in a database table.
> That store procedure will always return the last recorded entry from a
> table
> How to do that ?
> regards
> serge|||If you don't record the information about which row was the last then
SQL Server won't do it for you. The answer is something like:
SELECT ,,,
FROM your_table
WHERE created_date =
(SELECT MAX(created_date)
FROM your_table) ;
David Portas
SQL Server MVP
--|||Hi Jens,
nased on that, I have a column in my database table Identified as START_TIME
which is of type DataTime.
Based on that column, the "last" record will be the one who have the biggest
DateTIme value.
any tip ?
"Jens" wrote:

> Hi Serge,
> What is the "last" record in your case ?
> SQL Server doesn′t care about the order of data unless you specify an
> Order in a Select query or you can determine the last entry via an
> identity column or a column which store the modified date.
> HTH, Jens Suessmeyer.
>|||Thnaks it works
"MC" wrote:

> You need to add datetime column and insert the value when you add a record
.
> Then you can select the most recent record in the table.
> something like
> select top 1 <column list>
> from table
> order by Time_inserted DESC
> Thats offcourse if you mean 'last' by time of insert. If you want to handl
e
> updates ('LastChanged') read about timestamp in the BOL.
> MC
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
>
>

How to get Last record

Hi all
How to get last record
when i group with date i am getting number of records but in those records need to get last record
Is there any function like this
i am working with Crstal reports 8.5
Thanks a lotCreate a formula having this code

if NextIsNull({Field})=True then
{Field}|||Thanks

How to get last processed date of stored procedure

Hello everybody
Help me to get the last processed date of a stored procedure.The last time it was invoked, or the last time the code was altered?|||... and to short cut a bit.

Invocation cannot be established after the event - you would need to log the execution somewhere or be running a trace.
Alteration only shows up in SS 2005 - SS 2000 only has a creation date so if you altered rather than drop\ created you are out of luck.|||The last time it was invoked, or the last time the code was altered?

i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?|||i just want the date on which the stored procedure was executed and where the lastexcuted date and other details are stored?

one more doubt plz

1) the date on which the stored procedure was excuted last time
2)i want to know whether the stored procedure was excuted successfully or end with failure|||As Pootle Flump mentioned, you will have to add that audit trail detail to the code of your stored procedure. SQL Server (nor any other RDBMS that I know of) will keep this sort of information.|||I create my own logging for all stroed procedures...comes in very handy

Just call this...get the datetime on the way in, and the last datetime on the way out

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_LogProcCalls]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_LogProcCalls]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_LogProcCalls] (
@.SprocName sysname
, @.TranStart datetime
, @.TranEnd datetime
, @.APPUSER char(30)
, @.Rows int
, @.Err int
, @.Paramters varchar(255)
, @.rc int OUTPUT)
AS
SET NOCOUNT ON
--
-- Enterprise Solutions
--
-- File: \\paerscbvd0014\Succession\Procedures
-- Date: 01/04/2006
-- Author: Brett Kaiser
-- Server: paerscbvd0014
-- Database: Succession
-- Login: sa
-- Description: This Procedure will log all procedures executed in a database
--
--
-- The stream will do the following:
--
-- '1. Function...

--
-- Tables Used: Sproc_Log
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------------------------------
-- Sproc_Log 0 0 KB 0 KB 0 KB 0 KB
--
-- sp_spaceused Sproc_Log

--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 01/01/2006 1. Initial release
--
--
--

Declare @.error int, @.RowCount int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int

BEGIN TRAN
DECLARE @.LogStart datetime
SELECT @.rc = 0, @.LogStart = GetDate()
IF (SELECT @.@.TRANCOUNT) <> 1
BEGIN
SELECT @.Error_Loc = 1
, @.Error_Message = 'The logging procedure must be executed outside of any transaction. @.@.TRANSCOUNT='
+ CONVERT(varchar(5),@.@.TRANCOUNT)
, @.Error_Type = 50002, @.rc = -6661
GOTO usp_LogProcCalls_Error
END

INSERT INTO Sproc_Log (
[SprocName]
, [TranStart]
, [TranEnd]
, [APP_USER]
, [LogStart]
, [LogEnd]
, [Rows]
, [Err]
, [Paramters])
SELECT
@.SprocName
, @.TranStart
, @.TranEnd
, @.APPUSER
, @.LogStart
, GetDate()
, @.Rows
, @.Err
, @.Paramters

Select @.RowCount = @.@.ROWCOUNT, @.error = @.@.error

IF @.error <> 0
BEGIN
SELECT @.Error_Loc = 2, @.Error_Type = 50001, @.rc = -6662
GOTO usp_LogProcCalls_Error
END

IF @.RowCount <> 1
BEGIN
SELECT @.Error_Loc = 3
, @.Error_Message = 'Expected 1 row to be inserted in to the sproc log. Actual Number inserted = '
+ CONVERT(varchar(5),@.RowCount)
, @.Error_Type = 50002, @.rc = -6663
GOTO usp_LogProcCalls_Error
END

COMMIT TRAN

usp_LogProcCalls_Exit:

-- Place any house keeping procedures here like...

--Set ansi_warnings ON
SET NOCOUNT OFF

RETURN

usp_LogProcCalls_Error:

Rollback TRAN

If @.Error_Type = 50001
BEGIN

Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
-- + ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error)
END

If @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

RAISERROR @.Error_Type @.Error_Message

GOTO usp_LogProcCalls_Exit

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO