Why is exception not thrown in AseCommand when "Truncation error occurred"?

[Former Member]

In the below SQL, data is written to SOURCE and from there to TARGET.

The DECIMAL columns in TARGET are deliberately smaller than those in SOURCE (eg a DECIMAL (12, 2) column populated from a DECIMAL (19,11) source).

When I run this in an Query tool (eg, SqlDbx) I get the messages:

"Truncation error occurred

Command has been aborted"

But when I run this using the .net Client (supplied with the Developer Edition of ASE 16.0) no exception is thrown (the INSERT fails though).  The method is AseCommand.ExecuteNonQuery().

Is this deliberate? 

Is this believed to be correct?

How can I tell that a truncation error has been raised?

Thanks

IF OBJECT_ID ('dbo.TARGET') IS NOT NULL

  DROP TABLE dbo.TARGET

GO

CREATE TABLE dbo.TARGET

  (

  S_Name_NVARCHAR NVARCHAR (50) null,

    S_RedComponent_DEC_15_6 decimal(15, 6) NULL,

    S_BlueComponent_DEC_12_2 decimal(12, 2) NULL, 

    S_GreenComponent_DEC_18_10 decimal(18, 10) NULL

  )

GO

IF OBJECT_ID ('dbo.SOURCE') IS NOT NULL

  DROP TABLE dbo.SOURCE

GO

CREATE TABLE dbo.SOURCE

  (

  Name_NVARCHAR      NVARCHAR (2000) NULL,

  RedComponent_DEC   DECIMAL (19,11) NULL,

  GreenComponent_DEC DECIMAL (19,11) NULL,

  BlueComponent_DEC  DECIMAL (19,11) NULL

  )

GO

INSERT INTO dbo.SOURCE (Name_NVARCHAR, RedComponent_DEC, GreenComponent_DEC, BlueComponent_DEC)

VALUES ('Beige', 272.195, 272.195, 244.42)

GO

INSERT INTO dbo.SOURCE (Name_NVARCHAR, RedComponent_DEC, GreenComponent_DEC, BlueComponent_DEC)

VALUES ('Bisque', 283.305, 253.308, 217.756)

GO

INSERT INTO dbo.SOURCE (Name_NVARCHAR, RedComponent_DEC, GreenComponent_DEC, BlueComponent_DEC)

VALUES ('Black', 0, 0, 0)

GO

INSERT INTO dbo.SOURCE (Name_NVARCHAR, RedComponent_DEC, GreenComponent_DEC, BlueComponent_DEC)

VALUES ('BlanchedAlmond', 283.305, 261.085, 227.755)

GO

--Is there data to migrate?

SELECT LEFT( S.Name_NVARCHAR,8000),S.GreenComponent_DEC,S.GreenComponent_DEC,S.GreenComponent_DEC

FROM (

SELECT * FROM SOURCE

) S

--Yes.migrate away!

--Next line gives a truncation error occurred in Sybase (gives a truncation error occurred in a query tool

--but fails silently in AseCommand.ExecuteNonQuery).

INSERT dbo.TARGET (S_Name_NVARCHAR,S_RedComponent_DEC_15_6,S_BlueComponent_DEC_12_2,S_GreenComponent_DEC_18_10)

SELECT LEFT( S.Name_NVARCHAR,8000),S.GreenComponent_DEC,S.GreenComponent_DEC,S.GreenComponent_DEC

FROM (

SELECT * FROM SOURCE

) S

select * from dbo.TARGET

[Former Member · Accepted solution]

Here's something that might help.

1) Make sure you're processing all the messages from Sybase

I've worked at many places where the first message was a warning which they printed and then neglected to print the actual errors. In this case you might not get an exception.

We also had to add "IgnoreErrorsIfRS Pening" to process all the remaining results correctly - but that meant exceptions didn't work properly. So in the end we handled all exceptions in the Error/message handler and threw errors back up the stack. Not sure if this is the prefered way of doing it - but it was the only way we could get all messages processed correctly. I'd love to know if you have more success.

2) Truncation error occured in Severity 10 - which isnt' really an error at all - its "Status Information". You could argue it should be - but there are cases when you want it to be severe and cases when you don't

I recommend not using @@error as a check - write a decent message/error handler - include deadlock handling etc.. It took us a month of trial and error to get it right - but once it works you're sorted.

BTW, Are you using Connection Pool ? If so you'll need a decent handler to clean up for the next connection pool as its not automatic.

[Former Member · Accepted solution]

Thanks for that Mike,

We're not using Connection Pool. I'll look into that. Are there any good references to point us in the right directions?

[Former Member · Accepted solution]

> Are there any good references to point us in the right directions?

I've always found documentation to be a little sparse.

The main problem for us is that there's no way to reset a connection between different uses of a connection. If you're writing a service or using grid which can run different tasks then you have to do a lot of coding around the issue to ensure the state of the connection is usable since the last process may have left it in a bad state.

MS-SQL connection pool automatically calls a proc which resets everything after a connection gets released back the pool. We've written something which does some of this but its not great.

Personally, my advise would be if you can get away from using the Connection Pool, then do so.

