Hi all,
We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.
DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.
TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.
-PatP
Showing posts with label copying. Show all posts
Showing posts with label copying. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
DTS Package problem?
I have set up a couple of DTS local packages to run ActiveX scripts creating XML files and copying them to our webserver (on the same domain).
I can run them OK manually via the 'Execute' package command in the drop down list when I right click on them and also get the 'Package successfully run' message back on running them, but when I schedule them to run overnight I find the SQL Server Agent reports them as 'failed' .
What am I doing wrong?
This looks like a permissions issue...do you have a proxy account setup for the SQL Agent? Look here for info...http://www.support.microsoft.com/kb/890775
Brad Feaker
Ex nihilo, nihil fit
|||As referred if you are scheduling the DTS package then make sure to check privileges for the SQLAgent service account on those process, you can start the SQLAgent using a local account with Admin privleges to complete the task.Friday, February 24, 2012
DTS job is not copying all the data but still the Job History shows Successful?
Hello everybody.
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin Moustgaard
Job Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?[vbcol=seagreen]
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time[vbcol=seagreen]
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
package..[vbcol=seagreen]
> ?
more
> of
> there
> logging
>
|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> to
> existing
> time
> package..
2000[vbcol=seagreen]
other
> more
>
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin Moustgaard
Job Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>
|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?[vbcol=seagreen]
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging
>
|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time[vbcol=seagreen]
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
package..[vbcol=seagreen]
> ?
more
> of
> there
> logging
>
|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> to
> existing
> time
> package..
2000[vbcol=seagreen]
other
> more
>
DTS job is not copying all the data but still the Job History shows Successful?
Hello everybody.
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin MoustgaardJob Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > Job Shows Successfull of the Job ;
> >
> > not the each data copy process..
> >
> > Check ur DTS pack. is it well build. i mean the work flow of the
package..
> ?
> >
> > What happens when a step in DTS raise Error , u handle this ?
> >
> >
> > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > Hello everybody.
> > >
> > > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> > SP3a.
> > >
> > > On each SQL server there is a database that is copied to all the other
> > > servers every night using a DTS scheduled job.
> > >
> > > The database sizes are ranging from 50 Mb to 520 Mb.
> > >
> > > Now the problem is that sometimes the job fails but it still reports
> > > Successful in the Job History. The typical problem is that one ore
more
> of
> > > the tables on one of the destination server are empty. The table is
> there
> > > but none of the records are there. The rest of the tables and their
> > records
> > > are fine.
> > >
> > > Can anybody give me some hints as to how I can get a more detailed
> logging
> > > of the process so we can investigate what is going wrong?
> > >
> > > Martin Moustgaard
> > >
> > >
> >
> >
>|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> > The DTS is actually very simple.
> >
> > I only have a Copy SQL Server Objects Task in the package that is set up
> to
> > create destination objects (drop destination object first, include all
> > dependent objects, include extended properties), copy data (replace
> existing
> > data), use collation, copy all objects. Under options I've selected
> > everything except the 3 selections under secirity options.
> >
> > The only error handeling I have is that the DTS jopb is set to retry 2
> time
> > with 30 minutes interval if it fails.
> >
> > Martin Moustgaard
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> > news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > > Job Shows Successfull of the Job ;
> > >
> > > not the each data copy process..
> > >
> > > Check ur DTS pack. is it well build. i mean the work flow of the
> package..
> > ?
> > >
> > > What happens when a step in DTS raise Error , u handle this ?
> > >
> > >
> > > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > > Hello everybody.
> > > >
> > > > I've got 4 servers running Windows 2003 Standard edition with SQL
2000
> > > SP3a.
> > > >
> > > > On each SQL server there is a database that is copied to all the
other
> > > > servers every night using a DTS scheduled job.
> > > >
> > > > The database sizes are ranging from 50 Mb to 520 Mb.
> > > >
> > > > Now the problem is that sometimes the job fails but it still reports
> > > > Successful in the Job History. The typical problem is that one ore
> more
> > of
> > > > the tables on one of the destination server are empty. The table is
> > there
> > > > but none of the records are there. The rest of the tables and their
> > > records
> > > > are fine.
> > > >
> > > > Can anybody give me some hints as to how I can get a more detailed
> > logging
> > > > of the process so we can investigate what is going wrong?
> > > >
> > > > Martin Moustgaard
> > > >
> > > >
> > >
> > >
> >
> >
>
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin MoustgaardJob Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > Job Shows Successfull of the Job ;
> >
> > not the each data copy process..
> >
> > Check ur DTS pack. is it well build. i mean the work flow of the
package..
> ?
> >
> > What happens when a step in DTS raise Error , u handle this ?
> >
> >
> > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > Hello everybody.
> > >
> > > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> > SP3a.
> > >
> > > On each SQL server there is a database that is copied to all the other
> > > servers every night using a DTS scheduled job.
> > >
> > > The database sizes are ranging from 50 Mb to 520 Mb.
> > >
> > > Now the problem is that sometimes the job fails but it still reports
> > > Successful in the Job History. The typical problem is that one ore
more
> of
> > > the tables on one of the destination server are empty. The table is
> there
> > > but none of the records are there. The rest of the tables and their
> > records
> > > are fine.
> > >
> > > Can anybody give me some hints as to how I can get a more detailed
> logging
> > > of the process so we can investigate what is going wrong?
> > >
> > > Martin Moustgaard
> > >
> > >
> >
> >
>|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> > The DTS is actually very simple.
> >
> > I only have a Copy SQL Server Objects Task in the package that is set up
> to
> > create destination objects (drop destination object first, include all
> > dependent objects, include extended properties), copy data (replace
> existing
> > data), use collation, copy all objects. Under options I've selected
> > everything except the 3 selections under secirity options.
> >
> > The only error handeling I have is that the DTS jopb is set to retry 2
> time
> > with 30 minutes interval if it fails.
> >
> > Martin Moustgaard
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> > news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > > Job Shows Successfull of the Job ;
> > >
> > > not the each data copy process..
> > >
> > > Check ur DTS pack. is it well build. i mean the work flow of the
> package..
> > ?
> > >
> > > What happens when a step in DTS raise Error , u handle this ?
> > >
> > >
> > > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > > Hello everybody.
> > > >
> > > > I've got 4 servers running Windows 2003 Standard edition with SQL
2000
> > > SP3a.
> > > >
> > > > On each SQL server there is a database that is copied to all the
other
> > > > servers every night using a DTS scheduled job.
> > > >
> > > > The database sizes are ranging from 50 Mb to 520 Mb.
> > > >
> > > > Now the problem is that sometimes the job fails but it still reports
> > > > Successful in the Job History. The typical problem is that one ore
> more
> > of
> > > > the tables on one of the destination server are empty. The table is
> > there
> > > > but none of the records are there. The rest of the tables and their
> > > records
> > > > are fine.
> > > >
> > > > Can anybody give me some hints as to how I can get a more detailed
> > logging
> > > > of the process so we can investigate what is going wrong?
> > > >
> > > > Martin Moustgaard
> > > >
> > > >
> > >
> > >
> >
> >
>
DTS job is not copying all the data but still the Job History shows Successful?
Hello everybody.
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin MoustgaardJob Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging[vbcol=seagreen]
>|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging[vbcol=seagreen]
>|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
package..[vbcol=seagreen]
> ?
more[vbcol=seagreen]
> of
> there
> logging
>|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> to
> existing
> time
> package..
2000[vbcol=seagreen]
other[vbcol=seagreen]
> more
>
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin MoustgaardJob Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging[vbcol=seagreen]
>|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> SP3a.
of[vbcol=seagreen]
there[vbcol=seagreen]
> records
logging[vbcol=seagreen]
>|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
package..[vbcol=seagreen]
> ?
more[vbcol=seagreen]
> of
> there
> logging
>|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> to
> existing
> time
> package..
2000[vbcol=seagreen]
other[vbcol=seagreen]
> more
>
Friday, February 17, 2012
DTS Import/Export Wizard database copy problem
Hi
I'm experiencing problems copying a SQL 2000 database from my host to a local SQL Server.
I choose the Data Source OK, using SQL Server Authentication and the username and password used to setup my hosted database.
Then I specify the destination, my local server. I'm using my sa account and password and creating a <new> database (doesn't matter if I use the same db name as the source or a different one, I'm still getting an error).
I then choose "Copy objects and data between SQL Server databases", then take all defaults on the "Select Objects to Copy screen", i.e. create destination objects drop objects first and include dependent objects); creat data (replacing existing data); Copy all objects and use default options.
The copy appears to be creating a script OK (runs up to 100%), then looks to start copying objects. It gets to approx 20% and reports that it "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". When I click on the Executing Package entry it tells me:
[Microsoft][ODBC SQL Server Driver][SQL Server]The login already has an account under a different user name.
I can't seem to find any reference to this error in my books or on Google (usually a great source of info). Can't help feeling I'm doing something stupid. Would be very, very greatful if someone out there could give me a pointer in the right direction.
Thanks,
Keith.On the "Select Objects to Copy screen" I would uncheck the "Use default options" box at the bottom, click "Options..." and uncheck "Copy database users and database roles". This should take care of the problem.
Terri
Subscribe to:
Posts (Atom)