Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.
The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling not working

Hello.
Does RS have to be installed on the same machine as SQL Server in order to
get scheduling to work?
I created a schedule to run every minute (for testing). The time has come
and gone, and the Next Run time on the Shared Schedules screen isn't
changing, and the Last Run column shows Never. So scheduling isn't working.
Any ideas?
Thanks in advance,
MikeNo, I don't think so. However, scheduling is dependent on SQL Agent running
so make sure it is running on the database server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mike" <milop@.slomins.com> wrote in message
news:OCyTWKvuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't
> working.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Mike.
1) RS CAN be on different machine.
2)Make sure that the SQL Agent is running.
Regards, ProJester,
MSN Programmer.
"mike" wrote:
> Hello.
> Does RS have to be installed on the same machine as SQL Server in order to
> get scheduling to work?
> I created a schedule to run every minute (for testing). The time has come
> and gone, and the Next Run time on the Shared Schedules screen isn't
> changing, and the Last Run column shows Never. So scheduling isn't working.
> Any ideas?
> Thanks in advance,
> Mike
>
>

Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:
>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling enable tick box not working

Hi all,
I have a simple question, as follows.
I have set up a couple of jobs which I have scheduled to
run around the same time each night.
One of the jobs works fine, but the other does not run, I
have lloked into the problem and found that the tick box
which enables scheduling, unchecks it's self each night.
Is this possible and how can I stop it.
Both jobs were set-up in exactly the same manner.
Cheers.
Hi,
Is that job scheduled to run "One Time".
Check that by looking in to the "schedule" property of that job. If it is
the case make the job "Recurring"
Thanks
Hari
MCDBA
"Gazzer J" <anonymous@.discussions.microsoft.com> wrote in message
news:f99901c43e39$fc06ab50$a001280a@.phx.gbl...
> Hi all,
> I have a simple question, as follows.
> I have set up a couple of jobs which I have scheduled to
> run around the same time each night.
> One of the jobs works fine, but the other does not run, I
> have lloked into the problem and found that the tick box
> which enables scheduling, unchecks it's self each night.
> Is this possible and how can I stop it.
> Both jobs were set-up in exactly the same manner.
> Cheers.
|||There is a bug that can cause something like this. Do you
have any warnings in the SQL Agent log along the lines of
Possible date calculation spin for Schedule nn
If you have these warnings make sure you are on the latest
service pack. You may also want to try adding a minute or
two to the scheduled time as this can often correct the
problems with the data calculation "confusion" that SQL
Agent sometimes experiences.
-Sue
On Thu, 20 May 2004 00:13:43 -0700, "Gazzer J"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have a simple question, as follows.
>I have set up a couple of jobs which I have scheduled to
>run around the same time each night.
>One of the jobs works fine, but the other does not run, I
>have lloked into the problem and found that the tick box
>which enables scheduling, unchecks it's self each night.
>Is this possible and how can I stop it.
>Both jobs were set-up in exactly the same manner.
>Cheers.

Scheduling a java process

This question is more of a java question than a SQL Server question. But
still...
I am working in java; application server is Jboss and database is MS SQL
Server 2000.
I have a process written as an EJB in java. I want to schedule this proces
to run every hour every day. I was thinking about using the SQL Server to
schedule it. Is it possible? If yes then how? Any input is really
appreciated.
ThanksHi
Create a SQL Server agent job, with the job calling the EJB from the command
line. Not the best and cleanest solution, as database servers should not
have a lot of "application" instillations on it. IT should be a pure DB
server with all other processes running on a separate machine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rizwan" <hussains@.pendylum.com> wrote in message
news:PMG4e.9963$Fy3.815744@.news20.bellglobal.com...
> This question is more of a java question than a SQL Server question. But
> still...
> I am working in java; application server is Jboss and database is MS SQL
> Server 2000.
> I have a process written as an EJB in java. I want to schedule this proces
> to run every hour every day. I was thinking about using the SQL Server to
> schedule it. Is it possible? If yes then how? Any input is really
> appreciated.
> Thanks
>
>|||How the job can call an EJB from the command line? Can you give an example ?
Thanks
Rizwan
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uosA4rlOFHA.2144@.TK2MSFTNGP09.phx.gbl...
> Hi
> Create a SQL Server agent job, with the job calling the EJB from the
command
> line. Not the best and cleanest solution, as database servers should not
> have a lot of "application" instillations on it. IT should be a pure DB
> server with all other processes running on a separate machine.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:PMG4e.9963$Fy3.815744@.news20.bellglobal.com...
proces
to
>

scheduled server side trace - not working!

Hi,
I am looking to schedule a server side trace for a production server.
I need to capture all events on the server from 2AM until 9AM. The
server is automatically rebooted at 12-1AM so I have scripted a trace
and wrapped it in a stored proc. A SQL Job is scheduled for 2AM to run
the trace. When I check the trace file in the morning it doesn't show
the correct data, it shows only an hours worth of data for the
afternoon. The SQL Job runs with non errors.
Here's the file:
---
CREATE PROCEDURE dbo.pr_CCDTRACE
AS
/ ****************************************
************/
/* Created by: SQL Profiler */
/* Date: 05/10/2005 15:48:30 */
/ ****************************************
************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.DateTime datetime
set @.DateTime = '2005-10-06 10:00:00.000'
set @.maxfilesize = 50
exec @.rc = sp_trace_create @.TraceID output, 0, N'C:\CDDTrace_06102005',
@.maxfilesize, @.Datetime
if (@.rc != 0) goto error
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 10, 1, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 50, 1, @.on
exec sp_trace_setevent @.TraceID, 50, 6, @.on
exec sp_trace_setevent @.TraceID, 50, 9, @.on
exec sp_trace_setevent @.TraceID, 50, 10, @.on
exec sp_trace_setevent @.TraceID, 50, 11, @.on
exec sp_trace_setevent @.TraceID, 50, 12, @.on
exec sp_trace_setevent @.TraceID, 50, 13, @.on
exec sp_trace_setevent @.TraceID, 50, 14, @.on
exec sp_trace_setevent @.TraceID, 50, 15, @.on
exec sp_trace_setevent @.TraceID, 50, 16, @.on
exec sp_trace_setevent @.TraceID, 50, 17, @.on
exec sp_trace_setevent @.TraceID, 50, 18, @.on
exec sp_trace_setevent @.TraceID, 53, 1, @.on
exec sp_trace_setevent @.TraceID, 53, 6, @.on
exec sp_trace_setevent @.TraceID, 53, 9, @.on
exec sp_trace_setevent @.TraceID, 53, 10, @.on
exec sp_trace_setevent @.TraceID, 53, 11, @.on
exec sp_trace_setevent @.TraceID, 53, 12, @.on
exec sp_trace_setevent @.TraceID, 53, 13, @.on
exec sp_trace_setevent @.TraceID, 53, 14, @.on
exec sp_trace_setevent @.TraceID, 53, 15, @.on
exec sp_trace_setevent @.TraceID, 53, 16, @.on
exec sp_trace_setevent @.TraceID, 53, 17, @.on
exec sp_trace_setevent @.TraceID, 53, 18, @.on
exec sp_trace_setevent @.TraceID, 78, 1, @.on
exec sp_trace_setevent @.TraceID, 78, 6, @.on
exec sp_trace_setevent @.TraceID, 78, 9, @.on
exec sp_trace_setevent @.TraceID, 78, 10, @.on
exec sp_trace_setevent @.TraceID, 78, 11, @.on
exec sp_trace_setevent @.TraceID, 78, 12, @.on
exec sp_trace_setevent @.TraceID, 78, 13, @.on
exec sp_trace_setevent @.TraceID, 78, 14, @.on
exec sp_trace_setevent @.TraceID, 78, 15, @.on
exec sp_trace_setevent @.TraceID, 78, 16, @.on
exec sp_trace_setevent @.TraceID, 78, 17, @.on
exec sp_trace_setevent @.TraceID, 78, 18, @.on
exec sp_trace_setevent @.TraceID, 81, 1, @.on
exec sp_trace_setevent @.TraceID, 81, 6, @.on
exec sp_trace_setevent @.TraceID, 81, 9, @.on
exec sp_trace_setevent @.TraceID, 81, 10, @.on
exec sp_trace_setevent @.TraceID, 81, 11, @.on
exec sp_trace_setevent @.TraceID, 81, 12, @.on
exec sp_trace_setevent @.TraceID, 81, 13, @.on
exec sp_trace_setevent @.TraceID, 81, 14, @.on
exec sp_trace_setevent @.TraceID, 81, 15, @.on
exec sp_trace_setevent @.TraceID, 81, 16, @.on
exec sp_trace_setevent @.TraceID, 81, 17, @.on
exec sp_trace_setevent @.TraceID, 81, 18, @.on
exec sp_trace_setevent @.TraceID, 92, 1, @.on
exec sp_trace_setevent @.TraceID, 92, 6, @.on
exec sp_trace_setevent @.TraceID, 92, 9, @.on
exec sp_trace_setevent @.TraceID, 92, 10, @.on
exec sp_trace_setevent @.TraceID, 92, 11, @.on
exec sp_trace_setevent @.TraceID, 92, 12, @.on
exec sp_trace_setevent @.TraceID, 92, 13, @.on
exec sp_trace_setevent @.TraceID, 92, 14, @.on
exec sp_trace_setevent @.TraceID, 92, 15, @.on
exec sp_trace_setevent @.TraceID, 92, 16, @.on
exec sp_trace_setevent @.TraceID, 92, 17, @.on
exec sp_trace_setevent @.TraceID, 92, 18, @.on
exec sp_trace_setevent @.TraceID, 94, 1, @.on
exec sp_trace_setevent @.TraceID, 94, 6, @.on
exec sp_trace_setevent @.TraceID, 94, 9, @.on
exec sp_trace_setevent @.TraceID, 94, 10, @.on
exec sp_trace_setevent @.TraceID, 94, 11, @.on
exec sp_trace_setevent @.TraceID, 94, 12, @.on
exec sp_trace_setevent @.TraceID, 94, 13, @.on
exec sp_trace_setevent @.TraceID, 94, 14, @.on
exec sp_trace_setevent @.TraceID, 94, 15, @.on
exec sp_trace_setevent @.TraceID, 94, 16, @.on
exec sp_trace_setevent @.TraceID, 94, 17, @.on
exec sp_trace_setevent @.TraceID, 94, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
set @.intfilter = 7
exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
GO
---
What am I doing wrong?
TIA
fcYou say that you only have an hours worth of data for the _afternoon_, when
the trace is scheduled to run between 2AM and 10 AM. How does that work? Do
you mean you only have trace data for an hour from 2 AM?
I think that the size for the tracefile you have specified might be to
small. Either specify a larger size than 50 MB, or specify 2 for the options
(2nd parameter) to have the tracefile rollover into a new file when the file
size is exceeded.
Jacco Schalkwijk
SQL Server MVP
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1128598736.359959.299190@.o13g2000cwo.googlegroups.com...
> Hi,
> I am looking to schedule a server side trace for a production server.
> I need to capture all events on the server from 2AM until 9AM. The
> server is automatically rebooted at 12-1AM so I have scripted a trace
> and wrapped it in a stored proc. A SQL Job is scheduled for 2AM to run
> the trace. When I check the trace file in the morning it doesn't show
> the correct data, it shows only an hours worth of data for the
> afternoon. The SQL Job runs with non errors.
> Here's the file:
> ---
> CREATE PROCEDURE dbo.pr_CCDTRACE
> AS
> / ****************************************
************/
> /* Created by: SQL Profiler */
> /* Date: 05/10/2005 15:48:30 */
> / ****************************************
************/
>
> -- Create a Queue
> declare @.rc int
> declare @.TraceID int
> declare @.maxfilesize bigint
> declare @.DateTime datetime
> set @.DateTime = '2005-10-06 10:00:00.000'
> set @.maxfilesize = 50
> exec @.rc = sp_trace_create @.TraceID output, 0, N'C:\CDDTrace_06102005',
> @.maxfilesize, @.Datetime
> if (@.rc != 0) goto error
> declare @.on bit
> set @.on = 1
> exec sp_trace_setevent @.TraceID, 10, 1, @.on
> exec sp_trace_setevent @.TraceID, 10, 6, @.on
> exec sp_trace_setevent @.TraceID, 10, 9, @.on
> exec sp_trace_setevent @.TraceID, 10, 10, @.on
> exec sp_trace_setevent @.TraceID, 10, 11, @.on
> exec sp_trace_setevent @.TraceID, 10, 12, @.on
> exec sp_trace_setevent @.TraceID, 10, 13, @.on
> exec sp_trace_setevent @.TraceID, 10, 14, @.on
> exec sp_trace_setevent @.TraceID, 10, 15, @.on
> exec sp_trace_setevent @.TraceID, 10, 16, @.on
> exec sp_trace_setevent @.TraceID, 10, 17, @.on
> exec sp_trace_setevent @.TraceID, 10, 18, @.on
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 6, @.on
> exec sp_trace_setevent @.TraceID, 12, 9, @.on
> exec sp_trace_setevent @.TraceID, 12, 10, @.on
> exec sp_trace_setevent @.TraceID, 12, 11, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> exec sp_trace_setevent @.TraceID, 12, 13, @.on
> exec sp_trace_setevent @.TraceID, 12, 14, @.on
> exec sp_trace_setevent @.TraceID, 12, 15, @.on
> exec sp_trace_setevent @.TraceID, 12, 16, @.on
> exec sp_trace_setevent @.TraceID, 12, 17, @.on
> exec sp_trace_setevent @.TraceID, 12, 18, @.on
> exec sp_trace_setevent @.TraceID, 50, 1, @.on
> exec sp_trace_setevent @.TraceID, 50, 6, @.on
> exec sp_trace_setevent @.TraceID, 50, 9, @.on
> exec sp_trace_setevent @.TraceID, 50, 10, @.on
> exec sp_trace_setevent @.TraceID, 50, 11, @.on
> exec sp_trace_setevent @.TraceID, 50, 12, @.on
> exec sp_trace_setevent @.TraceID, 50, 13, @.on
> exec sp_trace_setevent @.TraceID, 50, 14, @.on
> exec sp_trace_setevent @.TraceID, 50, 15, @.on
> exec sp_trace_setevent @.TraceID, 50, 16, @.on
> exec sp_trace_setevent @.TraceID, 50, 17, @.on
> exec sp_trace_setevent @.TraceID, 50, 18, @.on
> exec sp_trace_setevent @.TraceID, 53, 1, @.on
> exec sp_trace_setevent @.TraceID, 53, 6, @.on
> exec sp_trace_setevent @.TraceID, 53, 9, @.on
> exec sp_trace_setevent @.TraceID, 53, 10, @.on
> exec sp_trace_setevent @.TraceID, 53, 11, @.on
> exec sp_trace_setevent @.TraceID, 53, 12, @.on
> exec sp_trace_setevent @.TraceID, 53, 13, @.on
> exec sp_trace_setevent @.TraceID, 53, 14, @.on
> exec sp_trace_setevent @.TraceID, 53, 15, @.on
> exec sp_trace_setevent @.TraceID, 53, 16, @.on
> exec sp_trace_setevent @.TraceID, 53, 17, @.on
> exec sp_trace_setevent @.TraceID, 53, 18, @.on
> exec sp_trace_setevent @.TraceID, 78, 1, @.on
> exec sp_trace_setevent @.TraceID, 78, 6, @.on
> exec sp_trace_setevent @.TraceID, 78, 9, @.on
> exec sp_trace_setevent @.TraceID, 78, 10, @.on
> exec sp_trace_setevent @.TraceID, 78, 11, @.on
> exec sp_trace_setevent @.TraceID, 78, 12, @.on
> exec sp_trace_setevent @.TraceID, 78, 13, @.on
> exec sp_trace_setevent @.TraceID, 78, 14, @.on
> exec sp_trace_setevent @.TraceID, 78, 15, @.on
> exec sp_trace_setevent @.TraceID, 78, 16, @.on
> exec sp_trace_setevent @.TraceID, 78, 17, @.on
> exec sp_trace_setevent @.TraceID, 78, 18, @.on
> exec sp_trace_setevent @.TraceID, 81, 1, @.on
> exec sp_trace_setevent @.TraceID, 81, 6, @.on
> exec sp_trace_setevent @.TraceID, 81, 9, @.on
> exec sp_trace_setevent @.TraceID, 81, 10, @.on
> exec sp_trace_setevent @.TraceID, 81, 11, @.on
> exec sp_trace_setevent @.TraceID, 81, 12, @.on
> exec sp_trace_setevent @.TraceID, 81, 13, @.on
> exec sp_trace_setevent @.TraceID, 81, 14, @.on
> exec sp_trace_setevent @.TraceID, 81, 15, @.on
> exec sp_trace_setevent @.TraceID, 81, 16, @.on
> exec sp_trace_setevent @.TraceID, 81, 17, @.on
> exec sp_trace_setevent @.TraceID, 81, 18, @.on
> exec sp_trace_setevent @.TraceID, 92, 1, @.on
> exec sp_trace_setevent @.TraceID, 92, 6, @.on
> exec sp_trace_setevent @.TraceID, 92, 9, @.on
> exec sp_trace_setevent @.TraceID, 92, 10, @.on
> exec sp_trace_setevent @.TraceID, 92, 11, @.on
> exec sp_trace_setevent @.TraceID, 92, 12, @.on
> exec sp_trace_setevent @.TraceID, 92, 13, @.on
> exec sp_trace_setevent @.TraceID, 92, 14, @.on
> exec sp_trace_setevent @.TraceID, 92, 15, @.on
> exec sp_trace_setevent @.TraceID, 92, 16, @.on
> exec sp_trace_setevent @.TraceID, 92, 17, @.on
> exec sp_trace_setevent @.TraceID, 92, 18, @.on
> exec sp_trace_setevent @.TraceID, 94, 1, @.on
> exec sp_trace_setevent @.TraceID, 94, 6, @.on
> exec sp_trace_setevent @.TraceID, 94, 9, @.on
> exec sp_trace_setevent @.TraceID, 94, 10, @.on
> exec sp_trace_setevent @.TraceID, 94, 11, @.on
> exec sp_trace_setevent @.TraceID, 94, 12, @.on
> exec sp_trace_setevent @.TraceID, 94, 13, @.on
> exec sp_trace_setevent @.TraceID, 94, 14, @.on
> exec sp_trace_setevent @.TraceID, 94, 15, @.on
> exec sp_trace_setevent @.TraceID, 94, 16, @.on
> exec sp_trace_setevent @.TraceID, 94, 17, @.on
> exec sp_trace_setevent @.TraceID, 94, 18, @.on
>
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> set @.intfilter = 7
> exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
>
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> -- display trace id for future references
> select TraceID=@.TraceID
> goto finish
> error:
> select ErrorCode=@.rc
> finish:
> GO
> ---
> What am I doing wrong?
> TIA
> fc
>sql