How are you getting on with the InfoHandler/Exceptions ?

I'd recommend writing some test case. eg multiple results set with each set throwing different severity levels - and then make sure you can handle them correctly.

[Former Member · Accepted solution]

Hi Mike,

Now that I know that some conditions are reported through the InfoMessage event I'm making progress.

In my view  "Truncation error occurred" is an error if it is followed by "Command has been aborted" as the data has not been inserted when I expected it would be, no error having been raised.

That means I can't treat messages with Severity>10 as errors as those two messages have Severity =10.  So in terms of the InfoMessage handler I'm plugging in data that I expect clients to use and seeing what messages come out when the data doesn't arrive as expected (then throwing those as exceptions), which seems a bit of a random process.

We're only making relatively limited use of Sybase (importing data into/out of Sql Server, one table at a time) so hopefully I won't have to worry about multiple result sets being returned. I'll try to avoid the ConnectionPool from what you've said.

Thanks for your input & suggestions in my various threads.

Dave

[Former Member · Accepted solution]

Glad I could help.

I agree that anything that does a Command has been aborted should be severity > 10.

BTW, I should mention the Info handler is called at the end of the proc with all the messages rather than as it goes along. This is very different from how its done in C and Perl that I've used.

The problem with this approach is that if you have any bad code doing an infinite loop or good code doing a lot of printing then the Unmanaged Code continues to build up a big list and eventually blows up with out of memory. As far as I can tell there is no work around for this.

I prefer the Perl or C method as you can then use it in long running procs to see the status of where you've got to by printing messages every few minutes.

[Former Member · Accepted solution]

Hi Mike,

I tend to agree when ASE sends the abort message it should maybe be at least 11. It might be there is a CR to log on ASE.  I don't know if this approach has been done before - the message has been like this forever, as far as I can tell.

I don't know if Info handler can be enhanced to allow access to result as they are received. It's not a well know piece of the Provider. It is something that can be investigated.

Cheers,

-Paul

[Former Member · Accepted solution]

> I don't know if Info handler can be enhanced to allow access to result as they are received

If it helps, we modified the Mono drivers to print the messages coming back from the server as they're received.

We now use Mono for our batch processes and Sybase drivers for the rest.

[Former Member · Accepted solution]

Hi Mike,

Do you have a sample of this sort of scenario? I'd like to try it out and then see if we can maybe log feature to allow the InfoMessageHandler release received results as they arrive.

