Scheduling approach

We user SQL sever 2005, I have a reports runs very slow since big chunk
of data, I just want to run it once a day. This report has few
selections fields, for different people they want to see different
selection criteria, is there a way I can just schedule it once for wide
open selection and each person based on this generic report to narrow
down to their own small set of data? thanksI use email to notify the end user for the "new" genereated report, but
why each time the user click the link, instead of giving the snapshot,
the link do the RE-Generat report? how do i let user only see the
snapshot, it is a big report.|||On the report options, set the execution to execute from a snapshot and set
the snapshot creation for a quiet time to avoid impacting OLTP. Then set up a
subscription to run when content is refreshed (available only for snapshot
reports). You will need to set up default parameters, but if chosen to return
NULL data, you effectively get an email with a notice of report content
update with a dummy report attached.
"Wang Xiaoning" wrote:
> I use email to notify the end user for the "new" genereated report, but
> why each time the user click the link, instead of giving the snapshot,
> the link do the RE-Generat report? how do i let user only see the
> snapshot, it is a big report.
>sql

Scheduled transaction log backup Failed

Hi Greeting,

Sql sever7
OS winNT

We have scheduled transaction log backup for user database to run every one hour. Now the transaction has failed. it gives out put

[12] Database oas: Delete Old Backup Files...
Unable to delete file E:\mssql7\tlogs\oas\oas_tlog_200608111300.TRN. 0 file(s) deleted.

When we try to delete the file manully, we get alert message "can not delelte the file as it is in use."

Please suggest how to solve this.

Awating for reply

Thanks in Advance
AdilSome other process is holding on to the file and won't let go. NetBackup is notorious for that sort of thing, as are other applications. You can:

1. Reboot the server
2. Kill the process holding on to the file.

There is a nice little windows utility called ProcessExplorer (http://www.sysinternals.com/Utilities/ProcessExplorer.html)
that will let you find the process holding the handle (file) and then kill the process.sql

Scheduled report printing?

Hi,
We are running SQL Server 2000 and would like to have a series of reports
print out each morning without user intervention. What would be the best
way to accomplish this? If this is not the correct news group for this type
of question please direct me to the correct one.
Thanks in advance,
LinnThanks Igor, that's an interesting article. Too bad I don't have Visual
Studio, or Reporting Services for that matter. I also just noticed that the
article is for SQL Server 2005, we are using SQL Server 2000 still, wonder
if it would work for our version too.
I wonder if a third party software package like Crystal Reports would offer
this option?
Thanks,
Linn
"Igor Taranov -- MSFT" <IgorTaranovMSFT@.discussions.microsoft.com> wrote in
message news:888959B3-CAFB-4122-9F07-3070B15C13B4@.microsoft.com...
> See http://msdn2.microsoft.com/en-us/library/ms160778.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Linn Kubler" wrote:
>> Hi,
>> We are running SQL Server 2000 and would like to have a series of reports
>> print out each morning without user intervention. What would be the best
>> way to accomplish this? If this is not the correct news group for this
>> type
>> of question please direct me to the correct one.
>> Thanks in advance,
>> Linn
>>|||Has anyone actually got the Sample Printer Delivery Extension to work on a
Reporting Server?
"Igor Taranov -- MSFT" wrote:
> Linn,
> SQL Server Reporting Services 2005 can be installed on SQL 2000, and printer
> delivery should work fine.
> -Igor
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Linn Kubler" wrote:
> > Thanks Igor, that's an interesting article. Too bad I don't have Visual
> > Studio, or Reporting Services for that matter. I also just noticed that the
> > article is for SQL Server 2005, we are using SQL Server 2000 still, wonder
> > if it would work for our version too.
> >
> > I wonder if a third party software package like Crystal Reports would offer
> > this option?
> >
> > Thanks,
> > Linn
> >
> > "Igor Taranov -- MSFT" <IgorTaranovMSFT@.discussions.microsoft.com> wrote in
> > message news:888959B3-CAFB-4122-9F07-3070B15C13B4@.microsoft.com...
> > > See http://msdn2.microsoft.com/en-us/library/ms160778.aspx
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > >
> > > "Linn Kubler" wrote:
> > >
> > >> Hi,
> > >>
> > >> We are running SQL Server 2000 and would like to have a series of reports
> > >> print out each morning without user intervention. What would be the best
> > >> way to accomplish this? If this is not the correct news group for this
> > >> type
> > >> of question please direct me to the correct one.
> > >>
> > >> Thanks in advance,
> > >> Linn
> > >>
> > >>
> > >>
> >
> >
> >

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 Backup for SQL Database

Hi All,
Very new user to SQL admin. I need to backup a sql
database, when I run it manually it runs fine and creates
a backup without any issue. However when I try to
schedule the job it does not save the schedule or run the
backup.
I am using Windows 2000 Server and SQL2000. Both are
fully patched and have latest service packs.
Am I doing something wrong or is this a bug. The SQL
agent is running on the local machine, and the job
appears in the backup list but always fails. I am trying
to write to another data disk on the server with the
routine to overwrite the file everytime.
Any help would be great
Hi
Make sure that the agent is running on a domain account that has access to
the other server. Are you using '\\machinename\sharename\file.bak' (UNC path)
as your naming standard for the backup file?
You can not make a maintenance plan that uses a UNC share, so you have to
code it manually and setup a job (see BOL for BACKUP DATABASE).
Cheers
Mike
"Chris" wrote:

> Hi All,
> Very new user to SQL admin. I need to backup a sql
> database, when I run it manually it runs fine and creates
> a backup without any issue. However when I try to
> schedule the job it does not save the schedule or run the
> backup.
> I am using Windows 2000 Server and SQL2000. Both are
> fully patched and have latest service packs.
> Am I doing something wrong or is this a bug. The SQL
> agent is running on the local machine, and the job
> appears in the backup list but always fails. I am trying
> to write to another data disk on the server with the
> routine to overwrite the file everytime.
> Any help would be great
>
|||Hi,
The backup is just running to another drive on the same
machine. My nightly backup routine goes and copies that
directory to the central server for nightly backup
>--Original Message--
>Hi
>Make sure that the agent is running on a domain account
that has access to
>the other server. Are you
using '\\machinename\sharename\file.bak' (UNC path)
>as your naming standard for the backup file?
>You can not make a maintenance plan that uses a UNC
share, so you have to
>code it manually and setup a job (see BOL for BACKUP
DATABASE).[vbcol=seagreen]
>Cheers
>Mike
>"Chris" wrote:
creates[vbcol=seagreen]
the[vbcol=seagreen]
trying
>.
>
|||Is the SQL Server Agent service startup account the same account as the SQL
Server service?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> The backup is just running to another drive on the same
> machine. My nightly backup routine goes and copies that
> directory to the central server for nightly backup
> that has access to
> using '\\machinename\sharename\file.bak' (UNC path)
> share, so you have to
> DATABASE).
> creates
> the
> trying
|||Yes they are both started by local administrator and the
local administrator owns the backup job as well
>--Original Message--
>Is the SQL Server Agent service startup account the same
account as the SQL
>Server service?
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free![vbcol=seagreen]
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
account[vbcol=seagreen]
run
>
>.
>
|||Any error messages for the job? Look in the SQL Server log too, usually
found in the \Log subfolder of SQL Server.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes they are both started by local administrator and the
> local administrator owns the backup job as well
> account as the SQL
> Lite, free!
> account
> run
|||Ok have nearly sorted it out. I Hope!
I have managed to get the job to run by doing a database
maintainance job going. This starts automatically and
runs when scheduled. Only problem is when the SQL server
shuts down it stops the agent. When the server restarts
it doesn't restart the agent. I have set the propeties on
the SQL Agent to restart after 10 minutes and the service
does restart ok.
For some reason the SQL agent under server management
does not restart so the job fails. As soon as I right
click and say start the job runs ok. So my question is
Why doesn't this restart when the service does?
Chris
>--Original Message--
>Any error messages for the job? Look in the SQL Server
log too, usually
>found in the \Log subfolder of SQL Server.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free![vbcol=seagreen]
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
the[vbcol=seagreen]
same[vbcol=seagreen]
same[vbcol=seagreen]
that[vbcol=seagreen]
BACKUP[vbcol=seagreen]
sql[vbcol=seagreen]
are[vbcol=seagreen]
SQL[vbcol=seagreen]
job[vbcol=seagreen]
the
>
>.
>
|||>> I have set the propeties on the SQL Agent to restart after 10 minutes
and the service does restart ok.[vbcol=seagreen]
so the job fails.
I do not quite understand the above two (conflicting) statements. I guess
the SQL Agent service is restarting, but the job is not running. Is it
because the job is not scheduled to run at that time?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:0b2801c4a6f6$21d26a20$a301280a@.phx.gbl...[vbcol=seagreen]
> Ok have nearly sorted it out. I Hope!
> I have managed to get the job to run by doing a database
> maintainance job going. This starts automatically and
> runs when scheduled. Only problem is when the SQL server
> shuts down it stops the agent. When the server restarts
> it doesn't restart the agent. I have set the propeties on
> the SQL Agent to restart after 10 minutes and the service
> does restart ok.
> For some reason the SQL agent under server management
> does not restart so the job fails. As soon as I right
> click and say start the job runs ok. So my question is
> Why doesn't this restart when the service does?
> Chris
> log too, usually
> Lite, free!
> the
> same
> same
> that
> BACKUP
> sql
> are
> SQL
> job
> the

Scheduled Backup for SQL Database

Hi All,
Very new user to SQL admin. I need to backup a sql
database, when I run it manually it runs fine and creates
a backup without any issue. However when I try to
schedule the job it does not save the schedule or run the
backup.
I am using Windows 2000 Server and SQL2000. Both are
fully patched and have latest service packs.
Am I doing something wrong or is this a bug. The SQL
agent is running on the local machine, and the job
appears in the backup list but always fails. I am trying
to write to another data disk on the server with the
routine to overwrite the file everytime.
Any help would be greatHi
Make sure that the agent is running on a domain account that has access to
the other server. Are you using '\\machinename\sharename\file.bak' (UNC path)
as your naming standard for the backup file?
You can not make a maintenance plan that uses a UNC share, so you have to
code it manually and setup a job (see BOL for BACKUP DATABASE).
Cheers
Mike
"Chris" wrote:
> Hi All,
> Very new user to SQL admin. I need to backup a sql
> database, when I run it manually it runs fine and creates
> a backup without any issue. However when I try to
> schedule the job it does not save the schedule or run the
> backup.
> I am using Windows 2000 Server and SQL2000. Both are
> fully patched and have latest service packs.
> Am I doing something wrong or is this a bug. The SQL
> agent is running on the local machine, and the job
> appears in the backup list but always fails. I am trying
> to write to another data disk on the server with the
> routine to overwrite the file everytime.
> Any help would be great
>|||Hi,
The backup is just running to another drive on the same
machine. My nightly backup routine goes and copies that
directory to the central server for nightly backup
>--Original Message--
>Hi
>Make sure that the agent is running on a domain account
that has access to
>the other server. Are you
using '\\machinename\sharename\file.bak' (UNC path)
>as your naming standard for the backup file?
>You can not make a maintenance plan that uses a UNC
share, so you have to
>code it manually and setup a job (see BOL for BACKUP
DATABASE).
>Cheers
>Mike
>"Chris" wrote:
>> Hi All,
>> Very new user to SQL admin. I need to backup a sql
>> database, when I run it manually it runs fine and
creates
>> a backup without any issue. However when I try to
>> schedule the job it does not save the schedule or run
the
>> backup.
>> I am using Windows 2000 Server and SQL2000. Both are
>> fully patched and have latest service packs.
>> Am I doing something wrong or is this a bug. The SQL
>> agent is running on the local machine, and the job
>> appears in the backup list but always fails. I am
trying
>> to write to another data disk on the server with the
>> routine to overwrite the file everytime.
>> Any help would be great
>.
>|||Is the SQL Server Agent service startup account the same account as the SQL
Server service?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> Hi,
> The backup is just running to another drive on the same
> machine. My nightly backup routine goes and copies that
> directory to the central server for nightly backup
> >--Original Message--
> >Hi
> >
> >Make sure that the agent is running on a domain account
> that has access to
> >the other server. Are you
> using '\\machinename\sharename\file.bak' (UNC path)
> >as your naming standard for the backup file?
> >
> >You can not make a maintenance plan that uses a UNC
> share, so you have to
> >code it manually and setup a job (see BOL for BACKUP
> DATABASE).
> >
> >Cheers
> >Mike
> >
> >"Chris" wrote:
> >
> >> Hi All,
> >>
> >> Very new user to SQL admin. I need to backup a sql
> >> database, when I run it manually it runs fine and
> creates
> >> a backup without any issue. However when I try to
> >> schedule the job it does not save the schedule or run
> the
> >> backup.
> >>
> >> I am using Windows 2000 Server and SQL2000. Both are
> >> fully patched and have latest service packs.
> >>
> >> Am I doing something wrong or is this a bug. The SQL
> >> agent is running on the local machine, and the job
> >> appears in the backup list but always fails. I am
> trying
> >> to write to another data disk on the server with the
> >> routine to overwrite the file everytime.
> >>
> >> Any help would be great
> >>
> >.
> >|||Yes they are both started by local administrator and the
local administrator owns the backup job as well
>--Original Message--
>Is the SQL Server Agent service startup account the same
account as the SQL
>Server service?
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
>> Hi,
>> The backup is just running to another drive on the same
>> machine. My nightly backup routine goes and copies that
>> directory to the central server for nightly backup
>> >--Original Message--
>> >Hi
>> >
>> >Make sure that the agent is running on a domain
account
>> that has access to
>> >the other server. Are you
>> using '\\machinename\sharename\file.bak' (UNC path)
>> >as your naming standard for the backup file?
>> >
>> >You can not make a maintenance plan that uses a UNC
>> share, so you have to
>> >code it manually and setup a job (see BOL for BACKUP
>> DATABASE).
>> >
>> >Cheers
>> >Mike
>> >
>> >"Chris" wrote:
>> >
>> >> Hi All,
>> >>
>> >> Very new user to SQL admin. I need to backup a sql
>> >> database, when I run it manually it runs fine and
>> creates
>> >> a backup without any issue. However when I try to
>> >> schedule the job it does not save the schedule or
run
>> the
>> >> backup.
>> >>
>> >> I am using Windows 2000 Server and SQL2000. Both are
>> >> fully patched and have latest service packs.
>> >>
>> >> Am I doing something wrong or is this a bug. The SQL
>> >> agent is running on the local machine, and the job
>> >> appears in the backup list but always fails. I am
>> trying
>> >> to write to another data disk on the server with the
>> >> routine to overwrite the file everytime.
>> >>
>> >> Any help would be great
>> >>
>> >.
>> >
>
>.
>|||Any error messages for the job? Look in the SQL Server log too, usually
found in the \Log subfolder of SQL Server.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
> Yes they are both started by local administrator and the
> local administrator owns the backup job as well
> >--Original Message--
> >Is the SQL Server Agent service startup account the same
> account as the SQL
> >Server service?
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> The backup is just running to another drive on the same
> >> machine. My nightly backup routine goes and copies that
> >> directory to the central server for nightly backup
> >> >--Original Message--
> >> >Hi
> >> >
> >> >Make sure that the agent is running on a domain
> account
> >> that has access to
> >> >the other server. Are you
> >> using '\\machinename\sharename\file.bak' (UNC path)
> >> >as your naming standard for the backup file?
> >> >
> >> >You can not make a maintenance plan that uses a UNC
> >> share, so you have to
> >> >code it manually and setup a job (see BOL for BACKUP
> >> DATABASE).
> >> >
> >> >Cheers
> >> >Mike
> >> >
> >> >"Chris" wrote:
> >> >
> >> >> Hi All,
> >> >>
> >> >> Very new user to SQL admin. I need to backup a sql
> >> >> database, when I run it manually it runs fine and
> >> creates
> >> >> a backup without any issue. However when I try to
> >> >> schedule the job it does not save the schedule or
> run
> >> the
> >> >> backup.
> >> >>
> >> >> I am using Windows 2000 Server and SQL2000. Both are
> >> >> fully patched and have latest service packs.
> >> >>
> >> >> Am I doing something wrong or is this a bug. The SQL
> >> >> agent is running on the local machine, and the job
> >> >> appears in the backup list but always fails. I am
> >> trying
> >> >> to write to another data disk on the server with the
> >> >> routine to overwrite the file everytime.
> >> >>
> >> >> Any help would be great
> >> >>
> >> >.
> >> >
> >
> >
> >.
> >|||Ok have nearly sorted it out. I Hope!
I have managed to get the job to run by doing a database
maintainance job going. This starts automatically and
runs when scheduled. Only problem is when the SQL server
shuts down it stops the agent. When the server restarts
it doesn't restart the agent. I have set the propeties on
the SQL Agent to restart after 10 minutes and the service
does restart ok.
For some reason the SQL agent under server management
does not restart so the job fails. As soon as I right
click and say start the job runs ok. So my question is
Why doesn't this restart when the service does?
Chris
>--Original Message--
>Any error messages for the job? Look in the SQL Server
log too, usually
>found in the \Log subfolder of SQL Server.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
>> Yes they are both started by local administrator and
the
>> local administrator owns the backup job as well
>> >--Original Message--
>> >Is the SQL Server Agent service startup account the
same
>> account as the SQL
>> >Server service?
>> >
>> >--
>> >Peter Yeoh
>> >http://www.yohz.com
>> >Need smaller SQL2K backup files? Use MiniSQLBackup
>> Lite, free!
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> The backup is just running to another drive on the
same
>> >> machine. My nightly backup routine goes and copies
that
>> >> directory to the central server for nightly backup
>> >> >--Original Message--
>> >> >Hi
>> >> >
>> >> >Make sure that the agent is running on a domain
>> account
>> >> that has access to
>> >> >the other server. Are you
>> >> using '\\machinename\sharename\file.bak' (UNC path)
>> >> >as your naming standard for the backup file?
>> >> >
>> >> >You can not make a maintenance plan that uses a UNC
>> >> share, so you have to
>> >> >code it manually and setup a job (see BOL for
BACKUP
>> >> DATABASE).
>> >> >
>> >> >Cheers
>> >> >Mike
>> >> >
>> >> >"Chris" wrote:
>> >> >
>> >> >> Hi All,
>> >> >>
>> >> >> Very new user to SQL admin. I need to backup a
sql
>> >> >> database, when I run it manually it runs fine and
>> >> creates
>> >> >> a backup without any issue. However when I try to
>> >> >> schedule the job it does not save the schedule or
>> run
>> >> the
>> >> >> backup.
>> >> >>
>> >> >> I am using Windows 2000 Server and SQL2000. Both
are
>> >> >> fully patched and have latest service packs.
>> >> >>
>> >> >> Am I doing something wrong or is this a bug. The
SQL
>> >> >> agent is running on the local machine, and the
job
>> >> >> appears in the backup list but always fails. I am
>> >> trying
>> >> >> to write to another data disk on the server with
the
>> >> >> routine to overwrite the file everytime.
>> >> >>
>> >> >> Any help would be great
>> >> >>
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||>> I have set the propeties on the SQL Agent to restart after 10 minutes
and the service does restart ok.
>> For some reason the SQL agent under server management does not restart
so the job fails.
I do not quite understand the above two (conflicting) statements. I guess
the SQL Agent service is restarting, but the job is not running. Is it
because the job is not scheduled to run at that time?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
<anonymous@.discussions.microsoft.com> wrote in message
news:0b2801c4a6f6$21d26a20$a301280a@.phx.gbl...
> Ok have nearly sorted it out. I Hope!
> I have managed to get the job to run by doing a database
> maintainance job going. This starts automatically and
> runs when scheduled. Only problem is when the SQL server
> shuts down it stops the agent. When the server restarts
> it doesn't restart the agent. I have set the propeties on
> the SQL Agent to restart after 10 minutes and the service
> does restart ok.
> For some reason the SQL agent under server management
> does not restart so the job fails. As soon as I right
> click and say start the job runs ok. So my question is
> Why doesn't this restart when the service does?
> Chris
> >--Original Message--
> >Any error messages for the job? Look in the SQL Server
> log too, usually
> >found in the \Log subfolder of SQL Server.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:42e701c4a6d0$7ccaf260$a501280a@.phx.gbl...
> >> Yes they are both started by local administrator and
> the
> >> local administrator owns the backup job as well
> >> >--Original Message--
> >> >Is the SQL Server Agent service startup account the
> same
> >> account as the SQL
> >> >Server service?
> >> >
> >> >--
> >> >Peter Yeoh
> >> >http://www.yohz.com
> >> >Need smaller SQL2K backup files? Use MiniSQLBackup
> >> Lite, free!
> >> >
> >> >
> >> ><anonymous@.discussions.microsoft.com> wrote in message
> >> >news:45ec01c4a634$9c1441e0$a301280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> The backup is just running to another drive on the
> same
> >> >> machine. My nightly backup routine goes and copies
> that
> >> >> directory to the central server for nightly backup
> >> >> >--Original Message--
> >> >> >Hi
> >> >> >
> >> >> >Make sure that the agent is running on a domain
> >> account
> >> >> that has access to
> >> >> >the other server. Are you
> >> >> using '\\machinename\sharename\file.bak' (UNC path)
> >> >> >as your naming standard for the backup file?
> >> >> >
> >> >> >You can not make a maintenance plan that uses a UNC
> >> >> share, so you have to
> >> >> >code it manually and setup a job (see BOL for
> BACKUP
> >> >> DATABASE).
> >> >> >
> >> >> >Cheers
> >> >> >Mike
> >> >> >
> >> >> >"Chris" wrote:
> >> >> >
> >> >> >> Hi All,
> >> >> >>
> >> >> >> Very new user to SQL admin. I need to backup a
> sql
> >> >> >> database, when I run it manually it runs fine and
> >> >> creates
> >> >> >> a backup without any issue. However when I try to
> >> >> >> schedule the job it does not save the schedule or
> >> run
> >> >> the
> >> >> >> backup.
> >> >> >>
> >> >> >> I am using Windows 2000 Server and SQL2000. Both
> are
> >> >> >> fully patched and have latest service packs.
> >> >> >>
> >> >> >> Am I doing something wrong or is this a bug. The
> SQL
> >> >> >> agent is running on the local machine, and the
> job
> >> >> >> appears in the backup list but always fails. I am
> >> >> trying
> >> >> >> to write to another data disk on the server with
> the
> >> >> >> routine to overwrite the file everytime.
> >> >> >>
> >> >> >> Any help would be great
> >> >> >>
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.
Hi
"mj" wrote:

> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the information
from the remote location and then processes it in one batch, then the process
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John
|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"?
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>
|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
"TheSQLGuru" wrote:

> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
>
>
|||Hi
"mj" wrote:
[vbcol=seagreen]
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put the
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
>
> "TheSQLGuru" wrote:
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job steps.
What do you do with the files that the process has transferred?
John

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.Hi
"mj" wrote:

> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode
.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the informatio
n
from the remote location and then processes it in one batch, then the proces
s
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"'
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
----
--
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
----
--
"TheSQLGuru" wrote:

> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
>
>|||Hi
"mj" wrote:
[vbcol=seagreen]
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put th
e
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
--
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
--
>
> "TheSQLGuru" wrote:
>
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job step
s.
What do you do with the files that the process has transferred?
John

schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.Hi
"mj" wrote:
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the information
from the remote location and then processes it in one batch, then the process
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"'
--
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
----
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
----
"TheSQLGuru" wrote:
> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> >
> > Task Summary:
> > I have a remote user to sending records every 5 minutes by DTS package.
> > I think the best way to handle this is to create a job that will up the
> > database
> > into single user mode, set the remote user to the single user.
> > allow the job to run 10 to 15 minutes then take it out of single user
> > mode.
> >
> >
> > Task Break down:
> >
> > 1:
> > Put the database into single user mode.
> > assign myself as the user.
> > (How do I define which user has access?)
> >
> > 2:
> > Backup the database
> >
> > unassign myself as the single user
> >
> > assign remote user as the single user
> >
> > 3:
> >
> > Scheduled the job to execute for 15 minutes
> >
> > 4:
> > take database out of single user mode
> >
> > 5:
> > Schedule job to execute evey 6 hours.
> >
> >
> >
> >
>
>|||Hi
"mj" wrote:
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put the
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
>
> "TheSQLGuru" wrote:
> > What is the justification for this attempt to "lock the database into
> > single-user mode for a specific user"'
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "mj" <mj@.discussions.microsoft.com> wrote in message
> > news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> > >
> > > Task Summary:
> > > I have a remote user to sending records every 5 minutes by DTS package.
> > > I think the best way to handle this is to create a job that will up the
> > > database
> > > into single user mode, set the remote user to the single user.
> > > allow the job to run 10 to 15 minutes then take it out of single user
> > > mode.
> > >
> > >
> > > Task Break down:
> > >
> > > 1:
> > > Put the database into single user mode.
> > > assign myself as the user.
> > > (How do I define which user has access?)
> > >
> > > 2:
> > > Backup the database
> > >
> > > unassign myself as the single user
> > >
> > > assign remote user as the single user
> > >
> > > 3:
> > >
> > > Scheduled the job to execute for 15 minutes
> > >
> > > 4:
> > > take database out of single user mode
> > >
> > > 5:
> > > Schedule job to execute evey 6 hours.
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job steps.
What do you do with the files that the process has transferred?
John

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
Grant
Check the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.
Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>

Schedule a Stored Procedure to run overnight

What I want to do is to create a Web Interface to allow a User to click a
link and execute a Stored Procedure, I think this would be straight forward.
What I also want to do is to allow the user to select a check box to have
that stored procedure run over night or even schedule the Stored procedures
to run at differnet times.
through E-SQL I would just set up an Agent to do the job, the User won't
have that privelage they will have to go through a web page.
I am not even sure what approach to take? Is there a way to setup an agent
to run via stored procedures so through my code I can just call a system
stored procedure that will create the agent and schedule the job?
Thanks
SteveCreate a queue Table to hold the requests by users for Jobs to run, and a SQ
L
Agent Job to run every tem minutes, or whatever, to read the records out of
that table, and execute them if the current system datetime has passed the
datetime as stored in the queue table.. Then delete the queue table record
(or, - better, flag it as completed, when the job has run successfully.
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
AMB
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||a Queue table is not a bad idea, but I would have to write the name of a
Stored Procedure in a field and then have the agent read that name and
execute the named procedure - I am not sure how to do that but I like the
thought I will look into it.
"CBretana" wrote:
> Create a queue Table to hold the requests by users for Jobs to run, and a
SQL
> Agent Job to run every tem minutes, or whatever, to read the records out o
f
> that table, and execute them if the current system datetime has passed the
> datetime as stored in the queue table.. Then delete the queue table record
> (or, - better, flag it as completed, when the job has run successfully.
> "Steve K" wrote:
>|||Thanks I will look up these sp's and see how to use them
"Alejandro Mesa" wrote:
> See sp_add_job, sp_add_jobstep, sp_add_jobschedule, etc. in BOL.
>
> AMB
> "Steve K" wrote:
>|||Steve,
After some research, Alej's idea is much simpler. (use sp_add_job)
I discovered you can even add the job with a flag set (@.delete_level =
3) that will cause the job to execute once and then delete iteself, which wa
s
the main reason I would have rolled-my-own system to do what you are trying
to do...
"Steve K" wrote:

> What I want to do is to create a Web Interface to allow a User to click a
> link and execute a Stored Procedure, I think this would be straight forwar
d.
> What I also want to do is to allow the user to select a check box to have
> that stored procedure run over night or even schedule the Stored procedure
s
> to run at differnet times.
> through E-SQL I would just set up an Agent to do the job, the User won't
> have that privelage they will have to go through a web page.
> I am not even sure what approach to take? Is there a way to setup an agent
> to run via stored procedures so through my code I can just call a system
> stored procedure that will create the agent and schedule the job?
> Thanks
> Steve|||I think there is a disconnect here. The solution that Charly is recommending
involves writing a record into a queue table and then having a scheduled job
execute the stored proc using the queue value to determine the data on which
to
act. The job would have the stored proc that should be executed hard coded i
nto
its list of steps.
You aren't asking to execute any stored proc in the system like right?
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>a Queue table is not a bad idea, but I would have to write the name of a
> Stored Procedure in a field and then have the agent read that name and
> execute the named procedure - I am not sure how to do that but I like the
> thought I will look into it.
> "CBretana" wrote:
>|||sounds good that's the direction I am going to run.
Thanks
Steve
"CBretana" wrote:
> Steve,
> After some research, Alej's idea is much simpler. (use sp_add_job)
> I discovered you can even add the job with a flag set (@.delete_level =
> 3) that will cause the job to execute once and then delete iteself, which
was
> the main reason I would have rolled-my-own system to do what you are tryin
g
> to do...
> "Steve K" wrote:
>|||No, I would have a seperate table that contains a list of avalable stored
procedured users can run on demand from this table I would probably just
throw a grid on the screen with an execute link and allow the user to select
execute, the difficult part here is if the user wants to schedule that job t
o
run overnight then I would need some way to know which Sp they selected and
what time to run it.
does that make sense?
"Thomas Coleman" wrote:

> I think there is a disconnect here. The solution that Charly is recommendi
ng
> involves writing a record into a queue table and then having a scheduled j
ob
> execute the stored proc using the queue value to determine the data on whi
ch to
> act. The job would have the stored proc that should be executed hard coded
into
> its list of steps.
> You aren't asking to execute any stored proc in the system like right?
>
> Thomas
>
> "Steve K" <SteveK@.discussions.microsoft.com> wrote in message
> news:0C18A082-88DF-4924-B65B-B4AB13EAEF55@.microsoft.com...
>
>|||Do I understand what you are planning on doing? Yes, I think so. Do I unders
tand
why you are doing it? No, but that may not be important ;->
I'm generally inclined to abstract that functionality provided to users away
from the means by which it is implemented. Thus, I wouldn't have users selec
ting
stored procedures per se and it may be the case that you are not either. Sin
ce
I'm presuming that the set of functionality provided to the user is finite,
I'd
be inclined to either make multiple queues and multiple jobs for each stored
proc or perhaps a single queue and multiple jobs for each stored proc. Users
would be presented with a list of actions to perform such that behind the sc
enes
would be a mapping of actions to stored procedures. If the user asked for a
scheduled action, the system would map that request to a post into the
appropriate queue table. That queue table would store the time and day that
the
stored procedure should be executed and the scheduled job would be set to po
ll
continuously (say every minute or every five minutes) to determine if there
is
work to do.
However, since I don't know the specifics of the problem you are attempting
to
solve, I may be completely off the mark.
Thomas
"Steve K" <SteveK@.discussions.microsoft.com> wrote in message
news:B4674B29-52CA-4320-9408-6C47600FEBB7@.microsoft.com...
> No, I would have a seperate table that contains a list of avalable stored
> procedured users can run on demand from this table I would probably just
> throw a grid on the screen with an execute link and allow the user to sele
ct
> execute, the difficult part here is if the user wants to schedule that job
to
> run overnight then I would need some way to know which Sp they selected an
d
> what time to run it.
> does that make sense?
> "Thomas Coleman" wrote:
>

Schedule a report to run at a particular time with user filled parameters

I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.

Thanks,

Chris

You could use a data driven subscription, which relies on a table you create to store the parameters for the report. Then provide the user with a simple interface to set the parameters in the table.

Or you could let them create a linked report in their My Reports folder, and set up their own subscription from there, which allows them to specify parameters values.

|||Would you mind elaborating on this technique, or pass along some documentation/samples for this? I am most interested in how you setup this 'table' that stores the parameters for the report and of course how you configure the report to use the values in the table.

Thanks!|||

I never did find a great solution to this problem. Please let me know if you do find one.

Thanks,

|||

What part of the solutions that you were offered didn't you like? I thought they were both good, depending on business requirements, and I wasn't too sure from what you described which one would meet yours... but didn't expect to hear that you used neither <s>.

So... what was the issue in your case?

>L<

PS. You can also roll your own. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2022350&SiteID=1. But I'm not (yet) convinced you need to, in your case...

|||Neither of the above options worked for you?

Schedule a report to run at a particular time with user filled parameters

I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.

Thanks,

Chris

You could use a data driven subscription, which relies on a table you create to store the parameters for the report. Then provide the user with a simple interface to set the parameters in the table.

Or you could let them create a linked report in their My Reports folder, and set up their own subscription from there, which allows them to specify parameters values.

|||Would you mind elaborating on this technique, or pass along some documentation/samples for this? I am most interested in how you setup this 'table' that stores the parameters for the report and of course how you configure the report to use the values in the table.

Thanks!
|||

I never did find a great solution to this problem. Please let me know if you do find one.

Thanks,

|||

What part of the solutions that you were offered didn't you like? I thought they were both good, depending on business requirements, and I wasn't too sure from what you described which one would meet yours... but didn't expect to hear that you used neither <s>.

So... what was the issue in your case?

>L<

PS. You can also roll your own. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2022350&SiteID=1. But I'm not (yet) convinced you need to, in your case...

|||Neither of the above options worked for you?

Schedule a report to run at a particular time with user filled parameters

I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.

Thanks,

Chris

You could use a data driven subscription, which relies on a table you create to store the parameters for the report. Then provide the user with a simple interface to set the parameters in the table.

Or you could let them create a linked report in their My Reports folder, and set up their own subscription from there, which allows them to specify parameters values.

|||Would you mind elaborating on this technique, or pass along some documentation/samples for this? I am most interested in how you setup this 'table' that stores the parameters for the report and of course how you configure the report to use the values in the table.

Thanks!
|||

I never did find a great solution to this problem. Please let me know if you do find one.

Thanks,

|||

What part of the solutions that you were offered didn't you like? I thought they were both good, depending on business requirements, and I wasn't too sure from what you described which one would meet yours... but didn't expect to hear that you used neither <s>.

So... what was the issue in your case?

>L<

PS. You can also roll your own. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2022350&SiteID=1. But I'm not (yet) convinced you need to, in your case...

|||Neither of the above options worked for you?