RE: EXECing stored proc causes temporary DB context switch?

May be because sp_who is looking up master..sysusers and
master..sysprocesses to get the sp_who info
 
-R


  _____  

From: [email protected] [mailto:[email protected]] On
Behalf Of Barlow, Ed
Sent: Tuesday, September 20, 2005 3:31 PM
To: Wechsler, Steven; [email protected]
Subject: 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