Also, with discussion internally, we do handle certain sev 10 as errors (off-hand I don't know which ones) and investigating if this condition, since ASE sends abort mesg and does abort the command, should be handles in AseException. However, there is risk we might affect other applications developed that do handle with infomessage handler.

If you folks here want to consider such an enhancement (and for a more responsive handling of results when under the auspices of InfoMessageHandler, please log incident at SAP under BC-SYB-SDK and we can log them.

Cheers,

-Paul

[Former Member · Accepted solution]

Paul,

I'm going to start a separate discussion about the issues/oddities about the working of the Info handle

as its more than the Truncation issue being Severity 10.

[Former Member · Accepted solution]

Hey Mike,

Did you ever start a new thread? I am here to also vote that "Anything that causes a 'Command has been aborted' results should be a severity > 10."

I found this thread while investigating why our inserts were seemingly succeeding and yet returning a row-count of 0. I can see reasons when the results might be 0 rows, but none of them would include a "Command has been aborted" statement.

For my own education, it's too bad that we cannot know what clients this would affect as I would be very interested to know who likes it the way it is and what conditions they have where the current state meets their needs. Anyone that wants to continue on processing in an overflow can easily try-catch their way to success, but I have to add a lot of machinations in order to get it to fail as I expect it should.

[Former Member · Accepted solution]

> Did you ever start a new thread?

I started a new thread to discuss the behaviour of the C# Info handler.

> I am here to also vote that "Anything that causes a 'Command has been aborted' results should be a severity > 10."

Voting for changes ? i wish that was a consideration.

> ... our inserts were seemingly succeeding and yet returning a row-count of 0.....

Did you find out the issue here ?

What client are you using ? If it's C# - make sure you iterate through all the messages in the info handler.

> but I have to add a lot of machinations in order to get it to fail as I expect it should.

Agreed. ASE and the ADO drivers could really do with some improvements on processing errors.

[Former Member · Accepted solution]

"Did you find out the issue here ?" Yes, I believe so and why I am here...

While we do have a listener for the messages so I do see them, but it was not made to look for these messages and do much with this specific condition (we use it to monitor IO and other perf stats). We assumed that all "bad things" in terms of "attempts to modify the database not succeeding" would be throwing exceptions (silly me).

That is, I see the messages and can certainly add my own "throw," but since AseException is internal, I cannot convert them into what they "should be" in my code.

[former_member188958]

>How can I tell that a truncation error has been raised?

You can check the value of @@error just after doing the insert.

set arithignore on

go

create table t8 (x numeric(3,1))

go

begin tran

declare @x numeric(4,2)

select @x = 1.23

insert t8 select @x

select @@error as '@@error'

insert t8 values (1.1)

commit tran

select * from t8

go

(1 row affected)
Command has been aborted.
(0 rows affected)

-----------
         241

(1 row affected)
(1 row affected)
x
------
    1.1

(1 row affected)

Error 241 is "Scale error during %S_MSG conversion of %s value ’%s’ to a %s field."

[Former Member]

So after calling AseCommand.ExecuteNonQuery() I've then got to make another call to check if an error was recorded (in @@error) but not raised as an exception?

This doesn't sound right. Is this a bug?

[dawn_kim]

Hi Dave,

I did a trace I see the Truncation error in the trace.

EED Token (0xE5); variable length.

  Length [2]:                   57

  Message Number [4]:           3624

  Message State [1]:            0

  Message Class [1]:            10

  SQL State Length [1]:         5

  SQL State [5]:                "ZZZZZ"

  Status [1]:                   NO_EED (0x00)

  Transaction State [2]:        TDS_STMT_ABORT (0x0003)

  Message Length [2]:           27

  Message Text [27]:            "Truncation error occurred.

"

But no message comes back and the data doesn't get moved.

Let me work on my sample and see what I can come up with or if I need to report a bug.

Thanks,
Dawn Kim

[dawn_kim]

Hi Dave,

Okay this is expected behavior because it is considered a message from the ASE. I checked with the other drivers and it was the same thing.

Add this to your code since this isn't considered error but a message from the ASE.

       private void conn_InfoMessage(object obj, AseInfoMessageEventArgs args)

        {

            MessageBox.Show(DateTime.Now + " " + args.Errors.Count + " " + args.Message);

            MessageBox.Show("From ASE: " + args.Message);

        }

Then you can call it like this:
       conn_InfoMessage += new AseInfoMessageEventArgs(conn_InfoMessage);

ref: SyBooks Online for SDK 15.7 ADO.NET driver

Thanks,
Dawn Kim

[Former Member]

Dawn,

Does this then process all messages whatever the severity ?

In our InfoHandler we have to then work through the AseErrorCollection list and process all the messages. And then we if any of them are Severity > 10 we throw the exception up the stack otherwise the errors aren't dealt with properly. It could be were not doing this right - is there a good guide of the best practice ?

Dave, in general I'd say ignore any severity of 0 as you can get a lot of them depending on what you're doing.

JDBC has different handlers for Errors and messages which simplifies things a little.

Mike

[dawn_kim]

Hi,

I pulled the code from a sample that had triggers and put it into the Table View sample that is included in the SDK. So I am thinking this will be for all informational messages. So some other logic would have to go into it about severity level that you have mentioned. I just needed to find out if this was a bug or not.

Thanks,
Dawn Kim

[Former Member]

Sorry not sure what you're referring to here...

> I just needed to find out if this was a bug or not.

What bug are you saying might exist ?

[dawn_kim]

Hi Mike,

It isn't considered a bug, From the ASE people this is what I got.

10 is informational

10-16 affect only a user

not reported to the log

like syntax or permissions which are usually severity  11-16

Thanks,
Dawn Kim

[Former Member]

Yep - I wouldn't expect that to be a bug.

But is it a bug that the Errors go through the InfoHandler as well ?

(By Errors I mean messages from the DB with severity higher than 10)

[Former Member]

Hi Mike,

What error is going throw the infomessage handler?

Cheers,

-Paul

[Former Member]

"Truncation error occurred

Command has been aborted"

[Former Member]

Hi Dave,

I am consulting internally on this. To my understanding, this behavior is based on the ASE severity level, as returned in the TDS_EED token. AseExceptions are thrown when severity level is 11 or higher.

Our docs are not explanatory in this regard.  Once I get clarification we will provide something in the form of wiki or KBA or doc bug to further explain the detail.  Apologize for the delay and problems this caused. I guess since the command is aborted by ASE, therefore no insertion occurs, even though ASE indicates truncation occurs prior - makes this confusing.

The message severity, etc is controlled by ASE and the client is just following suit according to the TDS spec. Its just lacking in the client docs. This can be remedied.

ASE does provide the option to allow truncation and not abort.  It's a set command:

Enter a query:
1 > set arithabort numeric_truncation off

Enter a query:
1 > INSERT dbo.TARGET (S_Name_NVARCHAR,S_RedComponent_DEC_15_6,S_BlueComponent_DEC_12_2,S_GreenComponent_DEC_18_10) SELECT LEFT( S.Name_NVARCHAR,8000),S.GreenComponent_DEC,S.GreenComponent_DEC,S.GreenComponent_DEC FROM (SELECT * FROM SOURCE
) S
4 rows Affected.

Notice though ASE does NOT send truncation message. This is when it is well known and accepted to insert and truncate the numeric data.

Cheers,

-Paul

[dawn_kim]

Hi Dave,

Do you have any exception code in your application?
ref: SyBooks Online

For C#:

catch( AseException ex ) {      MessageBox.Show( ex.Message ); } 

For Visual Basic .NET:

Catch ex As AseException      MessageBox.Show(ex.Message) End Try 

Thanks,
Dawn Kim

[Former Member]

Yes, I have exception handling but no exception is being raised by the AseCommand object.