Performance monitoring

Hi,
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.
We need to see the real trigger and preferably an error message from SQL Server if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:

> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> You need to select from the Virtaul table called "Inserted" to get the rows
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
[vbcol=seagreen]
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does not
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> If it does not work, then the odds are good that it is failing, and rolling
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||How does perfmon insert the data? Run a profiler trace to see whether perfmon does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can see
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :