SQL Server Performance

Correcting SQL Server Job with Tokens

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Nov 18, 2011.

  1. Trev256b Member

    I have to correct a job that uses a WMI token. I have read the documentation and plan to do the following:
    1) Enable token by selecting the option 'Replace tokens for all job responses to alerts'. Is this correct?
    Do I need to do the following?:
    2) The SQL Server 2005 version is higher than SP1 i.e. SP3, so therefore will I need to use an escape macro in the job step as the documentation states?: 'with SP1 jobs steps that contain tokens without escape macros will fail'
    3) Another note states: 'For the runtime token to work, you need to modify the registry by setting HKEY_LOCAL_MACHINE\SOFTWARE|Microsoft\Microsoft SQL Server\MSSQL.x\SQLServerAgent\AlertReplaceRuntimeTokens to 1'. Is this needed?
  2. Trev256b Member

    After re-reading the documentation 'Using Tokens in Job Steps' and Alerting on Database Mirroring Events' and the SQL Server error message, I think I need to do all three:
    1) Enable token by selecting the option 'Replace tokens for all job responses to alerts'.
    2) Use an escape macro, for WMI tokens specifically 'ESCAPE_SQUOTE'.
    3) Amedn registry setting HKEY_LOCAL_MACHINE\SOFTWARE|Microsoft\Microsoft SQL Server\MSSQL.x\SQLServerAgent\AlertReplaceRuntimeTokens to 1'

    Can anyone provide information to the contrary? The documentation is not specific enough to be 100% but the options by their naming suggest these need to be done. Definitely point 1), and point 2) probably applies for all service pack versions, and point 3) suggests you need to replace runtime tokens (although I suspect point 1) might give the same result. I will update you later... Please let me know if anyone has any informnation in the meantime.
  3. satya Moderator

    Not a solution tho.....
    Ok I believe the topics and information that you are getting here are absolutely generic to your environment, due to the questions you put up.
    So my suggestion is why not test the implications here and see if the results are expected as per your needs.

    You might have seen this already about how to use tokens in job steps, visit the http://msdn2.microsoft.com/en-us/library/ms175575.aspx link and also provide whether you are using any Master server and Target server concept here.
  4. Trev256b Member

    hi satya - it looks like my changes worked - as the job succeeds when it is automatically run, however I gte the error 'Unable to start execution of step 1 (reason: Variable WMI(StartTime) not found). The step failed.' - when i run the job. Is finding WMI variables a permission related thing? If so how can I check and test this? I can't find anything about WMI permissions.
  5. Trev256b Member

    Satya - my question more specifically is: do i get the error 'Unable to start execution of step 1 (reason: Variable WMI(StartTime) not found). The step failed.' when I run the job with my account instead of letting it run automatically via the service account for any reason? The code is:
    INSERT INTO dbo.[DB Mirroring State Changes] (
    [Event Time],
    [Event Description],
    [New State],
    [Database] )
    VALUES (
    $(ESCAPE_SQUOTE(WMI(StartTime))),
    '$(ESCAPE_SQUOTE(WMI(TextData)))',
    $(ESCAPE_SQUOTE(WMI(State))),
    '$(ESCAPE_SQUOTE(WMI(DatabaseName)))' )
  6. satya Moderator

    Unable to start execution..... that is more internal error and basic issue may be due to permissions or by the time it is started the process is already runing.
    You may need to scrutinize the process using profiler.
  7. Trev256b Member

    it probably is an internal error and/or permissions - any idea on how to check permissions? my account has 'sa' rights. I'm assuming to run WMI queries other rights are needed. any ideas? this isn't urgent but would be nice to understand at the deepest level.

Share This Page