EXACT sequence of events in sync mirroring

Hi
I need to know exactly the sequence of events in synchronous mirroring mode.
What im reading is this:
1. tranaction recved from a client by primary
2. primary writes trans to trans log
3. log written to dbase ie to disk from log on primary and concurrently
sends log to mirror.
4. Primary waits for response from mirror
5. Mirror writes log to disk
6. mirror returns acknowledgement to primary.
7. primary sends conf to client.
I need to know what happens if the mirror DOES NOT SEND THE ACK.
Q1. Does the primary roll the dabse back?
Q2. if so, the data goes back into the trans log awaiting response from
mirror and subsequent commitall on primary, correct?
Q2. Whats the timeout period for an ack from the mirror?
Question 1 is the most important. ive bought bloody standard verion with its
sync mirroring as oppoed to the laughably expensive Ent edition with its ASYN
nmirroring and im wondering if I can get away with it...
Thanks
Alastair
Methodology
This is the sequence
1) transaction received from a client by the primary
2) pages dirtied
3) transaction written to log buffer
4) transaction written to log buffer on mirror
5) transaction committed
6) log hardened to disk (written to transaction log) (5 and 6 can be
interchanged depending on the length of a transaction).
7) at checkpoint all dirty pages written to disk
referring to:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
in all SQL Server databases, data changes are recorded in the transaction
log before any changes to actual data pages are made. The transaction log
records are placed first in a database's log buffer in memory, and then
flushed to disk (or 'hardened') as quickly as possible. In database
mirroring, as the principal server writes the principal database's log
buffer to disk, it simultaneously sends that block of log records to the
mirror instance.
Answers to your questions I assume in this statement " Does the primary roll
the dabse back?" you mean "Does the primary roll the transaction back?"
1) The transaction is rolled back on the primary at the application level.
2) it may appear in the tlog, depending on whether it is hardened or not.
The commit or rollback will be in the transaction log so on recovery the
disk will be consistent.
3) the timeout is controlled by the app - it typically be default is 20 s.
Most apps using database mirroring or a HA technology will have retry logic
built in to minimize any data loss. The failover time once initiated it
typically less than 10 s. It may take some time for the witness to detect
that the principal or mirror is offline - this can be 10-20 seconds.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:FAAB3E4A-3F0B-4788-9DF9-3B66651A928A@.microsoft.com...
> Hi
> I need to know exactly the sequence of events in synchronous mirroring
> mode.
> What im reading is this:
> 1. tranaction recved from a client by primary
> 2. primary writes trans to trans log
> 3. log written to dbase ie to disk from log on primary and concurrently
> sends log to mirror.
> 4. Primary waits for response from mirror
> 5. Mirror writes log to disk
> 6. mirror returns acknowledgement to primary.
> 7. primary sends conf to client.
> I need to know what happens if the mirror DOES NOT SEND THE ACK.
> Q1. Does the primary roll the dabse back?
> Q2. if so, the data goes back into the trans log awaiting response from
> mirror and subsequent commitall on primary, correct?
> Q2. Whats the timeout period for an ack from the mirror?
> Question 1 is the most important. ive bought bloody standard verion with
> its
> sync mirroring as oppoed to the laughably expensive Ent edition with its
> ASYN
> nmirroring and im wondering if I can get away with it...
> Thanks
> Alastair
> Methodology

events raised by the report server when report is saved

there is possible ?

how ?

There are no events that you can hook up to when a report definition has been changed. Perhaps if you describe your scenario we can figure out a workaround for you.

-Daniel

|||

my scenario is:

What I want is to be able to "catch" the event where the user saves or closes the report into the Report Builder, so that I can create the related class, and it has to be right when it happens, it can′t be later. It would also be useful if the event could identify the name and path of the report I just created. I am sorry if I am not able to be a little more clear regarding this subject, but I hope you understand what I want, and if there is no way to do it just let me know either way.

Thanks for your help

gerard

|||There is no event when this happens.

events raised by the report server when report is saved

there is possible ?

how ?

There are no events that you can hook up to when a report definition has been changed. Perhaps if you describe your scenario we can figure out a workaround for you.

-Daniel

|||

my scenario is:

What I want is to be able to "catch" the event where the user saves or closes the report into the Report Builder, so that I can create the related class, and it has to be right when it happens, it can′t be later. It would also be useful if the event could identify the name and path of the report I just created. I am sorry if I am not able to be a little more clear regarding this subject, but I hope you understand what I want, and if there is no way to do it just let me know either way.

Thanks for your help

gerard

|||There is no event when this happens.

Events on SQL Server

