Change gridline color at specific value

Has anyone written a conditional statement to change a gridline value at a
specific value?
=iif(?=90, "Red","Silver")
Any advise is apreciated.
Thanks,
Scott A. BakerSorry, setting the color of individual gridlines is not supported.
Are you trying to set a specific horizontal gridline (in a column chart or
line chart) or a vertical gridline (in a bar chart)?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
> Has anyone written a conditional statement to change a gridline value at
> a specific value?
> =iif(?=90, "Red","Silver")
> Any advise is apreciated.
> Thanks,
> Scott A. Baker
>|||I am attempting to set a horizontal gridline for a stacked column chart. I
noticed that where you set the color for grid there is also an expression
button. I assumed you would be able to change the color depending on the
value.
This becomes helpful in setting a static goal line. In my example
=iif(?=90, "Red","Silver") , the 90 represents the goal.
Thank you for your response.
Scott
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Sorry, setting the color of individual gridlines is not supported.
> Are you trying to set a specific horizontal gridline (in a column chart or
> line chart) or a vertical gridline (in a bar chart)?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value at
>> a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>|||In that case, you could just add another data series and plot that series as
line. Set the data value expression to =90 and set the BorderColor property
accordingly.
Note: you don't use the gridlines to show your static goal line, but rather
you add a "fake" data series with a constant value to show the goal line.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>I am attempting to set a horizontal gridline for a stacked column chart. I
>noticed that where you set the color for grid there is also an expression
>button. I assumed you would be able to change the color depending on the
>value.
> This becomes helpful in setting a static goal line. In my example
> =iif(?=90, "Red","Silver") , the 90 represents the goal.
> Thank you for your response.
> Scott
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value at
>> a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>|||Robert,
Again thank you for your prompt response. I have added a static line to the
charts that cover multiple time periods. However, I also show a period
summary that only has one x axis value. In this case, a static line does
not work because there is not more than one data point to connect to.
Any other suggestions?
Thanks,
Scott
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> In that case, you could just add another data series and plot that series
> as line. Set the data value expression to =90 and set the BorderColor
> property accordingly.
> Note: you don't use the gridlines to show your static goal line, but
> rather you add a "fake" data series with a constant value to show the goal
> line.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>>I am attempting to set a horizontal gridline for a stacked column chart.
>>I noticed that where you set the color for grid there is also an
>>expression button. I assumed you would be able to change the color
>>depending on the value.
>> This becomes helpful in setting a static goal line. In my example
>> =iif(?=90, "Red","Silver") , the 90 represents the goal.
>> Thank you for your response.
>> Scott
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value
>> at a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>>
>|||If the chart has only one data point at all, then my suggestion won't work.
But here is another idea for the summary scenario with only one datapoint:
turn off margins on the x-axis and use a column chart to draw a "zone"
instead of a target line. Since the margins are turned off, it would like if
the chart had then only two zones and your actual datapoint summary value
either is in the "good" or the "bad" zone.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
news:up5Jr7W1FHA.2428@.tk2msftngp13.phx.gbl...
> Robert,
> Again thank you for your prompt response. I have added a static line to
> the charts that cover multiple time periods. However, I also show a
> period summary that only has one x axis value. In this case, a static
> line does not work because there is not more than one data point to
> connect to.
> Any other suggestions?
> Thanks,
> Scott
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> In that case, you could just add another data series and plot that series
>> as line. Set the data value expression to =90 and set the BorderColor
>> property accordingly.
>> Note: you don't use the gridlines to show your static goal line, but
>> rather you add a "fake" data series with a constant value to show the
>> goal line.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>>I am attempting to set a horizontal gridline for a stacked column chart.
>>I noticed that where you set the color for grid there is also an
>>expression button. I assumed you would be able to change the color
>>depending on the value.
>> This becomes helpful in setting a static goal line. In my example
>> =iif(?=90, "Red","Silver") , the 90 represents the goal.
>> Thank you for your response.
>> Scott
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
>> Sorry, setting the color of individual gridlines is not supported.
>> Are you trying to set a specific horizontal gridline (in a column chart
>> or line chart) or a vertical gridline (in a bar chart)?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
>> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
>> Has anyone written a conditional statement to change a gridline value
>> at a specific value?
>> =iif(?=90, "Red","Silver")
>> Any advise is apreciated.
>> Thanks,
>> Scott A. Baker
>>
>>
>>
>>
>|||Set Gridlines = null,Cellspacing = 1,BorderColor = 'Red' , and then you will
find the color of gridlines has been changed!
"Robert Bruckner [MSFT]" wrote:
> If the chart has only one data point at all, then my suggestion won't work.
> But here is another idea for the summary scenario with only one datapoint:
> turn off margins on the x-axis and use a column chart to draw a "zone"
> instead of a target line. Since the margins are turned off, it would like if
> the chart had then only two zones and your actual datapoint summary value
> either is in the "good" or the "bad" zone.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> news:up5Jr7W1FHA.2428@.tk2msftngp13.phx.gbl...
> > Robert,
> >
> > Again thank you for your prompt response. I have added a static line to
> > the charts that cover multiple time periods. However, I also show a
> > period summary that only has one x axis value. In this case, a static
> > line does not work because there is not more than one data point to
> > connect to.
> >
> > Any other suggestions?
> >
> > Thanks,
> >
> > Scott
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:usZ5QPS1FHA.1256@.TK2MSFTNGP09.phx.gbl...
> >> In that case, you could just add another data series and plot that series
> >> as line. Set the data value expression to =90 and set the BorderColor
> >> property accordingly.
> >>
> >> Note: you don't use the gridlines to show your static goal line, but
> >> rather you add a "fake" data series with a constant value to show the
> >> goal line.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> >> news:%23oI9ysO1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> >>I am attempting to set a horizontal gridline for a stacked column chart.
> >>I noticed that where you set the color for grid there is also an
> >>expression button. I assumed you would be able to change the color
> >>depending on the value.
> >>
> >> This becomes helpful in setting a static goal line. In my example
> >> =iif(?=90, "Red","Silver") , the 90 represents the goal.
> >>
> >> Thank you for your response.
> >>
> >> Scott
> >>
> >>
> >> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> >> news:%23j$WdcF1FHA.3660@.TK2MSFTNGP15.phx.gbl...
> >> Sorry, setting the color of individual gridlines is not supported.
> >> Are you trying to set a specific horizontal gridline (in a column chart
> >> or line chart) or a vertical gridline (in a bar chart)?
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Scott Baker" <sNbOaSkPeArM@.orasure.com> wrote in message
> >> news:u7$csZ%230FHA.1564@.tk2msftngp13.phx.gbl...
> >> Has anyone written a conditional statement to change a gridline value
> >> at a specific value?
> >>
> >> =iif(?=90, "Red","Silver")
> >>
> >> Any advise is apreciated.
> >>
> >> Thanks,
> >>
> >> Scott A. Baker
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

