RE: Testing for query success

Actually, there is still one thing I don't understand. If I copy the
failing statement from my code and paste it into sqsh it works - a
select shows the data and I get no 'Arithmetic overflow error converting
expression to data type datetime.The statement has been terminated.'
error message printed to the screen.

In my code, the exact same statement (SP) doesn't upload the data to the
database and does print the message stated above!

This is still confusing me considering because essentially the code is
identical under the hood. Is it possible this is a date-formatting env
variable or something?

Regards

Jim

On Wed, 2005-01-12 at 11:14 +0000, Thompson, Bill D (London) wrote:
> Jim,
> 
> Shortish answer:
> 
> in SQL Server there are a number of types of error condition.
> Some will terminate a whole batch, whereas others will terminate a
> statement within a batch (or a statement within a stored procedure for
> that matter)
> Judging by the text of the error message This error falls into the
> latter case.
> In this latter case, within a stored procedure, it's up to you to trap
> errors and adjust the logic accordingly.
> 
> You might for example code the following after each statement of a
> stored procedure:
> 
> if @@error <> 0
> begin
>    return -1
> end
> 
> If you choose not to trap errors like this, the stored procedure will
> continue executing.
> 
> The error message is being returned by freetds, because the server
> pushes any error message generated in the course of a batch execution
> back to the client.
> Most client applications then print these out using an "error handler" -
> sqsh certainly does this.
> But neither freetds or sqsh (which then handles the message by printing
> it out) use the presence of a message to "flag" an error in any way.
> 
> Bill
> 
> P.S. The underlying cause of the error message is likely to be more
> complicated.
> 
> 
> -----Original Message-----
> From: freetds-bounces@xxxxxxxxxxxxxxxxx
> [mailto:freetds-bounces@xxxxxxxxxxxxxxxxx] On Behalf Of James Vanns
> Sent: 12 January 2005 11:03
> To: FreeTDS Development Group
> Subject: [freetds] Testing for query success
> 
> 
> Dear all,
> 
> First a happy new year to you. Now, to the point! I am issuing an UPDATE
> statement (actually I am calling a stored procedure that issues this
> statement) against a Microsoft SQL Server (2000).
> 
> I am testing this using sqsh also...
> 
> Basically, this SP succeeds yet I also get an error message returned
> (not a record set):
> 
> 'Arithmetic overflow error converting expression to data type
> datetime.The statement has been terminated.'
> 
> As I said - the update succeeds and the date format is accepted and
> stored fine (format was: 2003-04-11 17:29:27).
> 
> So 
> 
> a) Why is this message returned by FreeTDS?
> b) If this error message is returned how come the SQSH (and therefore
> FreeTDS?) program prints '(return status = 0)' also? Doesn't 0 signify
> success?
> 
> Any direction you can give me to the solution to this will be
> appreciated. I.e. what dblib or even tds functions should I be calling
> to test for the real success or failure of my statement?
> 
> Regards
> 
> Jim Vanns
> 
> PS: This is FreeTDS 0.62
> 
-- 
James Vanns BSc (Hons) MCP
Linux Systems Administrator
Software Engineer (Linux / C & C++)
Canterbury Christ Church University College
Public Key: http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x24045370