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 job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisR
Take a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_status
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>
|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>

check job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisRTake a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_status
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>>I want to have a query that can tell me all the jobs on a server that have
>>failed and need to be run again in the future. This is what I have come up
>>with:
>>select Server = 'BoxName', SJ.Name
>>from BoxName.msdb.dbo.SysJobHistory SJH
>>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>>where SJH.run_status = 0
>>and SJS.enabled = 1
>>and SJS.next_run_date <> 0
>>and SJS.next_run_time <> 0
>>group by Server, SJ.Name
>>
>>The problem though is it would appear that SQL doesn't update the
>>run_status
>>in the SysJobHistory table very often as I am getting values returned from
>>this query that did fail several hours ago, but have since succeeded. Does
>>anyone know of a better way to write this query? I know I can setup
>>Alerting, but need a backup for it.
>>TIA, ChrisR
>

check job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisRTake a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_statu
s
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.
4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
>

Check if MSDE Installation is done

Hi everybody,
I already wrote about our job to build an setup installation including:
..net, msde, my application (using WISE InstallBuilder).
As everyone know, the MSDE setup makes a fork process and return the control
to the next step.
The next step is to install the DB, but I've to wait for it until the MSDE
setup is done.
I read on this NG something about it, but we tried with another solution:
After MSDE setup, wise installer makes a loop until he's able to start the
instance service.
When the Service is started up it's possible to work by OSQL tool.
On Windows98 OS we're not able to check if a service is running or not.
Where are we getting wrong ?
Thank in advance, Pierluigi.
Windows Millennium Edition and Windows 98 do not have a component that
corresponds to Window NT or Windows 2000 services. The SQL Server database
engine and SQL Server Agent run as executable programs on Windows Millennium
Edition and Windows 98.
Dylan
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> I already wrote about our job to build an setup installation including:
> .net, msde, my application (using WISE InstallBuilder).
> As everyone know, the MSDE setup makes a fork process and return the
control
> to the next step.
> The next step is to install the DB, but I've to wait for it until the MSDE
> setup is done.
> I read on this NG something about it, but we tried with another solution:
> After MSDE setup, wise installer makes a loop until he's able to start the
> instance service.
> When the Service is started up it's possible to work by OSQL tool.
> On Windows98 OS we're not able to check if a service is running or not.
> Where are we getting wrong ?
> Thank in advance, Pierluigi.
>
|||Hi Dylan,
Thanks, we just realised about it a few minutes ago.
How can I know when the MSDE installation is done on W/98 using batch file
or Wise InstallBuilder ?
Pierluigi.
"Dylan Kruger" <carmellobear1@.hotmail.com> ha scritto nel messaggio
news:e8DgIDaZEHA.1048@.tk2msftngp13.phx.gbl...
> Windows Millennium Edition and Windows 98 do not have a component that
> corresponds to Window NT or Windows 2000 services. The SQL Server database
> engine and SQL Server Agent run as executable programs on Windows
Millennium[vbcol=seagreen]
> Edition and Windows 98.
> Dylan
> "Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
> news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> control
MSDE[vbcol=seagreen]
solution:[vbcol=seagreen]
the
>
|||Ok,
after trying several times we were able to find a way to do it.
Maybe could be useful for something:
- we launch the MSDE setup with a log file (an error file)
- after launching we loop until the log file is not in use
So, we can mount the database and install out application.
Thanks again for yours suggestions.
Pierluigi.
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel messaggio
news:%23jzdAIaZEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Dylan,
> Thanks, we just realised about it a few minutes ago.
> How can I know when the MSDE installation is done on W/98 using batch file
> or Wise InstallBuilder ?
> Pierluigi.
> "Dylan Kruger" <carmellobear1@.hotmail.com> ha scritto nel messaggio
> news:e8DgIDaZEHA.1048@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> Millennium
including:[vbcol=seagreen]
> MSDE
> solution:
> the
not.
>
|||You can handle msde2000 setup process if you use original msde setup files.
In documentation evens says that you can handle callback msi return codes.
Type Callback = mydll!mycallbackfunction.
Bu ti don't know how to do it in vb.
Try this one :
"e:\msde2000setup\installdir\SETUP\SQLRUN01.MS I" /settings
"D:\develop\servis\setup.ini" /i /L*v "D:\develop\servis\MSDE_setup.log"
CmdLine = "e:\msde2000setup\installdir\SETUP\SQLRUN01.MS I" /settings
"D:\develop\servis\setup.ini" /i /L*v "D:\develop\servis\MSDE_setup.log"
You can easily get required dll declarations on the net.
Or in vb :
start.cb = Len(start)
ret = CreateProcessA(0&, CmdLine, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&,
0&, start, Proc)
i = 0
Do
ret = MsgWaitForMultipleObjects(1&, Proc.hProcess, 0&, INFINITE, _
(QS_POSTMESSAGE Or QS_SENDMESSAGE))
If ret = (WAIT_OBJECT_0) Then
Exit Do 'The process ended.
End If
OpenForms = DoEvents()
If i = 99999 Then
CloseHandle (Proc.hProcess)
sErrMsg = "Not responding"
End If
i = i + 1
Loop
ret = CloseHandle(Proc.hProcess)
ExecCmd = True
Regards
|||And the (poorly documented) /wait switch to the setup.exe command line for
the MSDE installation. This will force the process to wait for the MSDE
installation to finish and return the return code of the MSDE installation.
Jim
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> I already wrote about our job to build an setup installation including:
> .net, msde, my application (using WISE InstallBuilder).
> As everyone know, the MSDE setup makes a fork process and return the
control
> to the next step.
> The next step is to install the DB, but I've to wait for it until the MSDE
> setup is done.
> I read on this NG something about it, but we tried with another solution:
> After MSDE setup, wise installer makes a loop until he's able to start the
> instance service.
> When the Service is started up it's possible to work by OSQL tool.
> On Windows98 OS we're not able to check if a service is running or not.
> Where are we getting wrong ?
> Thank in advance, Pierluigi.
>
sql