Scheduled reports not working - service account

Hi
I'm just getting my feet wet with Scheduled reports in SSRS2005, and have
hit a snag...
The schedule is not working, and when I check the job history on SQL Server
I find the message:
'EXECUTE AS LOGIN' failed for the requested login 'NT AUTHORITY\NETWORK
SERVICE'. The step failed.
I find if I go into Management Studio, I can change the NETWORK SERVICE to a
domain user account, and everything works - but then I can't change the
subscription properties from Report Manager.
SQL Server & SSRS are both running on Windows Server 2003 under a domain
account, and the data/SSRS connection points to an SQL Server 2000 Database
on a different server in the domain.
Any ideas? I've gone through the security recommendations and still can't
find a solution.
Regards
GrantHi
Don't worry about responding to this one - I figured it out...
Fixed it by setting everything to use the NetworkService account, except for
the SQL Agent - I found one of my mistakes was changing the account from the
Service Manager, instead of the SQL Server Configuration Manager.
Anyway - all is well and reports are running as scheduled.
Thanks
Grant
"Grant Morgan" <grantmorgan@.noemail.noemail> wrote in message
news:ujq4Jos3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi
> I'm just getting my feet wet with Scheduled reports in SSRS2005, and have
> hit a snag...
> The schedule is not working, and when I check the job history on SQL
> Server I find the message:
> 'EXECUTE AS LOGIN' failed for the requested login 'NT AUTHORITY\NETWORK
> SERVICE'. The step failed.
> I find if I go into Management Studio, I can change the NETWORK SERVICE to
> a domain user account, and everything works - but then I can't change the
> subscription properties from Report Manager.
> SQL Server & SSRS are both running on Windows Server 2003 under a domain
> account, and the data/SSRS connection points to an SQL Server 2000
> Database on a different server in the domain.
> Any ideas? I've gone through the security recommendations and still can't
> find a solution.
> Regards
> Grant
>
>

