Schedule Job Failure

[scking]

I've a set of stored procedures that operate without error from the application and query analyzer. When the job is scheduled it fails without any real description of the problem in the Windows event log. What would be a good method to troubleshoot these failures? These procedures perform inserts and deletes using data acquired from another database application on the same server.


---------------------
scking@arinc.com
---------------------

 

[Corran007]

It is proably a permissions issue. what type and what user is the job being run as? does the user it is being run as have permission to perform those operations?

 

[scking]

I'm running as the dbo. The log pretty much cleared up my problem. It was on a remote computer administered by someone at their location so I needed to use the Computer Management Snap In to locate some information. Once done the log showed the following:
------------------------------------------------------

Database state cannot be changed while other users are using the database 'AlarmSQL' [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.



---------------------
scking@arinc.com
---------------------

 

[Catadmin]

FYI: I found another reason scheduled jobs might fail is if their owner isn't set as SA in the job properties, but is set as a normal SQL or Windows user.

Hope that helps someone. @=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!