Check if job is running?

Hi,
I have a stored procedure that needs to start a job stored in sql =
server. How can I check if this job is already started before trying to =
start it? Right now, users are getting the "can't start job because it =
is started" error, and I'd like to get rid of that.
Thanks!
--MichaelLook here:
http://www.databasejournal.com/feat...cle.php/3491201
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
Hi,
I have a stored procedure that needs to start a job stored in sql server.
How can I check if this job is already started before trying to start it?
Right now, users are getting the "can't start job because it is started"
error, and I'd like to get rid of that.
Thanks!
--Michael|||http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Raterus" wrote:

> Hi,
> I have a stored procedure that needs to start a job stored in sql server. How can
I check if this job is already started before trying to start it? Right now, users
are getting the "can't start job because it is started" error, and I'd like to get
rid
of that.
> Thanks!
> --Michael
>|||Thanks, this is exactly what I was looking for!
"Jens S=FC=DFmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> =
wrote in message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
>=20
> http://www.databasejournal.com/feat...cle.php/3491201
>=20
> HTH, Jens Suessmeyer.
>=20
> --
> http://www.sqlserver2005.de
> --
>=20
>=20
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag=20
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql =
server.=20
> How can I check if this job is already started before trying to start =
it?=20
> Right now, users are getting the "can't start job because it is =
started"=20
> error, and I'd like to get rid of that.
>=20
> Thanks!
> --Michael=20
>=20
>|||Nice to get a feedback :-)
"Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
news:%23WoN0QFRFHA.4028@.tk2msftngp13.phx.gbl...
Thanks, this is exactly what I was looking for!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:e2mmWKFRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Look here:
> http://www.databasejournal.com/feat...cle.php/3491201
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Raterus" <raterus@.hotmail.com> schrieb im Newsbeitrag
> news:OqpbhGFRFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a stored procedure that needs to start a job stored in sql server.
> How can I check if this job is already started before trying to start it?
> Right now, users are getting the "can't start job because it is started"
> error, and I'd like to get rid of that.
> Thanks!
> --Michael
>

Check for end of job

Hi everyone,

I'm quite a newbie to SQL and DTS and I need your help!
HTe situation:
I've got an Access97 frontend with an SQL7 backend. On the SQL-server I created a job (to execute a DTS package) which is triggered by a custom-error. From the Access-side I raise this error to start excuting the DTS. I do that this way: execute master..xp_logevent 50001, LOG

This works fine, the job runs and everything, however I need to know when the job has ended. The code that is after this call in Access97 needs to be sure that the DTS-package has finished executing.

How do I do that, please bear in mind that I'm quite a newbie so simple language please :)

TIA, RaymondMay be the last step in DTS populates a field in some table, and your Access 97 front end keeps checking that table to see if that field has been updated or not.

check diskspace UNC via T-SQL

Hello,
I have an EM Job script that backups up my databases using SQLsafe to a
target UNC.
Sometimes, the target server that will store the backups is off line or
it's disk is full.
I am wondering how I can check to see if the machine is up via the job
and secondly, check the disk space.
If either check fails, I would then check another machine machine.
Any ideas appreciated,
Thanks
Rob
SQL 2000 Server and Enterprise, Windows 2003
SQL 2005 Server and Etnerprise, Windows 2003
Target storage is a Windows 2003 and I connect via UNCYou can use a couple of extended procs to get what you want. I have a
stored proc that jumps through some hoops to give me that information.

First I call: EXEC master.dbo.xp_availablemedia

That returns a list of devices on the database server. I loop over the
results from that and do:

EXEC master..xp_cmdshell 'DIR /-C <drive>'

and I look for the line that has "bytes free" and parse that for the
number.

It's not terribly elegant or fancy, but it does the job. The SQL for
the stored proc is below if you're curious. I also reference a table
that I created in msdb to help me track growth over time. You can just
eliminate that part.

Hope it helps,
Teresa Masino

CREATE procedure sp_checkdbspace
AS
SET nocount ON

CREATE TABLE #DriveList (
namevarchar(20)null,
lowfreeintnull,
highfree intnull,
mediatype intnull
)

CREATE TABLE #DirList (
Drive varchar(20) null,
DirResults varchar(255) null
)

INSERT INTO #DriveList EXEC master.dbo.xp_availablemedia

DECLARE @.Drive varchar(20),
@.CMD varchar(255)

DECLARE mycursor CURSOR
FOR
SELECTname
FROM#DriveList
ORDER BY name

OPEN mycursor

FETCH mycursor INTO @.Drive

IF CURSOR_STATUS('variable', '@.mycursor') = 0
BEGIN
PRINT 'No such device'
CLOSE mycursor
DEALLOCATE mycursor
return
END

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD = 'insert into #DirList (DirResults) EXEC
master..xp_cmdshell ''DIR /-C ' + @.Drive + ''''
EXEC (@.CMD)
UPDATE #DirList SET Drive = @.Drive WHERE Drive IS NULL
FETCH mycursor INTO @.Drive
END

CLOSE mycursor
DEALLOCATE mycursor

SELECTDBName, LogicalName, PhysicalName, MinSize = min(SizeMB),
MaxSize = max(SizeMB), MinDate = min(StatusDate), MaxDate =
Max(StatusDate), MaxSizeMB = max(MaxSizeMB)
INTO#SpaceList
FROMmsdb..DBSpaceHistory
GROUP BY DBName, LogicalName, PhysicalName
ORDER BY DBName, LogicalName, PhysicalName

SELECT*, BytesFree = convert(numeric(18,0),
rtrim(ltrim(substring(replace(DirResults, ' bytes free', ''), 26,
50))))
INTO#SpaceOnDisk
FROM#DirList
WHEREDirResults LIKE '%bytes free%'

SELECTDBName = convert(varchar(20), DBName),
PhysicalName = convert(varchar(60), PhysicalName),
MaxSize,
Growth = MaxSize - MinSize,
DiskMBFree = convert(numeric(10,3), BytesFree / 1048576),
GrowthPeriod = datediff(day, MinDate, MaxDate),
DaysLeft = convert(numeric(10,3), (BytesFree / 1048576) / CASE WHEN
(MaxSize - MinSize) <= 0 THEN 1 ELSE ((MaxSize - MinSize) /
datediff(day, MinDate, MaxDate)) END)
FROM#SpaceList, #SpaceOnDisk
WHEREUPPER(substring(PhysicalName, 1, 3)) = Drive

GO