Hi,
We had a problem on one of our SQL Servers, basically it stopped accepting
connections and we could not even connect to it with the Enterprise Manager.
At the same time we started getting a lot of the following events
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/2/2005
Time: 2:58:56 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17120,
SQL Server could not spawn process_loginread thread.
..
Data:
0000: e0 42 00 00 10 00 00 00 B.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 00 00 00 00 ...
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/2/2005
Time: 2:51:15 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: Error:
17803, Severity: 20, State: 12
Insufficient memory available..
Data:
0000: 8b 45 00 00 14 00 00 00 <E.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 11 00 00 00 4a 00 6f 00 ...J.o.
0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
0040: 30 00 35 00 00 00 0.5...
At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
4Gb physical mem available though. We had to restart the services to bring
it up again..
ANy insight appreciated
Thanks Nich
You may want to apply
http://support.microsoft.com/default...b;en-us;893312 to try and
resolve SQL memory problems, but if they persist contact Microsoft Product
Support for assistance.
Adrian
"Nicholas Aquilina" <nickaquilina@.hotmail.com> wrote in message
news:%23kItF8MUFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> We had a problem on one of our SQL Servers, basically it stopped accepting
> connections and we could not even connect to it with the Enterprise
> Manager.
> At the same time we started getting a lot of the following events
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/2/2005
> Time: 2:58:56 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> 17120, SQL Server could not spawn process_loginread thread.
> .
> Data:
> 0000: e0 42 00 00 10 00 00 00 B.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 00 00 00 00 ...
> ----
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 5/2/2005
> Time: 2:51:15 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> Error: 17803, Severity: 20, State: 12
> Insufficient memory available..
> Data:
> 0000: 8b 45 00 00 14 00 00 00 <E.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 11 00 00 00 4a 00 6f 00 ...J.o.
> 0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
> 0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
> 0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
> 0040: 30 00 35 00 00 00 0.5...
>
> At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
> 4Gb physical mem available though. We had to restart the services to bring
> it up again..
> ANy insight appreciated
> Thanks Nich
>

Events on SQL Server

Hi,
We had a problem on one of our SQL Servers, basically it stopped accepting
connections and we could not even connect to it with the Enterprise Manager.
At the same time we started getting a lot of the following events
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/2/2005
Time: 2:58:56 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17120,
SQL Server could not spawn process_loginread thread.
.
Data:
0000: e0 42 00 00 10 00 00 00 B.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 00 00 00 00 ...
----
--
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/2/2005
Time: 2:51:15 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: Error:
17803, Severity: 20, State: 12
Insufficient memory available..
Data:
0000: 8b 45 00 00 14 00 00 00 <E.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 11 00 00 00 4a 00 6f 00 ...J.o.
0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
0040: 30 00 35 00 00 00 0.5...
At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
4Gb physical mem available though. We had to restart the services to bring
it up again..
ANy insight appreciated
Thanks NichYou may want to apply
http://support.microsoft.com/defaul...kb;en-us;893312 to try and
resolve SQL memory problems, but if they persist contact Microsoft Product
Support for assistance.
Adrian
"Nicholas Aquilina" <nickaquilina@.hotmail.com> wrote in message
news:%23kItF8MUFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> We had a problem on one of our SQL Servers, basically it stopped accepting
> connections and we could not even connect to it with the Enterprise
> Manager.
> At the same time we started getting a lot of the following events
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/2/2005
> Time: 2:58:56 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> 17120, SQL Server could not spawn process_loginread thread.
> .
> Data:
> 0000: e0 42 00 00 10 00 00 00 B.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 00 00 00 00 ...
> ----
--
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 5/2/2005
> Time: 2:51:15 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> Error: 17803, Severity: 20, State: 12
> Insufficient memory available..
> Data:
> 0000: 8b 45 00 00 14 00 00 00 <E.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 11 00 00 00 4a 00 6f 00 ...J.o.
> 0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
> 0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
> 0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
> 0040: 30 00 35 00 00 00 0.5...
>
> At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
> 4Gb physical mem available though. We had to restart the services to bring
> it up again..
> ANy insight appreciated
> Thanks Nich
>

Events on SQL Server

