Alert on deadlock

I have created an SQL Agent Alert which should notify me whenever
a deadlock is occured on sql server. The following are the details:

Type : Performance Condition Alert
Object : SQLServerLocks
Counter : Number of deadlocks/sec
Instance: Database
Alert : If counter value rises above 0

I got deadlock situation a couple of times on server but i have never received any notification. It seems this settings doenot work. Pls. advise!

Thanks

Is the SQL Agent definitely running ?

If so, "Test" that that the SQL Agent can create a mail session. Right click SQL Server Agent, choose Properties, click the test button.

|||yes. Nothing wrong with sql agent. as such i get other alerts but not this one.|||Is the occurrence count still zero ?
Under the Response tab try reducing the "delay between responses". I recall reading somewhere that if at the time SQL samples the counters there may well not be a deadlock. i.e. the delay between responses is sufficiently high for the condition to disappear before SQL samples the data.

after upgrading to sept ctp 2005 reports with subreports fail

I just installed September 2005 CTP and all reports with subreports
getting an error:
Error occured during local report processing. An internal error occured
on the report server.
If i delete subreports everything is fine. The error log says
following:
09/15/05 07:55:10, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
AdjustTokenPrivileges () failed (00000514)
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
4 supplied
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 3444
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
= 0x0
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x47405858
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ExceptionRecordPtr = 0x00000000
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
= <NULL>
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
not used
09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
not used
09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\SQLDmpr0001.mdmp
09/15/05 07:55:15, ACTION, aspnet_wp.exe, Watson Invoke: No
09/15/05 08:15:42, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
AdjustTokenPrivileges () failed (00000514)
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
4 supplied
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 3444
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
= 0x0
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x11A21E9C
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ExceptionRecordPtr = 0x00000000
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
= <NULL>
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
not used
09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
not used
09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\SQLDmpr0002.mdmp
09/15/05 08:15:45, ACTION, aspnet_wp.exe, Watson Invoke: No
09/15/05 08:23:14, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
AdjustTokenPrivileges () failed (00000514)
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
4 supplied
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 2732
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
= 0x0
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x10B35858
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
ExceptionRecordPtr = 0x00000000
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
= <NULL>
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
not used
09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
not used
09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles\SQLDmpr0003.mdmp
09/15/05 08:23:16, ACTION, aspnet_wp.exe, Watson Invoke: No
Any ideas?
Everything was fine in June CTP versionDid you upgrade the June CTP or did you remove everything (including the
dotnet 2.0 framework beta) first?
This CTP does not upgrade, you have to remove everything first and then
install.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1126801637.855296.186610@.g49g2000cwa.googlegroups.com...
>I just installed September 2005 CTP and all reports with subreports
> getting an error:
> Error occured during local report processing. An internal error occured
> on the report server.
> If i delete subreports everything is fine. The error log says
> following:
> 09/15/05 07:55:10, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 3444
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x47405858
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0001.mdmp
> 09/15/05 07:55:15, ACTION, aspnet_wp.exe, Watson Invoke: No
> 09/15/05 08:15:42, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 3444
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x11A21E9C
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0002.mdmp
> 09/15/05 08:15:45, ACTION, aspnet_wp.exe, Watson Invoke: No
> 09/15/05 08:23:14, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 2732
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x10B35858
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0003.mdmp
> 09/15/05 08:23:16, ACTION, aspnet_wp.exe, Watson Invoke: No
> Any ideas?
> Everything was fine in June CTP version
>|||removed everything twice and reinstalled|||I just created one (report/subreport). I suggest trying recreating the
report from scratch (or at least the subreport and then re-embed it).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1126803423.272541.149830@.g47g2000cwa.googlegroups.com...
> removed everything twice and reinstalled
>|||that what i did also. did not help.
here is another hint:
if i put this subreport under the group footer (or header) that i want,
report fails.
If i move subreport to different group header it works.|||If you email me the rdl and rdl.data files for both report and subreport I
will see about getting it to the correct person at MS. Just remove NOSPAM
from my email address.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1126807105.990018.151820@.z14g2000cwz.googlegroups.com...
> that what i did also. did not help.
> here is another hint:
> if i put this subreport under the group footer (or header) that i want,
> report fails.
> If i move subreport to different group header it works.
>|||before I'll do this try to reproduce it this way:
create report with 4 groups:
Group1
Group2
Group3
Group4
create subreport
put subreport under group 4 (or 3, header, footer, detail - does not
matter)
report will not run (at least for me)
if you put it inder group 1 or 2, report will work in Sept CTP 2005
package|||Great repro. I will pass it on to MS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1126879282.885405.84350@.g47g2000cwa.googlegroups.com...
> before I'll do this try to reproduce it this way:
> create report with 4 groups:
> Group1
> Group2
> Group3
> Group4
> create subreport
> put subreport under group 4 (or 3, header, footer, detail - does not
> matter)
> report will not run (at least for me)
> if you put it inder group 1 or 2, report will work in Sept CTP 2005
> package
>|||now, i think i know what really happened:
I have 4 groups
Group1
Group2
Group3
Group4
if one of the values for Group4 ( in my case Group3 also) will be null,
subreport that you will put under this group will make main report to
fail. And it happens only in Sept CTP 2005 package|||Great, I'll make sure someone gets this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1126887557.215825.300160@.f14g2000cwb.googlegroups.com...
> now, i think i know what really happened:
> I have 4 groups
> Group1
> Group2
> Group3
> Group4
> if one of the values for Group4 ( in my case Group3 also) will be null,
> subreport that you will put under this group will make main report to
> fail. And it happens only in Sept CTP 2005 package
>|||hi, i have hit this same problem as well. has a fix or workaround been
identified yet?
thanks,
steve
"bosstan1@.gmail.com" wrote:
> I just installed September 2005 CTP and all reports with subreports
> getting an error:
> Error occured during local report processing. An internal error occured
> on the report server.
> If i delete subreports everything is fine. The error log says
> following:
> 09/15/05 07:55:10, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 3444
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x47405858
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 07:55:10, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 07:55:15, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0001.mdmp
> 09/15/05 07:55:15, ACTION, aspnet_wp.exe, Watson Invoke: No
> 09/15/05 08:15:42, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 3444
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x11A21E9C
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 08:15:42, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 08:15:45, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0002.mdmp
> 09/15/05 08:15:45, ACTION, aspnet_wp.exe, Watson Invoke: No
> 09/15/05 08:23:14, ERROR , SQLDUMPER_UNKNOWN_APP.EXE,
> AdjustTokenPrivileges () failed (00000514)
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:
> 4 supplied
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID => 2732
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags
> = 0x0
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr => 0x10B35858
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,
> ExceptionRecordPtr = 0x00000000
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr => 0x00000000
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName
> = <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName => <NULL>
> 09/15/05 08:23:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11
> not used
> 09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7
> not used
> 09/15/05 08:23:16, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump
> completed: D:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\LogFiles\SQLDmpr0003.mdmp
> 09/15/05 08:23:16, ACTION, aspnet_wp.exe, Watson Invoke: No
> Any ideas?
> Everything was fine in June CTP version
>|||Has anyone found a solution to this? I am running into the same issues.
"Bruce L-C [MVP]" wrote:
> Great, I'll make sure someone gets this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <bosstan1@.gmail.com> wrote in message
> news:1126887557.215825.300160@.f14g2000cwb.googlegroups.com...
> > now, i think i know what really happened:
> > I have 4 groups
> > Group1
> > Group2
> > Group3
> > Group4
> >
> > if one of the values for Group4 ( in my case Group3 also) will be null,
> > subreport that you will put under this group will make main report to
> > fail. And it happens only in Sept CTP 2005 package
> >
>
>

