SQL print statements in a stored proc

Hi,

I am connecting to SQL Server using ODBC and executing a stored
proc. Its well known that select output and print / dbcc outputs use
different channels. To see the SQL Print output, one has to repoint
the odbc_err_handler to a custom error handler and the sql Print
outputs will be seen when the script is run. I have this script which
works fine:

use strict;

use DBI;
my $data_source = q/dbi:ODBC:Server_XYZ/;
my $user = q/usr/;
my $password = q/usr_ps/;
my $dbh = DBI->connect($data_source, $user, $password)
or die "Can't connect to $data_source: $DBI::errstr";

# Catch and display status messages with this error handler.
sub err_handler {
my ($sqlstate, $msg, $nativeerr) = @_;
# Strip out all of the driver ID stuff
$msg =~ s/^(\[[\w\s:]*\])+//;
print $msg;
print "===> state: $sqlstate msg: $msg nativeerr: $nativeerr\n";
return 0;
}

$dbh->{odbc_err_handler} = \&err_handler;

$dbh->{odbc_exec_direct} = 1;

$dbh->do("use Scratch");

my $sql = q/create proc p_print_messages as
print 'Inside stored proc'
select 1
print 'Exiting stored proc'
/;

$dbh->do($sql);
my $sth = $dbh->prepare("{ call p_print_messages }");
$sth->execute;
do {
while (my @row = $sth->fetchrow_array) {
if ($row[0] eq 1) {
print "This is the SELECT output\n";
}
}
} while ($sth->{odbc_more_results});

$dbh->do(q/drop procedure p_print_messages /);

$dbh->disconnect;

This works fine. I can see both the print outputs. But If I remove the
select statement in the stored proc, it outputs only the first print
statement. When there are multiple print statement without intervening
select statements, it prints only the first print statement. What
gives?
Actually, when there are multiple print statements together without
any select statement, it outputs the first print output and then I get
this error:
this is the end===> state: 01000 msg: this is the end nativeerr: 0
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement
currently executi
ng err=-1) at test_db.pl line 41.
Function sequence error===> state: HY010 msg: Function sequence
error nativeer
r: 0
Describe failed during DBI::st=HASH(0x1a657a0)-
FETCH(odbc_more_results,0) at te
st_db.pl line 40.

Any ideas ?

thx

.

Relevant Pages

  • Re: Suppressing Alerts
    ... If you're using an error handler, be sure to put the DoCmd.SetWarnings True ... the warnings alert messages will be turned ... >I am executing a query that creates a table from a TEMPLATE table. ... > DoCmd.RunSQL SQL ...
    (microsoft.public.access.formscoding)
  • Re: how to pass an sort by parameter to a stored proc
    ... When executing a string the are not optional. ... Also if security is of any concern, this proc is very exposed to SQL ... >> CREATE PROCEDURE spListUsers ...
    (microsoft.public.sqlserver)
  • Re: Global Temp Table & Multiple Users
    ... design and I'm not sure if performance would be any better. ... about serialization is that it limits the dbms pounding that these reports ... what is happening is this a dynamic SQL ... > The inner proc generates a portion of the SQL, ...
    (microsoft.public.sqlserver.server)
  • RE: Procedure Name
    ... Events alwasy start a new nesting sequence -- thus if you raise and error in the Error Handler of an Event, you get the ugle standard message box. ... > user reports an error, I can immediately identify what caused it). ... > and kill the proc that caused it). ...
    (microsoft.public.access.modulesdaovba)
  • Re: Urgent: Permissions Problem with Dynamic SQL
    ... > having problems with permissions when using dynamic sql. ... > CREATE PROC a2 AS ... > GRANT EXECUTE ON a2 TO user1 ...
    (microsoft.public.sqlserver.security)
Loading