CE upgrade tool?

Have anyone written a tool to upgrade from CE to Everywhere that is easier to use that the upgrade.exe program?

Thanks

Hi,
I have written a free GUI Interface to it - available at
http://www.gui-innovations.com/download/SqlUpgrade.zip

Pete

|||

Hello Pete,

Thanks for the link to your program.

I ran the program and got this error message:

SQLMobile appears not to be installed - can't continue. Cab files should be in the C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0 folders on your PC

What product/SDK is it expecting?

Thanks

Tom

|||

Hi,

you will need the .Net CF 2 runtimes, and the SqlMobile runtimes on the PPC to run it

Pete

CDOSYS email body truncated

I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- Save the configurations to the message object.
exec @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
exec @.hr = sp_OASetProperty @.iMsg, 'To', @.to
exec @.hr = sp_OASetProperty @.iMsg, 'From', @.from
exec @.hr = sp_OASetProperty @.iMsg, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
BEGIN
exec @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
exec @.hr = sp_OADestroy @.iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).

> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.

Have a data flow which inserts 1000 rows into a table. One of the rows cannot

go because of a constraint on the column. The following is thrown by SQL /

SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:

0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL

Native Client" Hresult: 0x80040E2F Description: "The

statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F

Description: "The INSERT statement conflicted with the CHECK constraint

"CK_cpxx". The conflict occurred in database "POS_ETL",

table "dbo.cpxx", column 'Col1'.".

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE

DB Destination Input" (32)" failed because error code 0xC020907B

occurred, and the error row disposition on "input "OLE DB Destination

Input" (32)" specifies failure on error. An error occurred on the

specified object of the specified component.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component

"OLE DB Destination" (19) failed with error code 0xC0209029. The

identified component returned an error from the ProcessInput method. The error

is specific to the component, but the error is fatal and will cause the Data

Flow task to stop running.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0"

has exited with error code 0xC0209029.

>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)

This causes the onerror event to fire 4 times.

This type of behavior is useless for any error handling of this type as

anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work

than it's worth.

OnFail constraint is the simplest and totally ignoring the onerror event

handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot go because of a constraint on the column. The following is thrown by SQL / SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the CHECK constraint "CK_cpxx". The conflict occurred in database "POS_ETL", table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?