After insert trigger fires even though no insert actually occured

SQL Server 2000 SP3a
Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine fires
on an insert being run but with no actual inserts occuring.
ie I have a sproc which inserts into a table, I've checked the select
statement of the insert and it returns no rows, so I presumed this meant tha
t
the trigger would not fire as no rows are being inserted, but it does.
======================
-- The basic spoc is this, the select returns no rows, so no inserts should
occur':
insert into table_a (
a_col_1,
a_col_2)
select
b_col_1,
b_col_2
from some_table b
left outer join table_a on
b.pkey = a.pkey
where
a.pkey is null
=======================
======================
-- The basic trigger is
Create trigger tg_Trigger_Name
on A_Table
after insert, update
if update (col_a)
begin
update some other table set some stuff
end
======================
I've looked through BOL, but it simply sais after insert/update trigger
fires after completion.
Thanks for any pointers.> if update (col_a)
> begin
> update some other table set some stuff
> end
The trigger will still fire, even if no rows are affected. So, you should
base any activities in the trigger on whether or not anything actually
happened. I typically wrap my DML trigger activities inside of the
following:
IF @.@.ROWCOUNT > 0
BEGIN
.. stuff here
END
For an insert trigger, you could equally say:
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
.. stuff here
END
A|||This is the way it works. This is not bad, if your actions inside the
trigger are based on contents of the inserted table, because this table is
empty. However, you can use the following code to skip the logic inside the
trigger (the same is true for updates):
if not exists(select * from inserted)
return
Leonid.
"Steve'o" <Steveo@.discussions.microsoft.com> wrote in message
news:55564004-68B8-4879-9F24-7BE5CF2E8F43@.microsoft.com...
> SQL Server 2000 SP3a
> Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine
> fires
> on an insert being run but with no actual inserts occuring.
> ie I have a sproc which inserts into a table, I've checked the select
> statement of the insert and it returns no rows, so I presumed this meant
> that
> the trigger would not fire as no rows are being inserted, but it does.
> ======================
> -- The basic spoc is this, the select returns no rows, so no inserts
> should
> occur':
> insert into table_a (
> a_col_1,
> a_col_2)
> select
> b_col_1,
> b_col_2
> from some_table b
> left outer join table_a on
> b.pkey = a.pkey
> where
> a.pkey is null
> =======================
> ======================
> -- The basic trigger is
> Create trigger tg_Trigger_Name
> on A_Table
> after insert, update
> if update (col_a)
> begin
> update some other table set some stuff
> end
> ======================
> I've looked through BOL, but it simply sais after insert/update trigger
> fires after completion.
> Thanks for any pointers.