uniqueidentifier in xp_sqlagent_enum_jobs

Question

uniqueidentifier in xp_sqlagent_enum_jobs

Gabriela Nanau

SSCommitted

Points: 1967
More actions
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

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

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

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 1

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

alex hatcher SSC Journeyman Points: 75 More actions · Answer 2

Ah, the old unix password trick.

generate the hex # from the job ids.

took about 15 seconds and I found it.

DOH!

Pramod Ingle Grasshopper Points: 10 More actions · Answer 3

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