DTS Scheduled tasks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Scheduled tasks

Author

Topic

mukta
Starting Member

12 Posts

Posted - 2004-04-07 : 07:05:20

Hi I am facing a problem with scheduiling a task in DTS which runs directly fine.Cana nybody help?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 07:20:00

mukta, you need to go into more detail for us if you want sufficient help.

Have you created a job to run the package?
Have you created a schedule to run the job?

as much detail as possible please.

Duane.

mukta
Starting Member

12 Posts

Posted - 2004-04-07 : 07:21:52

yes I have created a package job with the owner as sa.

quote:
Originally posted by mukta

Hi I am facing a problem with scheduiling a task in DTS which runs directly fine.Cana nybody help?

mukta
Starting Member

12 Posts

Posted - 2004-04-07 : 07:30:46

Hi I need to know what else u need to know other than the following:::

1. I create a job using DTS wizard, and schedule it for everyday at 2:00 pm.
2. the wizard runs fine when done directly
3. The job starts and fails with the following warning message in the event log::::Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 4/7/2004
Time: 2:01:22 PM
User: N/A
Computer: INTRANET
Description:
SQL Server Scheduled Job 'TestIntranetExportOnEPO' (0x8DA07349F2111949A424D9AC1D606C05) - Status: Failed - Invoked on: 2004-04-07 14:00:01 - Message: The job failed. The Job was invoked by Schedule 11 (TestIntranetExportOnEPO). The last step to run was step 1 (TestIntranetExportOnEPO).

quote:
Originally posted by ditch

mukta, you need to go into more detail for us if you want sufficient help.

Have you created a job to run the package?
Have you created a schedule to run the job?

as much detail as possible please.

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 07:56:57

For a start you might be able to get a better error message if you run this query from query analyzer.

use msdb

go

select a.*
from sysjobhistory a
join sysjobs b
on a.job_id = b.job_id
where b.name = 'your job name'

Duane.

mukta
Starting Member

12 Posts

Posted - 2004-04-07 : 08:39:53

I get 2 rows with the following messages:::
1. ... DTSRun: Executing... DTSRun OnStart:
Copy SQL Server Objects DTSRun OnProgress:
Copy SQL Server Objects;
Scripting objects for Transfer;
PercentComplete = 0; ProgressCount = 0
DTSRun OnProgress:
Copy SQL Server Objects; Scripting Logins; Pe

2. The job failed. The Job was invoked by Schedule 12 (ExportPMAtoEPO).
The last step to run was step 1 (ExportPMAtoEPO).

quote:
Originally posted by ditch

For a start you might be able to get a better error message if you run this query from query analyzer.

use msdb

go

select a.*
from sysjobhistory a
join sysjobs b
on a.job_id = b.job_id
where b.name = 'your job name'

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-07 : 08:51:04

The first rows data has been cut off by your qa only showing about 256 columns.
If you increase the size of the 'Maximum Characters Per Column' by clicking TOOLS/OPTIONS and then clicking the RESULTS tab, then you might get the full message when you run the query again.

Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-07 : 09:14:15

Who is listed as the owner of the DTS package?

Does the account that run SQL Server have access to the location you are transferring to?

Does the owner of the DTS package have access?

Both need to be true for a transfer to work.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mukta
Starting Member

12 Posts

Posted - 2004-04-07 : 23:54:37

yes the owner in both the cases is Sa..who I think has all the powers
and when i see the whole message thsi si what I get::

... DTSRun: Executing... DTSRun OnStart: Copy SQL Server Objects DTSRun OnProgress: Copy SQL Server Objects; Scripting objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Scripting Users; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Scripting Users : 0 Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 0 Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Scripting Users : 9 Percent Completed; PercentComplete = 9; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 8 Percent Completed; PercentComplete = 8; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Scripting Users : 18 Percent Completed; PercentComplete = 18;
ProgressCou... Process Exit Code 1. The step failed.
The job failed. The Job was invoked by Schedule 14 (PMAExportToEPO).
The last step to run was step 1 (PMAExportToEPO).

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-08 : 02:34:56

You think he has all the powers or he has all the powers? You need to go to Services on your SQL Server and see what account the SQL Server is running on. You then need to go to the server you're trying to script things out to and make sure that same account has the sufficient permissions.

Are you just trying to trnasfer logins and sql objects from one database to another with this DTS package? That's what it looks like.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mukta
Starting Member

12 Posts

Posted - 2004-04-08 : 02:49:35

Kindly guide me as to how can I find out what account the SQL Server is running on, and which services are u talking abut under which option?
What I am also doing is just copying data from one database to another database through my package you are right.
Please help as this is urgent.!!

quote:
Originally posted by derrickleggett

You think he has all the powers or he has all the powers? You need to go to Services on your SQL Server and see what account the SQL Server is running on. You then need to go to the server you're trying to script things out to and make sure that same account has the sufficient permissions.

Are you just trying to trnasfer logins and sql objects from one database to another with this DTS package? That's what it looks like.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mukta
Starting Member

12 Posts

Posted - 2004-04-08 : 07:20:47

the sa has all the pwers but i stheir some special role for scheduling tasks on SQL Server?

quote:
Originally posted by derrickleggett

You think he has all the powers or he has all the powers? You need to go to Services on your SQL Server and see what account the SQL Server is running on. You then need to go to the server you're trying to script things out to and make sure that same account has the sufficient permissions.

Are you just trying to trnasfer logins and sql objects from one database to another with this DTS package? That's what it looks like.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

[/quote]

mukta
Starting Member

12 Posts

Posted - 2004-04-08 : 07:51:55

I tried again creating a new task by sa login and teh same problem::

The job failed. The Job was invoked by Schedule 1 (Intranet Data Backup). The last step to run was step 1 (Intranet Data Backup).

quote:
Originally posted by mukta

the sa has all the pwers but i stheir some special role for scheduling tasks on SQL Server?

quote:
Originally posted by derrickleggett

You think he has all the powers or he has all the powers? You need to go to Services on your SQL Server and see what account the SQL Server is running on. You then need to go to the server you're trying to script things out to and make sure that same account has the sufficient permissions.

Are you just trying to trnasfer logins and sql objects from one database to another with this DTS package? That's what it looks like.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

[/quote]

mkaney
Starting Member

1 Post

Posted - 2004-04-09 : 11:28:46

I am having the same problem as this gentlemann. I am fairly new to SQL server but I am fairly capable with the rest of Windows. I am running on Windows 2003 Enterprise... I changed all the SQL related services to run under a specific account. That account has admin and domain admin rights. I added the user as a windows type in SQL server and added them as an operator as well. I added the same user name as an sql type in addition (without the domain prefix),and made it a system administrator. Then I changed all related settings in the jobs, dts packages, and what not. I can run the DTS packages find by themselves, but when I run the jobs manually or by schedule I have the same exact result. The jobs do not fail either, they hang and "run" forever and ever.

mukta
Starting Member

12 Posts

Posted - 2004-04-12 : 00:46:51

Hi ! Okay that means your DTS package keeps running when scheduled? Can u help me with this then?
Tell me how did you manage to add the "sa" user as a windows type in SQL server please? Can you give me the exact steps so that I can also check it at my end?

quote:
Originally posted by mkaney

I am having the same problem as this gentlemann. I am fairly new to SQL server but I am fairly capable with the rest of Windows. I am running on Windows 2003 Enterprise... I changed all the SQL related services to run under a specific account. That account has admin and domain admin rights. I added the user as a windows type in SQL server and added them as an operator as well. I added the same user name as an sql type in addition (without the domain prefix),and made it a system administrator. Then I changed all related settings in the jobs, dts packages, and what not. I can run the DTS packages find by themselves, but when I run the jobs manually or by schedule I have the same exact result. The jobs do not fail either, they hang and "run" forever and ever.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-08-11 : 06:38:05

hi, is this problem solved?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources