SQL Server, ENT manager job status

microsoft.public.sqlserver.server - Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.


SQL Server >> ENT manager job status

by Red » Wed, 15 Sep 2004 05:43:46 GMT

Hey all,
I was wondering if someone could point me to correct data in the msdb
database. Here is what I'm looking for. I want to be able go from server to
server and check the status of all the jobs that are enabled. I know I can
(and do) use ENT. Manger to do this but, hey, it tedious work. Open server,
open management, open SQL sever agent, click on jobs.....
I'd rather run query, from a my desk top, that will connect to the
servers I need and run this query.

Hope that is clear enough.

TIA

Red



Top

SQL Server >> ENT manager job status

by Hari Prasad » Wed, 15 Sep 2004 09:39:02 GMT


Hi,

You could query the sysjobs table in MSDB database.

select name , enabled from msdb..sysjobs where enabled=1

You could run this query using OSQL command line utility

OSQL -S Server1 -Usa -Ppassword -Q"select name , enabled from msdb..sysjobs
where enabled=1" >c:\result.txt
OSQL -S Server2 -Usa -Ppassword -Q"select name , enabled from msdb..sysjobs
where enabled=1" >>c:\result.txt

Thanks
Hari
MCDNA








Top

SQL Server >> ENT manager job status

by Mark Allison » Wed, 15 Sep 2004 21:21:38 GMT

Red,

Hari's suggestion is a good one for a small number of servers. If you
have a great number of servers, perhaps a VBScript would do it which
uses SQL-DMO to iterate through all your registered servers in Enteprise
Manager.

Just recursively loop through the ServerGroups collection, and then the
RegisteredServers collection. Then for each RegisteredServer, execute
your script.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html





Top
Similar Threads

1. Job Status not reporting in Enterprise Manager (SQL Server 2000) 

Jobs created with by Server Administrator Account 
privilege does not show current execution status. But 
when sp_help_job is run from query analyzer the 
current_execution_step shows 1 -- currently running. This 
issue started occuring gradually and now happens on a 
constant basis. However if I log onto to the  Windows 
2000 server directly using terminal services or console I 
see the running jobs status 95 percent of the time. I use 
trusted connection. SQL Server 2000..Any ideas.

2. A DTS step freezes (status is just "Executing Job Step 'job name'") - SQL Server DTS

3. Connect to SQL Database on the ISA Server through Ent Manager

4. Connect to SQL Database on the ISA Server through Ent Manager on m

5. Access Denied to WMSDE in SQL Ent Manager 

I am attempting to register the WMSDE WSS Database using 
SQL Server Enterprise Manager, I run through the setup 
wizard, point to the database, then when it finally tries 
to connect to the db I receive the error message for 
access denied. When we first setup WSS we didn't specify 
an administrator or password, thinking this I had the user 
that installed WSS try to perform the same method in 
Enterprise Manager but had no luck. Is there a default 
admin account that we are missing? It has been a while 
since he had installed this software and he has probably 
changed his password (company policy) since that time, 
could it be looking for his old password even though it is 
using windows domain auth?

6. Timeouts when running SQL queries in SQL Ent Manager - SQL Server

7. How to move new column to a different position in replicated table using Ent Manager 

I have a simple transactional replication test bed set up using SQL Server
2000 and I want to add a column to a table that islocated on the Publisher.
After looking thru BOL, I found the "Filter Columns" tab on the Publication
Properties dialog box and was finally able to add a new column into this
table with the Add Column to Replicated Table dialog. (even tho my T-SQL
scripting is real bad!)

To test this I added a simple column with a datatype of Int into the table.
The add goes fine but the new column gets placed at the bottom of the table
column listing. If possible, I would like the new column to be inserted at
position 30 in this 50 column table. If I try to move the column using drag
and drop in the Design Table dialog and then try to save the table...I get
an immediate error:

'OrdMain' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.OrdMain' because it is being used for replication.

I am fairly new to T-SQL scripting and if anyone has any Transact SQL script
they could share to fill the "Column Definition" area, I would really
appreciate it. Here's the default T-SQL (ALTER TABLE) elements that get put
in when adding the column:

<data_type>
DEFAULT <constant_expression>
IDENTITY <seed>, <increment> NOT FOR REPLICATION
ROWGUIDCOL
<column_constraint>

Cheers,

Bruce McDonald

8. Alter table via t-sql vs Ent Manager - SQL Server