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

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 Info.

Hi

I am using RS 2000. Currently I am scheduling the job by manually giving the date, time. Is there any possibility to make it as data driven so that I can configure each user with different timings.

Regards,

Venkataraman M

You can create multiple data driven subscriptions to run at different schedules for different users or user groups. You can filter users for which the subscription is run in data driven subscription query.

Scheduling DTS Package in SQL 2005

We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
--
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:
> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John

Scheduling DTS Package in SQL 2005

We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8Hi Daniel
"Daniel" wrote:

> We have an issue scheduling a DTS package we have created... We can run th
e
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrec
t)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/Sh...137778&SiteID=1 SP1
fixed something that caused this!
John

Scheduling DTS Package in SQL 2005

We have an issue scheduling a DTS package we have created... We can run the
package manually in SSIS with NO PROBLEMS... but when we attempt to
schedule the package we receive the following error:
Date 5/9/2007 6:12:57 PM
Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
Message
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
What configuration or settings are we missing to cause this to happen when
scheduled... If we execute it manually it runs fine.
Job is created
Credentials are setup
Proxies is setup under SSIS Package Execution
Using ASP, SQL2005 and DW8
Hi Daniel
"Daniel" wrote:

> We have an issue scheduling a DTS package we have created... We can run the
> package manually in SSIS with NO PROBLEMS... but when we attempt to
> schedule the package we receive the following error:
> Date 5/9/2007 6:12:57 PM
> Log SQL Agent (Current - 5/9/2007 6:12:00 PM)
> Message
> [LOG] Unable to read local eventlog (reason: The parameter is incorrect)
> What configuration or settings are we missing to cause this to happen when
> scheduled... If we execute it manually it runs fine.
> Job is created
> Credentials are setup
> Proxies is setup under SSIS Package Execution
>
> --
> Using ASP, SQL2005 and DW8
>
What version of SQL 2005 are you using? According to
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=137778&SiteID=1 SP1
fixed something that caused this!
John
sql

scheduler brings down the system?

I am hoping this is something simple, a security piece I've messed up.

When I run my dts package manually I get no errors and sucessful completion of each step. So I set my SQL Agent to run thepackage 15 minutes after every hour between 5am and 5pm, these being 15 minutes after the times the new data files are dropped to me.

But instead of running the package it disconnects all the remote desktop users and fails. When remote users can get back on we are faced with a system has recovered from a serious error message. The error message posted to the event logs is empty though.

Any one have any ideas?

We are running MS Server 2003 and SQL Server 2000

Thanks,

Carrie

It is not clear what is happening. When you say remote desktop users are you referring to RDP connections or Terminal Server sessions to your Windows Server. If so then it is not clear how this is a SQL Server issue.

If you are referring to SQL Server users then does the SQL Server service crash when you run the package?

Did you check the SQL Server error logs, windows eventlogs and SQL Server log directory for dump files?

Can you also repro the problem on another SQL Server with your database and package? If it is specific to this server, did you check for database corruptions or other hardware related issues?

Do you have the latest service pack installed on the SQL Server? If not, you should install it on a repro system and test your package again. If the problem doesn't happen then the bug you encountered has been fixed in a later service pack or hotfix.

If you are running the latest service pack and you can repro this problem on other SQL Servers with same package/database then it is a bug in SQL Server. You can file one with the repro details at http://connect.microsoft.com.

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.
Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen
|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen
|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>
|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>
|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END

> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>
|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>>
>|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Dear Dan,
>> Thank you for your advice and it works properly.
>> I have set up a job to with 2 steps - The first one is to make the backup
>> of the Production DB and the second one is to restore to the Testing DB.
>> I would like to make 2 enhancement and would like to seek your advice.
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
>> Thanks again.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax
>> details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do
>> so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every
>> Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>>
>

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END

> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Scheduled Job works when run manually but not when scheduled

Hi
I have a very simple local package, which performs the following:
1. Execute SQL Task
Delete from PURCHASE_LEDGER
Delete from SALES_LEDGER
2. Transform Data task
Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
There is a workflow between steps 1 and 2, so that step 2 only
executes if step 1 is successfull.
The connection to SAGE is ODBC (which I know works fine) and to SQL it
is OLE.
This package works perfectly if I right click on it and select
execute. However, if I schedule it, the first step works perfectly but
the second step fails. I know that the first step works because the
tables are empty. If I remove the first step and the workflow from the
package, the transform data task still does not work when scheduled,
but is fine when I run it manually. In the error log, it just says:
"SQL Server Scheduled Job 'Update SAGE
Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
was invoked by Schedule 6 (Update SAGE Tables). The last step to run
was step 1 (Update SAGE Tables)."
Any idea why this package should work manually but not when scheduled?
Thanks
Colin
Check the following knowledge base article for information
on troubleshooting this issue:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On 30 Mar 2007 03:11:12 -0700, "Bobby"
<bobby2@.blueyonder.co.uk> wrote:

>Hi
>I have a very simple local package, which performs the following:
>1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
>2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
>There is a workflow between steps 1 and 2, so that step 2 only
>executes if step 1 is successfull.
>The connection to SAGE is ODBC (which I know works fine) and to SQL it
>is OLE.
>This package works perfectly if I right click on it and select
>execute. However, if I schedule it, the first step works perfectly but
>the second step fails. I know that the first step works because the
>tables are empty. If I remove the first step and the workflow from the
>package, the transform data task still does not work when scheduled,
>but is fine when I run it manually. In the error log, it just says:
>"SQL Server Scheduled Job 'Update SAGE
>Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
>Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
>was invoked by Schedule 6 (Update SAGE Tables). The last step to run
>was step 1 (Update SAGE Tables)."
>Any idea why this package should work manually but not when scheduled?
>Thanks
>Colin
|||On 30 Mar, 12:52, Sue Hoegemeier <S...@.nomail.please> wrote:
> Check the following knowledge base article for information
> on troubleshooting this issue:
> INF: How to Run a DTS Package as a Scheduled Jobhttp://support.microsoft.com/?id=269074
> -Sue
Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
It's set up on my p/c, which is why it works manually, but when it's
scheduled it runs from the server and can't find the link to SAGE.
Thanks
Colin
|||> Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> It's set up on my p/c, which is why it works manually, but when it's
> scheduled it runs from the server and can't find the link to SAGE.
>
Hmmm... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?
Colin
|||On 30 Mar, 13:58, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hmmm... Not quite as simple as I thought. Anybody know how to install
> the SAGE ODBC driver without installing the whole software package?
> Colin
Ok, forget it - I've worked it out
Colin

Scheduled Job works when run manually but not when scheduled

Hi
I have a very simple local package, which performs the following:
1. Execute SQL Task
Delete from PURCHASE_LEDGER
Delete from SALES_LEDGER
2. Transform Data task
Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
There is a workflow between steps 1 and 2, so that step 2 only
executes if step 1 is successfull.
The connection to SAGE is ODBC (which I know works fine) and to SQL it
is OLE.
This package works perfectly if I right click on it and select
execute. However, if I schedule it, the first step works perfectly but
the second step fails. I know that the first step works because the
tables are empty. If I remove the first step and the workflow from the
package, the transform data task still does not work when scheduled,
but is fine when I run it manually. In the error log, it just says:
"SQL Server Scheduled Job 'Update SAGE
Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
was invoked by Schedule 6 (Update SAGE Tables). The last step to run
was step 1 (Update SAGE Tables)."
Any idea why this package should work manually but not when scheduled?
Thanks
ColinHi Colin,
Are the accounts you use to 1) execut the package manually and 2) the
account that runs it via a schedule, the same account?
Without digging too deeply to me it sounds like the account that is used for
the scheduled job, isnt the same as the one where you execute it manually.
Barry
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1175249472.172771.158350@.y80g2000hsf.googlegroups.com...
> Hi
> I have a very simple local package, which performs the following:
> 1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
> 2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
> There is a workflow between steps 1 and 2, so that step 2 only
> executes if step 1 is successfull.
> The connection to SAGE is ODBC (which I know works fine) and to SQL it
> is OLE.
> This package works perfectly if I right click on it and select
> execute. However, if I schedule it, the first step works perfectly but
> the second step fails. I know that the first step works because the
> tables are empty. If I remove the first step and the workflow from the
> package, the transform data task still does not work when scheduled,
> but is fine when I run it manually. In the error log, it just says:
> "SQL Server Scheduled Job 'Update SAGE
> Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
> Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
> was invoked by Schedule 6 (Update SAGE Tables). The last step to run
> was step 1 (Update SAGE Tables)."
> Any idea why this package should work manually but not when scheduled?
> Thanks
> Colin
>|||Check the following knowledge base article for information
on troubleshooting this issue:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On 30 Mar 2007 03:11:12 -0700, "Bobby"
<bobby2@.blueyonder.co.uk> wrote:
>Hi
>I have a very simple local package, which performs the following:
>1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
>2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
>There is a workflow between steps 1 and 2, so that step 2 only
>executes if step 1 is successfull.
>The connection to SAGE is ODBC (which I know works fine) and to SQL it
>is OLE.
>This package works perfectly if I right click on it and select
>execute. However, if I schedule it, the first step works perfectly but
>the second step fails. I know that the first step works because the
>tables are empty. If I remove the first step and the workflow from the
>package, the transform data task still does not work when scheduled,
>but is fine when I run it manually. In the error log, it just says:
>"SQL Server Scheduled Job 'Update SAGE
>Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
>Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
>was invoked by Schedule 6 (Update SAGE Tables). The last step to run
>was step 1 (Update SAGE Tables)."
>Any idea why this package should work manually but not when scheduled?
>Thanks
>Colin|||On 30 Mar, 12:52, Sue Hoegemeier <S...@.nomail.please> wrote:
> Check the following knowledge base article for information
> on troubleshooting this issue:
> INF: How to Run a DTS Package as a Scheduled Jobhttp://support.microsoft.com/?id=269074
> -Sue
Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
It's set up on my p/c, which is why it works manually, but when it's
scheduled it runs from the server and can't find the link to SAGE.
Thanks
Colin|||> Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> It's set up on my p/c, which is why it works manually, but when it's
> scheduled it runs from the server and can't find the link to SAGE.
>
Hmmm... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?
Colin|||On 30 Mar, 13:58, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> > Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> > It's set up on my p/c, which is why it works manually, but when it's
> > scheduled it runs from the server and can't find the link to SAGE.
> Hmmm... Not quite as simple as I thought. Anybody know how to install
> the SAGE ODBC driver without installing the whole software package?
> Colin
Ok, forget it - I've worked it out
Colin

Scheduled Job works when run manually but not when scheduled