Hi,
We had a problem on one of our SQL Servers, basically it stopped accepting
connections and we could not even connect to it with the Enterprise Manager.
At the same time we started getting a lot of the following events
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/2/2005
Time: 2:58:56 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 17120,
SQL Server could not spawn process_loginread thread.
.
Data:
0000: e0 42 00 00 10 00 00 00 àB.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 00 00 00 00 ...
----
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/2/2005
Time: 2:51:15 AM
User: N/A
Computer: SQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be
able to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: Error:
17803, Severity: 20, State: 12
Insufficient memory available..
Data:
0000: 8b 45 00 00 14 00 00 00 <E.....
0008: 0a 00 00 00 53 00 51 00 ...S.Q.
0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
0018: 56 00 45 00 52 00 00 00 V.E.R...
0020: 11 00 00 00 4a 00 6f 00 ...J.o.
0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
0040: 30 00 35 00 00 00 0.5...
At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
4Gb physical mem available though. We had to restart the services to bring
it up again..
ANy insight appreciated
Thanks NichYou may want to apply
http://support.microsoft.com/default.aspx?scid=kb;en-us;893312 to try and
resolve SQL memory problems, but if they persist contact Microsoft Product
Support for assistance.
Adrian
"Nicholas Aquilina" <nickaquilina@.hotmail.com> wrote in message
news:%23kItF8MUFHA.2172@.tk2msftngp13.phx.gbl...
> Hi,
> We had a problem on one of our SQL Servers, basically it stopped accepting
> connections and we could not even connect to it with the Enterprise
> Manager.
> At the same time we started getting a lot of the following events
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/2/2005
> Time: 2:58:56 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> 17120, SQL Server could not spawn process_loginread thread.
> .
> Data:
> 0000: e0 42 00 00 10 00 00 00 àB.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 00 00 00 00 ...
> ----
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 5/2/2005
> Time: 2:51:15 AM
> User: N/A
> Computer: SQLSERVER
> Description:
> The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information
> or message DLL files to display messages from a remote computer. You may
> be able to use the /AUXSOURCE= flag to retrieve this description; see Help
> and Support for details. The following information is part of the event:
> Error: 17803, Severity: 20, State: 12
> Insufficient memory available..
> Data:
> 0000: 8b 45 00 00 14 00 00 00 <E.....
> 0008: 0a 00 00 00 53 00 51 00 ...S.Q.
> 0010: 4c 00 53 00 45 00 52 00 L.S.E.R.
> 0018: 56 00 45 00 52 00 00 00 V.E.R...
> 0020: 11 00 00 00 4a 00 6f 00 ...J.o.
> 0028: 75 00 72 00 6e 00 61 00 u.r.n.a.
> 0030: 6c 00 69 00 6e 00 67 00 l.i.n.g.
> 0038: 5f 00 4a 00 61 00 6e 00 _.J.a.n.
> 0040: 30 00 35 00 00 00 0.5...
>
> At the same time SQL was using 1.9Gb ov Ram & an other 1.9 VM. Server has
> 4Gb physical mem available though. We had to restart the services to bring
> it up again..
> ANy insight appreciated
> Thanks Nich
>

Events log crashing SQL server 2005

i have my SQL server 2005 crashing when Events log is full

I have a hacker attacking my DB with a brut force tool but whereas he does not have the password, event log registers his access attempts as Failure Audit.

I have thousands of lines of "Failure Audit" in my event log

The event logs are set to be overwritten automatically when they reach 16Mb but it's not working correctly, they r not overwriting their content.

in SQL 2005 ERRORLOG file i see:

2007-05-14 01:57:11.57 spid80 Error: 17054, Severity: 16, State: 1.

2007-05-14 01:57:11.57 spid80 The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.

SQL Agent cannot starts because it's not able to write in the event log that it's starting and when it cannot write in event log, it does not start and my sql server crashed

My Question is simply how to fix this issue once for all

Which Windows OS are you using?|||In the event viewer, right click the "Security Log" and select "Properties" and change the radio button to "Overwrite events as needed". This will prevent the log from filling up and crashing.

Actually, do this for each log.
|||You should also add a line to your firewall to prevent his IP from accessing your SQL Server. You should actually lock all users from hitting your SQL Server directly, but start with his subnet as a good first step.

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events in SSRS Reports

