April 16, 2008 at 3:09 pm
Two more great tips, thanks.
BTW to run the query, you need to enable 'xp_cmdshell' in Surface Area Configuration.
April 17, 2008 at 6:02 am
On my SQL 2005 Developer installation, sysprocesses is no table, but a view (master.sys.sysprocesses), which ruled out the first answer to me (as I did only check if the table existed).
Peter Rijs
BI Consultant, The Netherlands
April 17, 2008 at 9:09 am
I also found that you can't just query sysprocesses (unlike sysobjects), you have to specify sys.sysprocesses.
Derek
April 17, 2008 at 9:47 am
Derek,
I wonder why it doesn't work the same way for you.
I am running MSSQL 2005 SP2 Developer on Win XP Pro SP2. I open SSMS, click New Query & connect to master using Windows authentication (logged in to XP with administrator credentials) & run the query
select * from sysprocesses where program_name like 'SQLAgent%'
and it returns the one row, as previously stated. I don't need to specify the "sys." in order for it to work.
Is this another Edition or config difference?
April 18, 2008 at 1:37 am
"select * from sysprocesses" works in 'master'. In any other database I need "select * from sys.sysprocesses". SSMS only highlights it if it's got the sys prefix.
Derek
April 18, 2008 at 1:59 am
come on, Derek. 😉
Why would you try running a query about system objects in a database other than master?
Kelsey Thornton
MBCS CITP
April 18, 2008 at 2:15 am
Are we looking too much into the questions and answers provided? Or do we think that the questions should be accurately deterministic? I believe a healthy debate/discussion is always good 🙂
April 18, 2008 at 2:38 am
Kelsey Thornton (4/18/2008)
come on, Derek. 😉Why would you try running a query about system objects in a database other than master?
Habit. 🙂
Novices start by firing up SSMS and running every query in 'master'. After they've cluttered up the master database on their development server, they realise that perhaps they should create another database (or maybe even two or three) for applications and testing.;)
I'm almost at the other extreme where my habitual action is click [New Query], type <U><s><e><space><s><c><r><a><t><c><h><enter><s><e><l><e><c><t>...:cool:
Derek
April 18, 2008 at 3:59 am
Rajan John (4/18/2008)
Are we looking too much into the questions and answers provided? Or do we think that the questions should be accurately deterministic? I believe a healthy debate/discussion is always good 🙂
I've always felt that I learn more from some of the 'ambiguous' QOTDs than the simple 'how do you do X' type.:)
Derek
April 18, 2008 at 8:33 am
Derek Dongray (4/18/2008)
Kelsey Thornton (4/18/2008)
come on, Derek. 😉Why would you try running a query about system objects in a database other than master?
Habit. 🙂
Novices start by firing up SSMS and running every query in 'master'. After they've cluttered up the master database on their development server, they realise that perhaps they should create another database (or maybe even two or three) for applications and testing.;)
I agree with Kelsey, if you're querying system objects you would use master. If you're querying backup & restore history you would use msdb.
Think of Sutton's Law: The law is named after the bank robber Willie Sutton, who supposedly answered a reporter inquiring why he robbed banks by saying "because that's where the money is."
OTOH if you are actually creating new objects for testing, a separate test db makes perfect sense.
April 18, 2008 at 8:46 am
I trust you all realise I was talking tongue-in-cheek there!;)
Kelsey Thornton
MBCS CITP
April 23, 2008 at 9:06 am
I think answer should be
xp_ServiceControl 'QueryState', 'SQLServerAgent'
this because if no job will run then sysprocesses will not have any process related to that service. So, i think answer as SQl Server Central Guys has selected is wrong. As per my suggestion they should correct the answer.
Thanks & Regards
Rajni Kant Ranjan
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply