Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like t
o
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_nam
e'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get t
he
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related t
o
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, rene
Hi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
sql

check Access table exists from SSIS

I am writing my first SSIS task. I have an MS Access database and I'm moving the contents of each table into a like-named SQL Server database. The task is working properly except for one step.

The source Access database may or may not have one of the tables in it. It will always have TableA, TableB, and TableC, but may or may not have TableD. I need to write my SSIS package in such a way that it will detect the presence/absence of TableD and either run or skip the import step for this table accordingly.

Can someone assist with the step that detects the table presence/absence in the MSAccess database?

thanks,

matt tag

easy.

I never work with Access/Excel, but can you run an Execute SQL task against it and populate an Integer variable with the result set?

This is the query. You'll need to map a parameter, or simply hard code the table name in place of the "?" below. If the output is greater than zero (which you can test in a precedence constraint) then the table exists, if not then the table does not exist:

SELECT count(*) as TableExists
FROM MSysObjects
WHERE MSysObjects.Type=1
and Name = ?|||

Matt,

The post of the Phil is excellent, but you must do it inside the control flow. Although you have a control error for the OLE DB Source that you can redirect to do something in the case of the source does not exist...

Regards,

Pedro