I need to perform a 3 table join but my sql is a little rusty and I was never that good at Joins anyway!
I'll try and explain the basic DB layout. It's for a very simple forum board I'm making, I have 3 tables at the moment, one contains the messages which belong to a topics, which have their own table. And the topics all belong to a table listing all the forums.
tblThread
threadID PK
topicID FK
Author
Message
MessageDate
tblTopic
topicID PK
forumID FK
Subject
numViews
tblForum
forumID PK
forumName
forumDescription
forumOwner
What I'm trying to achieve is to get the number threads belonging to a forum.
So far my attempt at an sql query looks like this:
SELECT COUNT('tblthread.threadID') AS postsCount
FROM tblThread
INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
but that's missing an operator somewhere. Anyone know where i'm going wrong?
Thanks,
RichardSELECT COUNT('tblthread.threadID') AS postsCount
There shouldn't be apostrophes around the column name.|||your statement seems be fine. what database server do you use? there might be some syntax problem (INNER JOIN or something) try this one, but basically that's the same:
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1|||There shouldn't be apostrophes around the column name.
actually this shouldn't be issue. you can put whatever as count() argument. it can be column name, *, or some constant such as 1 or 'XXX'. in this case it's constant string which shouldn't affect result.|||True, I stand corrected.|||Hi Guys,
Thanks for the help. I'm using MS Access as the DB. I tried your suggestion madafaka but I'm still getting the same error:
"Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause."
Is this an error specific to access?
Thanks.|||daffy_dowden,
I created all 3 tables in MS Access (I think it's 2000 version)
I created Query (Create Query in Design view) using (SQL View)
simply: pasted code posted before
your code returned exactly the same error as you described. I don't know why, but what you can expect from MS Access :-)
Then I used (Design view) and it generated this code, which works fine:
SELECT count(*)
FROM tblForum
INNER JOIN (tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID) ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1;
code I posted before worked fine, so I don't know why you received an error
SELECT COUNT(*) AS postsCount
FROM tblThread, tblTopic, tblForum
WHERE tblThread.topicID = tblTopic.topicID
AND tblForum.forumID = tblTopic.forumID
AND tblForum.forumID=1;|||I guess Access joins tables step by step : tblThread join tblTopic, and then join tblForum
SELECT COUNT('tblthread.threadID') as postsCount
FROM
(tblThread INNER JOIN tblTopic ON tblThread.topicID = tblTopic.topicID)
INNER JOIN tblForum ON tblForum.forumID = tblTopic.forumID
WHERE tblForum.forumID=1
Showing posts with label basic. Show all posts
Showing posts with label basic. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
3 Basic SQL 2000 Profiler Questions
1. Is it possible to capture only those events that are associated with a
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this with
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I think
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 =
spcompleted) stored soewhere in SQL Server?
Thanks
Dave
Hi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 =
> spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>
|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this with
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I think
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 =
spcompleted) stored soewhere in SQL Server?
Thanks
Dave
Hi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 =
> spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>
|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
3 Basic SQL 2000 Profiler Questions
1. Is it possible to capture only those events that are associated with a
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this with
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I think
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 = spcompleted) stored soewhere in SQL Server?
Thanks
DaveHi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 => spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this with
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I think
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 = spcompleted) stored soewhere in SQL Server?
Thanks
DaveHi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 => spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
3 Basic SQL 2000 Profiler Questions
1. Is it possible to capture only those events that are associated with a
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this wit
h
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I thin
k
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 =
spcompleted) stored soewhere in SQL Server?
Thanks
DaveHi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 =
> spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
particular table? When I try to filter on object name or id it appears to
have no effect.
2. I am trying to capture all stored procs calls without redundancy (e.g.,
so that the same call is not recorded twice). It apprears I can do this wit
h
just the SP:Completed event. But should I also use the RPC:Completed event?
In Query Analyzer SP:Completed works fine. But on the production box I thin
k
I probably need RPC:Completed. Can anyone give me an example that
distinguishes between these two events?
3. Is there a list of event codes and their descriptions (i.e., 43 =
spcompleted) stored soewhere in SQL Server?
Thanks
DaveHi Dave
1. Filtering on tables seems to be very problematic in SQL 2000 Profiler.
The best I usually can do is to filter on the table name in the text field
2. I'm not sure exactly what you mean by 'without redundancy'. When DO you
see the same call recorded twice?
3. No, this information is not stored in SQL Server 2000, but SQL 2005 does
have one of its new DMVs with this info. The mapping is in the
sp_trace_setevent documentation and you can take that info and build a
lookup table of your own, or, you can use this lookup table that I already
created:
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:3FFDDDC7-ADA7-4841-BFCE-03F8626B05D4@.microsoft.com...
> 1. Is it possible to capture only those events that are associated with a
> particular table? When I try to filter on object name or id it appears to
> have no effect.
> 2. I am trying to capture all stored procs calls without redundancy (e.g.,
> so that the same call is not recorded twice). It apprears I can do this
> with
> just the SP:Completed event. But should I also use the RPC:Completed
> event?
> In Query Analyzer SP:Completed works fine. But on the production box I
> think
> I probably need RPC:Completed. Can anyone give me an example that
> distinguishes between these two events?
> 3. Is there a list of event codes and their descriptions (i.e., 43 =
> spcompleted) stored soewhere in SQL Server?
> Thanks
> Dave
>|||Thank you very much Kalen.
One point of clarification. What type of stored proc calls will show up
under RPC:Completed vs SP:Completed? Query Analyzer calls appaer to be
priced up under SP:Completed. Would my middle tier apps be using
RPC:Completed to call?
Thursday, March 8, 2012
2147217887 Error when sending to SQL 2005
I have a Visual Basic 6.0 app that I developed initially using MS SQL Server
7, then MS SQL
Server 2000 and now MS SQL Server 2005. I seem to get this error more often
in 2005 that the older versions. Googling does not seem to give any help.
What are the causes of this error? What can I do to avoid it?
The error happens when I use a command like:
DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
If I resend the command after the error it always works.
Thanks,
Bob HillerBob and Sharon Hiller (aoklans@.tir.com) writes:
> I have a Visual Basic 6.0 app that I developed initially using MS SQL
> Server 7, then MS SQL Server 2000 and now MS SQL Server 2005. I seem to
> get this error more often in 2005 that the older versions. Googling does
> not seem to give any help. What are the causes of this error? What can I
> do to avoid it?
> The error happens when I use a command like:
> DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
> If I resend the command after the error it always works.
That seems to be very informative message "Multiple-step OLE DB operation ge
nerated errors. Check each OLE DB status value, if available. No work was do
ne."
That is, there are several possible reasons for the error, or more
exactly, it can be about anything.
One problem may be that the input data is too long for the field. Another
might that there is no metadata available to make it possible to assign
the field a value. The fact that you say that you get this more often
with SQL 2005 indicates this. See
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
for such a case.
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
7, then MS SQL
Server 2000 and now MS SQL Server 2005. I seem to get this error more often
in 2005 that the older versions. Googling does not seem to give any help.
What are the causes of this error? What can I do to avoid it?
The error happens when I use a command like:
DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
If I resend the command after the error it always works.
Thanks,
Bob HillerBob and Sharon Hiller (aoklans@.tir.com) writes:
> I have a Visual Basic 6.0 app that I developed initially using MS SQL
> Server 7, then MS SQL Server 2000 and now MS SQL Server 2005. I seem to
> get this error more often in 2005 that the older versions. Googling does
> not seem to give any help. What are the causes of this error? What can I
> do to avoid it?
> The error happens when I use a command like:
> DataRecord3.Fields("PSI_Fifteen") = CStr(PSI_Fifteen)
> If I resend the command after the error it always works.
That seems to be very informative message "Multiple-step OLE DB operation ge
nerated errors. Check each OLE DB status value, if available. No work was do
ne."
That is, there are several possible reasons for the error, or more
exactly, it can be about anything.
One problem may be that the input data is too long for the field. Another
might that there is no metadata available to make it possible to assign
the field a value. The fact that you say that you get this more often
with SQL 2005 indicates this. See
http://lab.msdn.microsoft.com/produ...59-17b0073cfa26
for such a case.
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
Thursday, February 9, 2012
2005 - Remove Old Backups
Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
2005 - Remove Old Backups
Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!
You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>
|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!
You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>
|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
2005 - Remove Old Backups
Is there a way to remove backups older than a certain number of days with a
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
--
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
generic 2005 maintenance plan. I looked through the basic tasks available
and I did not see this option. There is an option to remove old history
(i.e. logs) but I did not see a task to remove old backups.
This functionality was available with the SQL Server 2000 maintenance plans.
Thanks!You didn't look hard enough<g>. There is a task for exactly that, deleting
old backup files. It is called the "Maintenance Cleanup Task".
--
Andrew J. Kelly SQL MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
> Is there a way to remove backups older than a certain number of days with
> a generic 2005 maintenance plan. I looked through the basic tasks
> available and I did not see this option. There is an option to remove old
> history (i.e. logs) but I did not see a task to remove old backups.
> This functionality was available with the SQL Server 2000 maintenance
> plans.
> Thanks!
>|||Hi Cgal,
As Andrew has mentioned, there does exists the Maintenance Task for
cleaning up backup files in the SQL 2005's Maintenance Plan items... (also
available in sql server 2000 through the maintenance plan creation
wizard...).
Create a new maintenance plan in sql2005 management studio, and in the
"Maintenance Plan Task" toolbox, choose the "Maintenance cleanup task",
there has setting for removing backup files of certain age of time....
#Maintenance Cleanup Task (Maintenance Plan)
http://msdn2.microsoft.com/en-us/library/ms177182.aspx
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
| References: <OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl>
| Subject: Re: 2005 - Remove Old Backups
| Date: Tue, 20 Dec 2005 18:07:16 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Response
| Message-ID: <e2cIhobBGHA.3156@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: c-24-128-28-245.hsd1.nh.comcast.net 24.128.28.245
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:414948
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| You didn't look hard enough<g>. There is a task for exactly that,
deleting
| old backup files. It is called the "Maintenance Cleanup Task".
|
| --
| Andrew J. Kelly SQL MVP
|
|
| "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
| news:OcWhvIbBGHA.736@.TK2MSFTNGP10.phx.gbl...
| > Is there a way to remove backups older than a certain number of days
with
| > a generic 2005 maintenance plan. I looked through the basic tasks
| > available and I did not see this option. There is an option to remove
old
| > history (i.e. logs) but I did not see a task to remove old backups.
| >
| > This functionality was available with the SQL Server 2000 maintenance
| > plans.
| >
| > Thanks!
| >
| >
|
|
|
Subscribe to:
Posts (Atom)