Scheduled Job Not Working

I have created a scheduled job in the enterprise manager, which checks around 20 different date columns and replaces any non-dates with NULL using the following syntax: -

update policy set [exp] =null where isdate (substring([exp],4,2)+'-'+left([exp],2)+'-'+'200'+right ([exp],1 ))=0
go
update policy set [eff] =null where isdate (substring([eff],4,2)+'-'+left([eff],2)+'-'+'200'+right ([eff],1 ))=0
go
update policy set [written] =null where isdate (substring([written],4,2)+'-'+left([written],2)+'-'+'200'+right ([written],1 ))=0
go

etc......

When I start the job, if fails within 2 seconds. If I copy the syntax in to the query analyzer it works fine.

All help appreciated.

A couple of questions

1 what is the error

2 who is the owner of the Job (and does he have permissions to the DB)

3 is the correct DB seleted in the step?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

to view the errro message , right click on the job, select view job history, click show step details (top right)

Click on the stepid and the error will be displayed in the bottom part

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi there,

The error message is: The job failed. The Job was invoked by User KEELAN_WESTALL\James. The last step to run was step 1 (UpdateDates).

The user is James (me)

I have security to the server/database/tables etc.

The correct DB is selected.

|||

The error message is: -

Executed as user: sa. The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256). The step failed.

