sybperl-l Archive

From: "Wechsler, Steven" <Steven dot Wechsler at mtvstaff dot com>
Subject: RE: EXECing stored proc causes temporary DB context switch?
Date: Sep 20 2005 8:01PM

It happens with any system stored proc (well, I shouldn't say "any", but
sp_helprotect, which goes only against tables in the current DB, caused
the same problem).
 
I'm running as 'sa', so master is the default DB.

________________________________

From: Srinivasan, Ravikumar [mailto:ravikumar.srinivasan@gs.com] 
Sent: Tuesday, September 20, 2005 3:38 PM
To: 'Barlow, Ed'; Wechsler, Steven; sybperl-l@peppler.org
Subject: 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: owner-sybperl-l@peppler.org
[mailto:owner-sybperl-l@peppler.org] On Behalf Of Barlow, Ed
	Sent: Tuesday, September 20, 2005 3:31 PM
	To: Wechsler, Steven; sybperl-l@peppler.org
	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: owner-sybperl-l@peppler.org
[mailto:owner-sybperl-l@peppler.org] On Behalf Of Wechsler, Steven
	Sent: Tuesday, September 20, 2005 3:15 PM
	To: sybperl-l@peppler.org
	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