Hi
I have a very simple local package, which performs the following:
1. Execute SQL Task
Delete from PURCHASE_LEDGER
Delete from SALES_LEDGER
2. Transform Data task
Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
There is a workflow between steps 1 and 2, so that step 2 only
executes if step 1 is successfull.
The connection to SAGE is ODBC (which I know works fine) and to SQL it
is OLE.
This package works perfectly if I right click on it and select
execute. However, if I schedule it, the first step works perfectly but
the second step fails. I know that the first step works because the
tables are empty. If I remove the first step and the workflow from the
package, the transform data task still does not work when scheduled,
but is fine when I run it manually. In the error log, it just says:
"SQL Server Scheduled Job 'Update SAGE
Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
was invoked by Schedule 6 (Update SAGE Tables). The last step to run
was step 1 (Update SAGE Tables)."
Any idea why this package should work manually but not when scheduled?
Thanks
ColinHi Colin,
Are the accounts you use to 1) execut the package manually and 2) the
account that runs it via a schedule, the same account?
Without digging too deeply to me it sounds like the account that is used for
the scheduled job, isnt the same as the one where you execute it manually.
Barry
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1175249472.172771.158350@.y80g2000hsf.googlegroups.com...
> Hi
> I have a very simple local package, which performs the following:
> 1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
> 2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
> There is a workflow between steps 1 and 2, so that step 2 only
> executes if step 1 is successfull.
> The connection to SAGE is ODBC (which I know works fine) and to SQL it
> is OLE.
> This package works perfectly if I right click on it and select
> execute. However, if I schedule it, the first step works perfectly but
> the second step fails. I know that the first step works because the
> tables are empty. If I remove the first step and the workflow from the
> package, the transform data task still does not work when scheduled,
> but is fine when I run it manually. In the error log, it just says:
> "SQL Server Scheduled Job 'Update SAGE
> Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
> Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
> was invoked by Schedule 6 (Update SAGE Tables). The last step to run
> was step 1 (Update SAGE Tables)."
> Any idea why this package should work manually but not when scheduled?
> Thanks
> Colin
>|||Check the following knowledge base article for information
on troubleshooting this issue:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On 30 Mar 2007 03:11:12 -0700, "Bobby"
<bobby2@.blueyonder.co.uk> wrote:

>Hi
>I have a very simple local package, which performs the following:
>1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
>2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
>There is a workflow between steps 1 and 2, so that step 2 only
>executes if step 1 is successfull.
>The connection to SAGE is ODBC (which I know works fine) and to SQL it
>is OLE.
>This package works perfectly if I right click on it and select
>execute. However, if I schedule it, the first step works perfectly but
>the second step fails. I know that the first step works because the
>tables are empty. If I remove the first step and the workflow from the
>package, the transform data task still does not work when scheduled,
>but is fine when I run it manually. In the error log, it just says:
>"SQL Server Scheduled Job 'Update SAGE
>Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
>Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
>was invoked by Schedule 6 (Update SAGE Tables). The last step to run
>was step 1 (Update SAGE Tables)."
>Any idea why this package should work manually but not when scheduled?
>Thanks
>Colin|||On 30 Mar, 12:52, Sue Hoegemeier <S...@.nomail.please> wrote:
> Check the following knowledge base article for information
> on troubleshooting this issue:
> INF: How to Run a DTS Package as a Scheduled Jobhttp://support.microsoft.c
om/?id=269074
> -Sue
Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
It's set up on my p/c, which is why it works manually, but when it's
scheduled it runs from the server and can't find the link to SAGE.
Thanks
Colin|||> Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> It's set up on my p/c, which is why it works manually, but when it's
> scheduled it runs from the server and can't find the link to SAGE.
>
Hmmm... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?
Colin|||On 30 Mar, 13:58, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> Hmmm... Not quite as simple as I thought. Anybody know how to install
> the SAGE ODBC driver without installing the whole software package?
> Colin
Ok, forget it - I've worked it out
Colin

Scheduled job executing Stored procedures fails

Hi,
I have 4 seperate stored procedures that if I run manually from Query
Analyzer work fine. Each one picks rows from a number of joined tables
depending on a date criteria. Each SP is exactly the same except for the
date ranges.
When they are run manually they do what I expect and I end up with 4 tables
each populated with a 1/2 dozen records.
However when I create a job in Enterprise Manager and add four steps into it
(one for each job) I end up with it creating four empty tables even though
two minutes ago I ran them manually and got what I expect and if I run them
manually after the scheduled job ran I get the correct results.
Each step says something like "Exec MoneyLaundering_21Days" and that's it.
The correct database is chosen in the top of the scheduled job dialogue box.
I've even tried scheduling a new job and just putting one SP into it but I
get the same result. I'm kinda at my wits end here so any assistance would
be much appreciated.
Regards,
Matt Lemon.Do you have the sp wrapped in a transaction that you are not committing?
Andrew J. Kelly SQL MVP
"news.esat.net" <matt.lemon@.williamfry.ie> wrote in message
news:dvttn9$cj4$1@.reader01.news.esat.net...
> Hi,
> I have 4 seperate stored procedures that if I run manually from Query
> Analyzer work fine. Each one picks rows from a number of joined tables
> depending on a date criteria. Each SP is exactly the same except for the
> date ranges.
> When they are run manually they do what I expect and I end up with 4
> tables each populated with a 1/2 dozen records.
> However when I create a job in Enterprise Manager and add four steps into
> it (one for each job) I end up with it creating four empty tables even
> though two minutes ago I ran them manually and got what I expect and if I
> run them manually after the scheduled job ran I get the correct results.
> Each step says something like "Exec MoneyLaundering_21Days" and that's it.
> The correct database is chosen in the top of the scheduled job dialogue
> box.
> I've even tried scheduling a new job and just putting one SP into it but I
> get the same result. I'm kinda at my wits end here so any assistance
> would be much appreciated.
> Regards,
> Matt Lemon.
>|||Did you look at error log? Have you tried profiler?
I assume, your procedures create tables AND populate them with some records
right?
Maybe the identity under which job is running doesn't have appropriate
credentials to populate tables?
Peter

Scheduled DTS package not running

I have created a DTS package to read data from a SQL Server table and copy it to an Excel file on one sheet. I have tested the DTS package manually and it runs. I scheduled the package but it keeps failing giving the following error message. The path to the Excel file is valid and the SQL server is connected to the server where the Excel file resides.

... DTSRun: Executing... DTSRun OnStart: Copy Data from vw_Symbols to vw_Symbols$ Step DTSRun OnError: Copy Data from vw_Symbols to vw_Symbols$ Step, Error = -2147467259 (80004005) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error sour... Process Exit Code 1. The step failed.

Is S:\ a network-shared drive?

The Agent runs your package under its own account, so if S:\ is a network drive mapped under your account, it might not be visible to Agent's account.

You may create Agent Proxy to make package run under your account.|||

I have a same or pretty similar problem, but in my case it′s when a trying to schedule a DTS and leave a file in a share point tool. If you have some information or tips I will appreciate it.

This is the message error.

Executed as user: Server Name\CRMSandbox. ...Step_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failur... Process Exit Code 2. The step failed.

Thanks.

Miguel

|||

Hi, with regards to creating "Agnet Proxy" have you any further information regarding this?

Thank you.

|||Search for "Creating SQL Server Agent Proxies" in Books Online.

Scheduled DTS package not running

I have created a DTS package to read data from a SQL Server table and copy it to an Excel file on one sheet. I have tested the DTS package manually and it runs. I scheduled the package but it keeps failing giving the following error message. The path to the Excel file is valid and the SQL server is connected to the server where the Excel file resides.

... DTSRun: Executing... DTSRun OnStart: Copy Data from vw_Symbols to vw_Symbols$ Step DTSRun OnError: Copy Data from vw_Symbols to vw_Symbols$ Step, Error = -2147467259 (80004005) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error sour... Process Exit Code 1. The step failed.

Is S:\ a network-shared drive?

The Agent runs your package under its own account, so if S:\ is a network drive mapped under your account, it might not be visible to Agent's account.

You may create Agent Proxy to make package run under your account.|||

I have a same or pretty similar problem, but in my case it′s when a trying to schedule a DTS and leave a file in a share point tool. If you have some information or tips I will appreciate it.

This is the message error.

Executed as user: Server Name\CRMSandbox. ...Step_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failur... Process Exit Code 2. The step failed.

Thanks.

Miguel

|||

Hi, with regards to creating "Agnet Proxy" have you any further information regarding this?

Thank you.

|||Search for "Creating SQL Server Agent Proxies" in Books Online.

Scheduled DTS package not running

I have created a DTS package to read data from a SQL Server table and copy it to an Excel file on one sheet. I have tested the DTS package manually and it runs. I scheduled the package but it keeps failing giving the following error message. The path to the Excel file is valid and the SQL server is connected to the server where the Excel file resides.

... DTSRun: Executing... DTSRun OnStart: Copy Data from vw_Symbols to vw_Symbols$ Step DTSRun OnError: Copy Data from vw_Symbols to vw_Symbols$ Step, Error = -2147467259 (80004005) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'S:\Mission Critical Enterprises\Current Projects\JPMC Aperture Rollout 05-100-1-0001\Symbol Requests\JPMC - Symbols Added.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error sour... Process Exit Code 1. The step failed.

Is S:\ a network-shared drive?

The Agent runs your package under its own account, so if S:\ is a network drive mapped under your account, it might not be visible to Agent's account.

You may create Agent Proxy to make package run under your account.|||

I have a same or pretty similar problem, but in my case it′s when a trying to schedule a DTS and leave a file in a share point tool. If you have some information or tips I will appreciate it.

This is the message error.

Executed as user: Server Name\CRMSandbox. ...Step_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Failure creating file. Error source: Microsoft JET Database Engine Help file: Help context: 5003436 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -329978796 (EC54EC54) Error string: Failur... Process Exit Code 2. The step failed.

Thanks.

Miguel

|||

Hi, with regards to creating "Agnet Proxy" have you any further information regarding this?

Thank you.

|||Search for "Creating SQL Server Agent Proxies" in Books Online.

Scheduled DTS Job Failed Urgent!

Hi All,

SQL Server 7
I have many DTS jobs running some of them have failed to run as per schedule time, when i try to run manually its giving the below error. pls see the below error
and help me as it is a case of urgency.

"Backup,CHECKALLOC,bulk copy, SELECT INTO,and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed."

The Scheduled backup has also failed. when i look at the job history details it gives
sqlmaint.exe failed (SQLSTATE 42000) (Error 22029). The step failed.First, check to make sure that all jobs are currently not running.

Next, disable non-essential jobs to quiesce activity.

Next, how large is the database? If not very large (<500MB), I would automatically run a DBCC CHECKDB just to make sure everything is okay. The only reason to consider database size here is that the CHECKDB command can take a long time to run. Your decision here will have to made based on your assessment of the business' need to get back on line.

Next, re-run your back up job. Make sure you get a clean run of the back-up process.

Finally, re-enable your DTS jobs. Look to see if you have any tasks within the DTS packages that run around the time of your backup that use some of the restricted functions/commands/statements. Focus on these packages first and see if they can be fixed or moved.

If you are backing up to tape, consider backing up to Disk. It is hundreds of time faster and will allow the backup job to complete more quickly.

HTH,
hmscott

scheduled dts fails but runs when done manually

I'm a newbie to sql2000. i have some jobs that copy stuff from a non microsoft database(titanium) to SQL2000. i use the propriety odbc drivers provided by titanium. when run manually in enterprize manager,it works. but scheduled as a job it fails with the message "System cannot find the specified File" i've read up and tried microsofts suggestions ie ensuring that the sql server agent has rights to the folders used etc. i've also tried puttin in the the dts owner and user passwords. each time it fails with the same error.
any one out there who can help?:confused:I'm a SQL2K newbie and I've experienced a similar problem.

When you run the DTS pkg manually, it runs on your client machine;
when you run it as a job it runs on the SQL server.

If you can run the DTS pkg manually on the SQL server (from its console), do that. You should get more details on the error. Most likely
something is missing from the PATH environment variable on the SQL
server (and you can compare to what you have for this on your client
machine).

Good luck!

Jeff|||Jeff's on the right track. When you develop a DTS package remotely, it remembers the paths based on where you developed it. You would either need to
- develop the package locally (on the server), in which case it would not run interactively from a remote machine, or

- use a unc path, ie. \\servername\sharename\filename. In this case, it should run interactively or scheduled.

Steve|||I'd also add to check permissions. You need to keep in mind that the job on the server is not going to run as "you" but as the sql server agent, so it maynot have the same authorization to access drives and shares as you do. That's the one that always catches me.