|||

Convert to varchar first, take a look at this

select substring(1952005,1,2) -- will fail

select substring(convert(varchar(8),1952005),1,2) -- correct

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

As a test I ran the job just changing one column using the following syntax: -

update policy set [exp] =null where isdate (convert (varchar(8), (substring([eff],4,2)+'/'+left([eff],2)+'/'+'200'+right ([eff],1))))=0
go

It worked perfectly.

Thanks very much Denis.

Scheduled job for a backup device stopped working

Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
Sounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped? I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>
|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>
|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
..
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger

Scheduled job for a backup device stopped working

Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etcâ?¦each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION = N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of â'occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheersâ?¦.Roger SagerSounds like SQL Agent is stopped. Have you checked that?
--
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> => N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:
> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> > Hi all...
> >
> > The are a number of real databases on our SQL Server 2000 and initially I
> > set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> > backups. My concern is with the FULL backups and the DIFFs are working
> > just
> > fine. I scheduled a number of FULL backup jobs to execute each Sunday
> > only
> > and at difference times, such as, the 1st database at 4 am, the next at
> > 4:15
> > am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> > and
> > like clock work each of these real databases were over written as defined.
> > The job history shows that a FULL backup for these jobs was performed Nov
> > 13th (when initially defined); the next showed the jobs run at Nov 20th,
> > then
> > Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> > correct date stamp, but all jobs stopped' I expected, after returning
> > from
> > holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> > 1st.
> > But no, the last run was the 11th of Dec?
> >
> > The definitions of one particular job (under the Management> SQL Server
> > Agent > Jobs definition) is as follows:
> >
> > Name : Auscott Sunday overwrite BU
> > Under its property definition:
> > General Tab> enable is checked, Category is Database Maintenance, Owner
> > is sa
> > Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> > Script (TSQL), and Database: Auscott, and Command:
> > BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD
> > ,
> > NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> > => > N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> > INT
> > select @.i = position from msdb..backupset where database_name='Auscott'and
> > type!='F' and backup_set_id=(select max(backup_set_id) from
> > msdb..backupset
> > where database_name='Auscott')
> > RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> >
> > Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> > Recurring radio button with a description of "occurs every 1 week(s) on
> > Sunday, at 4:15:00 am
> >
> > To my way of thinking, every thing seemed to be defined correctly using
> > backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> > And there was no disck space issues...so I am stumped.
> >
> > Does anyone have any suggestions?
> > Cheers..Roger Sager
> >
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger

Scheduled job for a backup device stopped working

Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them a
nd
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, the
n
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped' I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is
sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOU
NLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger SagerSounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> Hi all...
> The are a number of real databases on our SQL Server 2000 and initially I
> set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
> backups. My concern is with the FULL backups and the DIFFs are working
> just
> fine. I scheduled a number of FULL backup jobs to execute each Sunday
> only
> and at difference times, such as, the 1st database at 4 am, the next at
> 4:15
> am, the next at 4:30 am etc.each Sunday only. Well I was monitoring them
> and
> like clock work each of these real databases were over written as defined.
> The job history shows that a FULL backup for these jobs was performed Nov
> 13th (when initially defined); the next showed the jobs run at Nov 20th,
> then
> Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
> correct date stamp, but all jobs stopped' I expected, after returning
> from
> holidays to see in the job history a backup for Dec 18, Dec 25, and Jan
> 1st.
> But no, the last run was the 11th of Dec?
> The definitions of one particular job (under the Management> SQL Server
> Agent > Jobs definition) is as follows:
> Name : Auscott Sunday overwrite BU
> Under its property definition:
> General Tab> enable is checked, Category is Database Maintenance, Owner
> is sa
> Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
> Script (TSQL), and Database: Auscott, and Command:
> BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, N
OUNLOAD
> ,
> NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION
> =
> N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i
> INT
> select @.i = position from msdb..backupset where database_name='Auscott'and
> type!='F' and backup_set_id=(select max(backup_set_id) from
> msdb..backupset
> where database_name='Auscott')
> RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
> Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
> Recurring radio button with a description of "occurs every 1 week(s) on
> Sunday, at 4:15:00 am
> To my way of thinking, every thing seemed to be defined correctly using
> backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
> And there was no disck space issues...so I am stumped.
> Does anyone have any suggestions?
> Cheers..Roger Sager
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger

