|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
msdb.dbo.sp_help_job current_execution_step always returns 0 (unknown)In SQL Server 2005, unless you belong to the sysadmin server role, executing
msdb.dbo.sp_help_job will always return "0 (unknown)" in the current_execution_step column of first dataset returned. Granting the user membership to the SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole does not make a difference. Is this fixed in Service Pack 1? Hello,
Based on my test, this issue is by design and still in SQL Server 2005 SP1. According to the Books online, only the sysadmin could view the current step informaton of a job. sp_help_job (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms186722.aspx SQL Server Agent Fixed Database Roles http://msdn2.microsoft.com/en-us/library/ms188283.aspx Sincerely, Wei Lu Microsoft Online Community Support ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== (This posting is provided "AS IS", with no warranties, and confers no rights.) I appreciate the response. I've read through those BOL entries before, so
maybe I'm missing something, or I haven't had enough coffee yet, but I can't seem to find a note indicating that only users with the sysadmin role could view the current job step. In the event that this is indeed by design, are there any workarounds that I can use to retrieve the step information without the user being added to the sysadmin role? Hello,
I did a deep research on how msdb.dbo.sp_help_job works. Finnally, I found that msdb.dbo.sp_help_job invoke the system extended stored procedure master.dbo.xp_sqlagent_enum_jobs and pass the parameter @is_sysadmin. So my conclusion is unless you add the user to the sysadmin role, you could not retrieve the step information for the job. You may try to dig into the system stored procedure sp_help_job to find whether there are any helpful information for you. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. Hi ,
How is everything going? Please feel free to let me know if you need any assistance. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.  |
|||||||||||||||||||||||
Other interesting topics