[unixODBC-support] Stored procedure messages returned in reverse cron order from DBD::ODBC mssql/EasySoft
Martin J. Evans
bohica at ntlworld.com
Wed Jul 27 19:33:57 BST 2011
On 26/07/2011 23:24, eric.berg at barclayscapital.com wrote:
> Hey, Martin. We've put together a test that is reliably reproducing
> the problem.
> I've been able to reproduce this using both EasySoft and FreeTDS data
> sources, so I'm pretty confident that it's something between unixodbc
> and the server. Straight SQL shells (i.e., db artisan) print the
> messages in the correct order. It's only with DBD::ODBC that we're
> seeing this at this point.
> And, as you'd expect when seeing an error like this, the output
> doesn't appear until the sproc is finished running.
> And btw, I'm changing the " [Easysoft][SQL Server Driver 10.0][SQL
> Server]" string to just leave "[EasySoft]".
> Here are the sprocs:
> CREATE PROCEDURE [dbo].[HS_post_message2] as begin declare @msg_cnt
> int declare @ret_cde int
> select @msg_cnt = 0 while ( @msg_cnt< 10 ) begin
> execute HS_sp_trace 'mark' select @msg_cnt = @msg_cnt +1 waitfor
> delay '00:00:01' print @msg_cnt end
> ...whcih uses this sp_trace:
> CREATE PROCEDURE [dbo].[HS_sp_trace] @msg VARCHAR(255), @errval
> INT = NULL, @numrows INT = NULL, @totalrows INT = NULL OUTPUT AS
> BEGIN DECLARE @message VARCHAR(255) IF @totalrows IS NOT NULL AND
> @numrows IS NOT NULL SELECT @totalrows = @totalrows + @numrows
> --IF USER_NAME() = 'dbo' OR dbo.site_name(DEFAULT) = 'pntdbuser'
> BEGIN SELECT @message = CONVERT( CHAR(26), GETDATE(), 109 ) + CASE
> WHEN ISNULL(@numrows,0) != 0 THEN ' : rows affected ' +
> STR(@numrows) ELSE '' END + CASE WHEN ISNULL(@totalrows,0) != 0 THEN
> ' / total rows ' + STR(@totalrows) ELSE '' END + CASE WHEN
> ISNULL(@errval,0 ) != 0 THEN ' : error ' + STR(@errval)
> ELSE '' END + + ' - ' + @msg --PRINT @message RAISERROR(@message, 0,
> 1) WITH NOWAIT END
> IF @errval != 0 AND @errval IS NOT NULL RETURN @errval
> RETURN END
I've not looked into these procedures in detail but hopefully I can
create them here and reproduce your problem.
> Which produces this output using unixodbc 2.3.0:
> [FreeTDS]10 [FreeTDS]Jul 26 2011 6:02:03:433PM - mark [FreeTDS]9
> [FreeTDS]Jul 26 2011 6:02:02:433PM - mark [FreeTDS]8 [FreeTDS]Jul 26
> 2011 6:02:01:433PM - mark [FreeTDS]7 [FreeTDS]Jul 26 2011
> 6:02:00:433PM - mark [FreeTDS]6 [FreeTDS]Jul 26 2011 6:01:59:430PM -
> mark [FreeTDS]5 [FreeTDS]Jul 26 2011 6:01:58:430PM - mark
> [FreeTDS]4 [FreeTDS]Jul 26 2011 6:01:57:430PM - mark [FreeTDS]3
> [FreeTDS]Jul 26 2011 6:01:56:430PM - mark [FreeTDS]2 [FreeTDS]Jul 26
> 2011 6:01:55:430PM - mark [FreeTDS]1 [FreeTDS]Jul 26 2011
> 6:01:54:430PM - mark
> What do you think?
Not sure right now. I will look at this properly tomorrow morning.
How are you calling the procs - what is the Perl code?
Thanks for producing a (hopefully) self contained example.
Please ping me if you don't hear from me by lunchtime UK time tomorrow.
>> -----Original Message----- From:
>> unixodbc-support-bounces at mailman.unixodbc.org [mailto:unixodbc-
>> support-bounces at mailman.unixodbc.org] On Behalf Of Martin J. Evans
>> Sent: Thursday, July 21, 2011 12:41 PM To: Support for the unixODBC
>> project Subject: Re: [unixODBC-support] Stored procedure messages
>> returned in reverse cron order from DBD::ODBC mssql/EasySoft
>> On 20/07/2011 19:11, eric.berg at barclayscapital.com wrote:
>>> I have an odbc_err_handler set to a very simple subroutine that
>>> strips off the Vendor and driver info and the just prints it to
>>> the console. It looks like this:
>>> $msg =~ s/\[Easysoft\]\[SQL Server Driver 10.0\]\[SQL
>>> warn $msg . "\n";
>>> return 1;
>>> we also use this command all over our sprocs:
>>> RAISERROR(@message, 0, 1) WITH NOWAIT
>>> Which generates "error" messages that hit this odbc_err_handler.
>>> The problem is that for MSSQL (DBD::ODBC/Easysoft), we are
>>> receiving the output of this command in reverse cron order after
>>> the sproc completes.
>>> Anyone run into this problem?
>> Sorry Eric, but I've only just seen your post.
>> Are you suggesting that if you call RAISERROR('one') then
>> RAISERROR('two') the odbc_err_handler sees two before one.
>> If so I'm not aware of this problem.
>> Perhaps you could knock up a small self-contained example and I'll
>> take a look at it.
>> Martin -- Martin J. Evans Wetherby, UK
>> _______________________________________________ unixODBC-support
>> mailing list unixODBC-support at mailman.unixodbc.org
> This e-mail may contain information that is confidential, privileged
> or otherwise protected from disclosure. If you are not an intended
> recipient of this e-mail, do not duplicate or redistribute it by any
> means. Please delete it and any attachments and notify the sender
> that you have received it in error. Unless specifically indicated,
> this e-mail is not an offer to buy or sell or a solicitation to buy
> or sell any securities, investment products or other financial
> product or service, an official confirmation of any transaction, or
> an official statement of Barclays. Any views or opinions presented
> are solely those of the author and do not necessarily represent those
> of Barclays. This e-mail is subject to terms available at the
> following link: www.barcap.com/emaildisclaimer. By messaging with
> Barclays you consent to the foregoing. Barclays Capital is the
> investment banking division of Barclays Bank PLC, a company
> registered in England (number 1026167) with its registered offi! ce
> at 1 Churchill Place, London, E14 5HP. This email may relate to or
> be sent from other members of the Barclays Group.
> _______________________________________________ unixODBC-support
> mailing list unixODBC-support at mailman.unixodbc.org
Martin J. Evans
More information about the unixODBC-support