Hi,
I would like to have a button type of thing on the report and on click of
it, I need to call a method in the custom assembly.
I was successful creating and using the custom assembly, but not able to
raise an event from the report.
Thanks in advance,
SureshI am not aware of any way to do this. I am aware of a variety of extensions
that are possible: rendering, security, data processing and delivery.
However, this is not like a winform or webform that has a variety of events
you can respond and write your custom code for.
What are you trying to do, perhaps there is another way to solve the
problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Suresh" <Suresh@.discussions.microsoft.com> wrote in message
news:409DCFC3-7875-4A9E-818D-C55094BC0895@.microsoft.com...
> Hi,
> I would like to have a button type of thing on the report and on click of
> it, I need to call a method in the custom assembly.
> I was successful creating and using the custom assembly, but not able to
> raise an event from the report.
> Thanks in advance,
> Suresh|||Thanks Bruce for the response,
After the report is generated from report server, I need to publish this
report to som eother location. I would like to acheive that by the click of a
button. Here Im not sure how to place a button, or just raise an event by the
user to perform that action.
Suresh. India
"Bruce L-C [MVP]" wrote:
> I am not aware of any way to do this. I am aware of a variety of extensions
> that are possible: rendering, security, data processing and delivery.
> However, this is not like a winform or webform that has a variety of events
> you can respond and write your custom code for.
> What are you trying to do, perhaps there is another way to solve the
> problem.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
> news:409DCFC3-7875-4A9E-818D-C55094BC0895@.microsoft.com...
> > Hi,
> > I would like to have a button type of thing on the report and on click of
> > it, I need to call a method in the custom assembly.
> > I was successful creating and using the custom assembly, but not able to
> > raise an event from the report.
> >
> > Thanks in advance,
> > Suresh
>
>|||I was looking for a way to reset the rownumber for each group, so I
could display a record count for every item in each group not one
continuous number for the entire report, when I saw this posting.
Access to the group event and being able to set rwonumber back to 1
would solve my task.
Perhaps there is another way to do it?
Thanks,
IanO|||What do you mean you need to publish? Are you wanting to save the report as
PDF somewhere? Or are you talking about publishing the report to another
Server?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Suresh" <Suresh@.discussions.microsoft.com> wrote in message
news:D617027B-61DB-4AA8-AD99-0E39070B60C8@.microsoft.com...
> Thanks Bruce for the response,
> After the report is generated from report server, I need to publish this
> report to som eother location. I would like to acheive that by the click
> of a
> button. Here Im not sure how to place a button, or just raise an event by
> the
> user to perform that action.
> Suresh. India
> "Bruce L-C [MVP]" wrote:
>> I am not aware of any way to do this. I am aware of a variety of
>> extensions
>> that are possible: rendering, security, data processing and delivery.
>> However, this is not like a winform or webform that has a variety of
>> events
>> you can respond and write your custom code for.
>> What are you trying to do, perhaps there is another way to solve the
>> problem.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Suresh" <Suresh@.discussions.microsoft.com> wrote in message
>> news:409DCFC3-7875-4A9E-818D-C55094BC0895@.microsoft.com...
>> > Hi,
>> > I would like to have a button type of thing on the report and on click
>> > of
>> > it, I need to call a method in the custom assembly.
>> > I was successful creating and using the custom assembly, but not able
>> > to
>> > raise an event from the report.
>> >
>> > Thanks in advance,
>> > Suresh
>>

Events for Selected Month

I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too.

so far,as an example, I have:

SELECTEvents.startDate,Events.endDateFROM EventsWHEREEvents.Active = 1ANDstartDateBETWEENconvert(smalldatetime,'5/1/2006')ANDconvert(smalldatetime,'5/31/2006')ORDER BY Events.startDateASC;

thank in advance.

Ty this using Month function of your datatime filed.

WHERE
Events.Active = 1
ANDMONTH(startDate)BETWEEN 1AND7

Or

WHERE
Events.Active = 1
AND
(Month(startDate)>0 and Month(startdate)<8)

|||

the months will be depend on the event. In other words I just said January and July but it could be anything. Can the one and seven be wildcards or maybe 1 and 12 to cover the entire year?

|||

Yes, you can do

Events.Active = 1
AND
(Month(startDate)>0 and Month(startdate)<12)

Or you can cover the year like this:

Year(startDate)=2006 --for year 2006 or any year you can change.

This only takes care of your event start date. In the same logic, you can do something to the event end date if you have one.

Let me know if you need more information.

Events for LoadFromSqlServer method?

Hi everyone,

I was wondering what kind of events fires when you do a LoadFromSqlServer method? I've got defined a Events class but debugging with F11 never get in touch with any event (apparently or mabye the problem

is that I haven't declared that concrete one)

Let me know if possible or not. I provide you my snippet of code:

Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package 'Para que no choque con el objeto APPLICATION DE SYSTEM.WINDOWS.FORMS

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

Dim EventsSSIS As Eventos

EventsSSIS = New Eventos()

..

..

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "aaa", EventsSSIS)

..

..

Public Class Eventos

Implements IDTSEvents

OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed

OnWarning(ByVal source As DtsObj

OnPreValidate

..

..

Thanks in advance,

Any idea? Jamie?

TIA

|||You'll get OnError and OnWarning if there are any problems with the package (e.g. the package is encrypted and the password you supplied is incorrect).|||Ok, thanks a lot for that.

Events for LoadFromSqlServer method?

Hi everyone,

I was wondering what kind of events fires when you do a LoadFromSqlServer method? I've got defined a Events class but debugging with F11 never get in touch with any event (apparently or mabye the problem

is that I haven't declared that concrete one)

Let me know if possible or not. I provide you my snippet of code:

Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package 'Para que no choque con el objeto APPLICATION DE SYSTEM.WINDOWS.FORMS

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

Dim EventsSSIS As Eventos

EventsSSIS = New Eventos()

..

..

pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "\" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "aaa", EventsSSIS)

..

..

Public Class Eventos

Implements IDTSEvents

OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed

OnWarning(ByVal source As DtsObj

OnPreValidate

..

..

Thanks in advance,

Any idea? Jamie?

TIA

|||You'll get OnError and OnWarning if there are any problems with the package (e.g. the package is encrypted and the password you supplied is incorrect).|||Ok, thanks a lot for that.

Events Calendar Questions: how to find events in the week end

I have a start date, end date for each event.

I want to list all events between the start and end date comes in Saturday or Sunday.
in SQL server 2005 TSQL statement.

any insights ?Below is a link to an article that explains how to manipulate Dates in SQL and gives a good description of the DATENAME command which you will most likely need to use for this.

http://www.quest-pipelines.com/newsletter-v3/0202_G.htm

I hope that this helps.

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
Event Type:Warning
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:312
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
Event Type:Information
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:311
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Attempting to to re-open the local eventlog...
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:318
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
>
|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:

> It's chicken and egg. The reason you get the messages is because you cleared
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you will
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
>
>

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
----
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 312
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
----
--
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 311
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Attempting to to re-open the local eventlog...
----
--
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 318
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
----
--It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
--
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
--
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
--
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
--
>|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:

> It's chicken and egg. The reason you get the messages is because you clear
ed
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you wil
l
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
>
>

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
----
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 312
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
----
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 311
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Attempting to to re-open the local eventlog...
----
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 318
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
----It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
>|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:
> It's chicken and egg. The reason you get the messages is because you cleared
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you will
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> > Each time I clear the event log on two of our SQL servers, I get the
> > following errors a few seconds later (see below). Config is as follows:
> > Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> > servers).
> >
> > Any help in clearing up these annoying error messages would be
> > appreciated.
> >
> > ----
> > Event Type: Warning
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 312
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Successfully re-opened the local eventlog - NOTE: Some events may have
> > been
> > missed.
> > ----
> > Event Type: Information
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 311
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Attempting to to re-open the local eventlog...
> > ----
> > Event Type: Error
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 318
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Unable to read local eventlog (reason: The event log file has changed
> > between read operations).
> > ----
> >
>
>

Eventhandlers for DataFlow task events

Does anyone know how to create an eventhandler for a dataflow task specific events (OnPipelinePostEndOfRowset, OnPipelineRowsSent, etc.)? These events are available for logging via the standard logging infrastructure, but there seems to no eventhandler for them.

The reason I'm interested is that parsing information logged by these events using builtin log providers is not easy (eg., the number of rows sent gets burried somewhere in the message column (i'm using sql provider). I'd like to capture this information and record it cleanly in a custom ssis metadata database i'm building. Any ideas are welcome. Thanks.

-alex

chianuri wrote:

Does anyone know how to create an eventhandler for a dataflow task specific events (OnPipelinePostEndOfRowset, OnPipelineRowsSent, etc.)? These events are available for logging via the standard logging infrastructure, but there seems to no eventhandler for them.

The reason I'm interested is that parsing information logged by these events using builtin log providers is not easy (eg., the number of rows sent gets burried somewhere in the message column (i'm using sql provider). I'd like to capture this information and record it cleanly in a custom ssis metadata database i'm building. Any ideas are welcome. Thanks.

-alex

Alex,

I've just checked this out and it was news to me that there are no eventhandlers for these events. Interesting. And annoying.

I guess your only option is to parse out the number of rows from the OnInformaiton event that you already know about.

Perhaps you could raise a request at Connect to get these eventhandlers added. I would vote for it.

-Jamie

|||

Jamie Thomson wrote:

Perhaps you could raise a request at Connect to get these eventhandlers added. I would vote for it.

The link can be found here, Jamie. Thanks.

|||

[microsoft follow-up]

hello guys. Why is there no eventhandler for OnPipelineRowsSent?

Thanks

Jamie