SQL Server Performance

job_id -- sysjobs vs. xp_sqlagent_enum_jobs

Discussion in 'General Developer Questions' started by chopeen, Sep 15, 2005.

  1. chopeen Member

    Running SELECT job_id FROM msdb..sysjobs gives the following results - 20 job identifiers:
    /* 8616C780-14CF-419A-BDD0-25343D4925E6
    3F9EAB0E-82CF-46E2-B3FD-4556D39960C4
    59CC0FFA-1F58-474A-90CA-5A75A7B1F0EF
    5E096692-909F-4746-8A4E-5BFE0D8ADB3E
    C247A83C-D816-4422-9BBE-6268779D810A
    A2EF8708-3B77-44D3-A4E5-65AFB1209485
    B0D00D21-33CB-4E02-BDE2-71F1C355F210
    FA810974-6734-4243-9B7F-73A9AB1AAFF7
    3D9819C3-9BA1-4E6C-B0AF-8F75561388BB
    C4D40A4D-05C2-4C55-990D-95D250B63AC9
    9D49BF61-0188-40BA-806E-96AB3D860A5B
    C289E094-1CD9-4B25-BB76-A394B9D6F183
    282C05F4-D51C-419F-AE2B-AACF74AACD1C
    B1481850-C903-4570-9840-C0A7E193BF55
    17CD2C79-342B-4F21-A2EB-C4D790570066
    1B374CF3-095F-4FB8-92FE-C95884564B70
    76FE1E9A-1387-4EB2-B9D4-CC99FA7E6391
    F0F777C3-1F8E-4EC0-A62F-DDB9700E02D4
    67D1036D-71D2-4839-939B-DF47DA28ED12
    01B5F79F-99E2-4826-8D1B-FB59D704075C */

    Running EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner_name = '' gives 20 ID-s, too. However, they are different:

    /* 0x0887EFA2773BD344A4E565AFB1209485
    0x0EAB9E3FCF82E246B3FD4556D39960C4
    0x210DD0B0CB33024EBDE271F1C355F210
    0x3CA847C216D822449BBE6268779D810A
    0x4D0AD4C4C205554C990D95D250B63AC9
    0x501848B103C970459840C0A7E193BF55
    0x61BF499D8801BA40806E96AB3D860A5B
    0x6D03D167D2713948939BDF47DA28ED12
    0x740981FA346743429B7F73A9AB1AAFF7
    0x792CCD172B34214FA2EBC4D790570066
    0x80C71686CF149A41BDD025343D4925E6
    0x9266095E9F9046478A4E5BFE0D8ADB3E
    0x94E089C2D91C254BBB76A394B9D6F183
    0x9A1EFE768713B24EB9D4CC99FA7E6391
    0x9FF7B501E29926488D1BFB59D704075C
    0xC319983DA19B6C4EB0AF8F75561388BB
    0xC377F7F08E1FC04EA62FDDB9700E02D4
    0xF34C371B5F09B84F92FEC95884564B70
    0xF4052C281CD59F41AE2BAACF74AACD1C
    0xFA0FCC59581F4A4790CA5A75A7B1F0EF */

    Can anybody explain why?
    Is it possible to perform some kind of conversion between these two types of ID-s?

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
    chopeen, Sep 15, 2005
    #2

  2. dineshasanka Moderator

    Both are same

    try this


    CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
    last_run_date INT NOT NULL,
    last_run_time INT NOT NULL,
    next_run_date INT NOT NULL,
    next_run_time INT NOT NULL,
    next_run_schedule_id INT NOT NULL,
    requested_to_run INT NOT NULL, -- BOOL
    request_source INT NOT NULL,
    request_source_id sysname COLLATE database_default NULL,
    running INT NOT NULL, -- BOOL
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    job_state INT NOT NULL)



    insert into #xp_results
    exec master..xp_sqlagent_enum_jobs 1,''
    select job_id from #xp_results

    this and sysjobs will give you same answers



    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
    dineshasanka, Sep 15, 2005
    #2

  3. ghemant Moderator

    or may try this
    select cast(0x0887EFA2773BD344A4E565AFB1209485 as uniqueidentifier)


    regards




    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
    ghemant, Sep 15, 2005
    #2

  4. chopeen Member

    Thanks guys for all your help.

    A solved problem and this are the results.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
    chopeen, Sep 15, 2005
    #2

  5. chopeen Member

    quote:Originally posted by ghemant

    or may try this
    select cast(0x0887EFA2773BD344A4E565AFB1209485 as uniqueidentifier)
    A guy at microsoft.private.directaccess.sqlserver showed me how to perform the conversion in the opposite direction:

    DECLARE @b binary(16)
    DECLARE @u uniqueidentifier

    SET @b = 0x0887EFA2773BD344A4E565AFB1209485

    -- binary(16) to uniqueidentifier
    SET @u = CONVERT(uniqueidentifier, @b)
    SELECT @u

    -- uniqueidentifier to binary(16)
    SET @b = CONVERT(binary(16), @u)
    SELECT @b

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
    chopeen, Sep 15, 2005
    #2

Share This Page