[unixODBC-support] Stored procedure messages returned in reverse cron order from DBD::ODBC mssql/EasySoft

eric.berg at barclayscapital.com eric.berg at barclayscapital.com
Tue Jul 26 23:24:28 BST 2011


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
     
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

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?

Eric


> -----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 Server\]//;
> >
> > 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?
> >
> > Eric
> 
> 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
> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
_______________________________________________

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 office at 1 Churchill Place, London, E14 5HP.  This email may relate to or be sent from other members of the Barclays Group.
_______________________________________________


More information about the unixODBC-support mailing list