Scheduled job fails because locks were not released on the table

Hi,
I have a scheduled job that has started failing frequently because of a user
who only works on the weekends. Even though the user stops working on the
application, somehow the locks stay on the table making the scheduled job to
fail. Is there a way to release the locks on a particular table through TSQL
before the job runs so that the job gets completed successfully?Not some general way. You could use procedures such as sp_who, sp_lock, sp_who2 etc to find the SPID
you need to get rid of and then use the KILL command to force a rollback and termination of the
connection in question. Another option is to set the database to single user and specify a ROLLBACK
option (see ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> Hi,
> I have a scheduled job that has started failing frequently because of a user
> who only works on the weekends. Even though the user stops working on the
> application, somehow the locks stay on the table making the scheduled job to
> fail. Is there a way to release the locks on a particular table through TSQL
> before the job runs so that the job gets completed successfully?|||Well, what exactly is the user doing to lock the table? What do they do
when they "stop working" on it?
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> Hi,
> I have a scheduled job that has started failing frequently because of a
> user
> who only works on the weekends. Even though the user stops working on the
> application, somehow the locks stay on the table making the scheduled job
> to
> fail. Is there a way to release the locks on a particular table through
> TSQL
> before the job runs so that the job gets completed successfully?|||The user works remotely and connects to the application through a VPN. I am
trying to find out from the user how he logs off after he stops working.
Maybe he doesn't even logs off and leave the application open on the machine
and just closes the VPN connection. I will post the answer as soon as I hear
from the user.
As this job runs during the night, I can get the spid from the username and
then put in a TSQL command to kill the spid/s created by this user so that
the locks get released from the table.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, what exactly is the user doing to lock the table? What do they do
> when they "stop working" on it?
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> > Hi,
> >
> > I have a scheduled job that has started failing frequently because of a
> > user
> > who only works on the weekends. Even though the user stops working on the
> > application, somehow the locks stay on the table making the scheduled job
> > to
> > fail. Is there a way to release the locks on a particular table through
> > TSQL
> > before the job runs so that the job gets completed successfully?
>|||I found out from the user that while running an update on the table, his
machine froze and he wasn't able to do anything afterwards. Since the machine
is at a remote location and he is connecting through VPN he wasn't even able
to reboot the machine. This might have caused the locks to remain on this
table.
What should be done to handle situations like this to prevent future
scheduled job failures?
"ronnie" wrote:
> The user works remotely and connects to the application through a VPN. I am
> trying to find out from the user how he logs off after he stops working.
> Maybe he doesn't even logs off and leave the application open on the machine
> and just closes the VPN connection. I will post the answer as soon as I hear
> from the user.
> As this job runs during the night, I can get the spid from the username and
> then put in a TSQL command to kill the spid/s created by this user so that
> the locks get released from the table.
> "Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, what exactly is the user doing to lock the table? What do they do
> > when they "stop working" on it?
> >
> >
> >
> > "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> > news:DDE09196-44F6-4681-BD95-40472FB8112A@.microsoft.com...
> > > Hi,
> > >
> > > I have a scheduled job that has started failing frequently because of a
> > > user
> > > who only works on the weekends. Even though the user stops working on the
> > > application, somehow the locks stay on the table making the scheduled job
> > > to
> > > fail. Is there a way to release the locks on a particular table through
> > > TSQL
> > > before the job runs so that the job gets completed successfully?
> >|||> What should be done to handle situations like this to prevent future
> scheduled job failures?
Well, what I was trying to get at what was, what exactly is the user doing
to hold locks on the table in the first place? Using what app(s)? Is he
opening data in a grid? Ideally he should be submitting short transactions
(using stored procedures or insert/update statements) and should not be as
exposed to the risk of connection interruptions. If this has happened more
than once then my guess is his data manipulation techniques are not ideal.|||The user was working on an Microsoft Access application that uses SQL Server
database as the backend. This user did an update / insert into a table using
an Access Form and while he was doing that using VPN and remote desktop his
machine froze and the connection broke down. This somehow created the
situation where the entire table got locked and the connection to SQL Server
persisted even though the user's machine has frozen.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > What should be done to handle situations like this to prevent future
> > scheduled job failures?
> Well, what I was trying to get at what was, what exactly is the user doing
> to hold locks on the table in the first place? Using what app(s)? Is he
> opening data in a grid? Ideally he should be submitting short transactions
> (using stored procedures or insert/update statements) and should not be as
> exposed to the risk of connection interruptions. If this has happened more
> than once then my guess is his data manipulation techniques are not ideal.
>

Scheduled DTS job to run a cmd file not working

Hi,

I have created a DTS job that contains one 'Execute SQL Task' job. This SQL task runs a cmd file. The cmd file runs a few windows commands and then runs a Micorosoft Access Macro. Once finished both access and the cmd screen close down.

If I open up the DTS job and execute it manually, it works fine (takes about 1/2 an hour to run). My problem is that when I schedule the DTS job, the job starts up at the correct time but it never actually starts running the cmd file and it gives no error. It says Executing until I actually stop the job manually.

The Job details are:

Type: Operating System Command [CmdExec]

Command: DTSRun /~Z0x2F3FF84472BB6E7FF356EB006BA1AEC62C95AB3BF506F3 4A241F228CE148AB09DBC66B8651A450B725E6C4E6A1D328E4 EC2F2C0F8E323F1C7D501FD5B8FD00E25656514AF2224407DB 1C569163CBE383A8E7D8BE4974A0911F5CEB

The DTS details are:

C:\Batches\DTSrunofUpdatesqlpmi.cmd

The cmd program details are:

echo on
:Start

if not exist "c:\apps\CI_Databases\pmi\pmiload.mdb" exit

rem cleanup unfinsihed runs
if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"

rem main file locked assume its being work on so don't run this job.
if exist "c:\apps\CI_Databases\pmi\pmiload.ldb" exit

rem if a tmp exits then the last job never completed - continue to add to tmp file

if exist "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" goto Load

copy "c:\apps\CI_Databases\pmi\pmiload.mdb" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"

:Load
"C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/x updatedata
rem Compact databases
"C:\Program Files\microsoft office 2003\OFFICE11\msaccess.exe" "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb"/compact

ren "c:\apps\CI_Databases\pmi\pmiload.mdb" "pmiloadold.mdb"
ren "c:\apps\CI_Databases\pmi\pmiloadtmp.mdb" "pmiload.mdb"
if exist "c:\apps\CI_Databases\pmi\pmiload.mdb" if exist "c:\apps\CI_Databases\pmi\pmiloadold.mdb" del "c:\apps\CI_Databases\pmi\pmiloadold.mdb"

All help is greatly appreciated as this has been bugging me for some time now.

Thanks
SamIf all your DTS package is doing is running a .cmd (batch) file, why not run it instead from a scheduled task. In the schedule task step, select task type of Operating system command and type in the path and name of the batch file.

This may not resolve the problem, however. I suspect that the problem lies within permissions. What account is the SQL Agent user set up to run under? Then think about what permissions your account has that the SQL Agent account might not have.

Regards,

hmscott|||Thanks hmscott.
That works when i change the SQL Server Agent startup account username and password.