Post reply

uniqueidentifier in xp_sqlagent_enum_jobs

  • December 7, 2004 at 2:29 pm

    #87234

    I want to link a job that is running with a process in sysprocess. For it the process_name in sysprocesses will be something like: "SQLAgent - TSQL JobStep (Job 0x5D28E67698A3194197B09FAF1662E791 : Step 1)"                                                  

    I want to isolate that job_id hexa number in some varchar variable to compare, but I don't know how. Can anyone give me any idea?

    Please see below for details:

    I created a job for test which got job_id=76E6285D-A398-4119-97B0-9FAF1662E791 in sysjobs.

    If I run:

    declare @v_jobid uniqueidentifier

    select @v_jobid=job_id from msdb..sysjobs where name='TEST long job'

    exec master..xp_sqlagent_enum_jobs 1,'',@v_jobid

    this will display:

    Job ID                            

    --------------------------------------------0x5D28E67698A3194197B09FAF1662E791

    if I put the result in a table with the following structure (as in msdb..sp_get_composite_job_info):

      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)

    declare @v_jobid uniqueidentifier

    select @v_jobid=job_id from msdb..sysjobs where name='TEST long job'

    insert into #xp_results

    exec master..xp_sqlagent_enum_jobs 1,'',@v_jobid

    select job_id from #xp_results

    will show:

    job_id                              

    ------------------------------------

    76E6285D-A398-4119-97B0-9FAF1662E791

    which is the same value in sysjobs and is not the same thing shown in the preocess_name when job runs.

    I have no clue how to get the 0x stuff in a character string.

    Any help will be appreciated.

    Thank you,

    Gabriela

     

  • December 8, 2004 at 7:34 am

    #533033

    I did a little research on this forum last night and it seems I found my answer in a posting sometimes in February 2004. The below code will to the conversion:

    master.dbo.fn_varbintohexstr(convert(binary(16),job_id ))

    Gabriela

     

  • October 27, 2005 at 9:53 am

    #600633

    Ah, the old unix password trick.

    generate the hex # from the job ids.

    took about 15 seconds and I found it.

    DOH!

     

  • January 23, 2006 at 4:43 am

    #616379

    Hi,

    I want to get the Job_ID through the program. I have written a code snippet which is given below:

    _bstr_t bstrJobId = m_pRecordsetJob->Fields->GetItem(L"Job ID")->GetValue();

    The above code returns the value of "Job ID" column which seems to be garbage. How should I convert the same to view the content of the "Job ID" column.

    I have tried to use the same in the function "master.dbo.fn_varbintohexstr()" but while using the variable "bstrJobId" it returns some garbage value. Can we convert the same into string representation?

    Reply ASAP.

    Thanks,

    Pramod

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply