RE: EXECing stored proc causes temporary DB context switch?

    I would speculate that the issue is the sp_who.  All procedures that
start with sp_ run with a database context of sybsystemprocs (sybase) or
master (sql server).
 
    Theoretically, once the proc is run, the connection should revert
back to your current database.  It appears that there is a slight delay
with this connection reset.  I would bet a sleep(1) will fix the
problem.
 
    Ed Barlow
 
    Of course... assuming you are running on sybase (because this is a
sybase list), i have no idea why your db context would be master...
 
-----Original Message-----
From: [email protected] [mailto:[email protected]]
On Behalf Of Wechsler, Steven
Sent: Tuesday, September 20, 2005 3:15 PM
To: [email protected]
Subject: DBI: EXECing stored proc causes temporary DB context switch?



	I've been experiencing this issue in a couple of scripts: 

	If I do some DBI statements, then execute the following: 

	            $sth = $dbh->prepare("select db_name()"); 
	            $sth->execute; 
	            while (@row = $sth->fetchrow_array) { 
	                print "before sp_who: $row[0]"; 
	            } 
	            $query = "exec sp_who"; 
	            $sth = $dbh->prepare($query); 
	            $sth->execute; 
	            undef $select; 
	            while (@data = $sth->fetchrow_array) { 
	                print @data; 
	            } 
	            $sth = $dbh->prepare("select db_name()"); 
	            $sth->execute; 
	            while (@row = $sth->fetchrow_array) { 
	                print "after sp_who: $row[0]"; 
	            } 
	            $sth = $dbh->prepare("select db_name()"); 
	            $sth->execute; 
	            while (@row = $sth->fetchrow_array) { 
	                print "after sp_who 2: $row[0]"; 
	            } 

	I get this: 

	before sp_who: somedb 
	[sp who output] 
	after sp_who: master 
	after sp_who 2: somedb 

	Is there any explanation for this behavior? 

	Thanks, 

	Steve