Gurus:
<<Running SQL2K SP4 Standard on WIN2K>>
Have a DTS which scripts Replication from 1 domain to another...creates EVERYTHING on the fly (Distributor, Dist. DB, Subscription DB, ALL Jobs, etc.)
QUESTION - Need to replicate the SAME DB AGAIN (after data modifications) but need to find a flag/tbl entry saying Replication was SUCCESSFUL.
ThanksHi All:
Anyone have an idea about this one for me?
"Something - Doo Economics ...anyone...anyone..."|||"SQL2K SP4"? I don't think that exists; they are only up to SP3a.
You are looking for an internal table that indicates a replication success? What type of replication are you using?
Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts
Tuesday, March 27, 2012
Sunday, February 26, 2012
DTS Migration Wizard error
I'm using SQL Server 2005 Enterprise x64 and when attempting to migrate a DTS from our SQL Server 2000 Standard x86 server I received the following error message:
DTS Migration Wizard Error
Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified.
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue to the next task.
From the error message, it would appear something did not install or register correctly. Any idea on what is missing and how I can fix it?
Hi there,
Did you do a full SSIS install, or did you install the Migration Wizard by itself? I believe you also need choose "Legacy Components" from the setup if you choose to install the Migration Wizard without the rest of the workbench. That might be the cause of the error you're seeing.
Thanks,
~Matt
|||I chose to install all (full) components, both server and tools, during my installation of SQL Server 2005 Evaluation Edition. I found it strange that the icon said MS Visual Studio Premier Edition -enu instead of SQL Server Business Intelligence Development Studio. I tried a repair on the MS Visual Studio Premier Edition from Add / Remove Programs but it didn't help. I then removed it using Add / Remove Programs. However, when I fire up the SQL Server 2005 Evaluation Edition setup and attempted to reinstall the tools, it said they were already installed and would not let me go any further. Is a reboot of the production server necessary for Windows 2003 Enterprise to realize I uninstalled MS Visual Studio?
Thanks for your response Matt!
|||The Visual Studio icon will only be labeled "SQL Server Business Intelligence Development Studio" under the SQL Server folder in the start menu. Your old visual studio icons won't change (they all point to the same thing).
You shouldn't have to reboot after installing SQL Server, but if you've done a repair, I'm not sure what state that puts you in. You might want to uninstall it all and start over again.
The Migration Wizard will be looking for the Microsoft.SqlServer.Exec80PackageTask assembly in the GAC - you might want to make sure it's there. This task gets installed when you select "Legacy Components" or the workbench, so doing a full install should give you all the bits you need.
|||That is the strange part. MS Visual Studio 2005 was not previously installed on this machine; it came across when I chose to install every component of the SQL Server 2005 Eval Edition installation. And the icon in the SQL Server folder didn't say BIDS, instead it was labeled MS Visual Studio 2005. Either way, I have now uninstalled MS Visual Studio 2005 and it looks like I will need to reboot my server before it recognizes the change as it will not allow me to re-install them at this point.|||I ended up uninstalling and reinstalling all of SQL Server 2005 and components. Then to address the missing Business Intellligence projects; I finallly found the answer, In MS Visual Studio (BIDS) click on Tools - Import and Export Settings - Import Selected Environment Settings - Yes, Save my current settings and then highlight "Business Intelligence Settings" and click on finish. This will add the Business Intelligence projects to BIDS.DTS Migration Wizard error
I'm using SQL Server 2005 Enterprise x64 and when attempting to migrate a DTS from our SQL Server 2000 Standard x86 server I received the following error message:
DTS Migration Wizard Error
Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of it's dependencies. The system cannot find the file specified.
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue to the next task.
From the error message, it would appear something did not install or register correctly. Any idea on what is missing and how I can fix it?
Hi there,
Did you do a full SSIS install, or did you install the Migration Wizard by itself? I believe you also need choose "Legacy Components" from the setup if you choose to install the Migration Wizard without the rest of the workbench. That might be the cause of the error you're seeing.
Thanks,
~Matt
|||I chose to install all (full) components, both server and tools, during my installation of SQL Server 2005 Evaluation Edition. I found it strange that the icon said MS Visual Studio Premier Edition -enu instead of SQL Server Business Intelligence Development Studio. I tried a repair on the MS Visual Studio Premier Edition from Add / Remove Programs but it didn't help. I then removed it using Add / Remove Programs. However, when I fire up the SQL Server 2005 Evaluation Edition setup and attempted to reinstall the tools, it said they were already installed and would not let me go any further. Is a reboot of the production server necessary for Windows 2003 Enterprise to realize I uninstalled MS Visual Studio?
Thanks for your response Matt!
|||The Visual Studio icon will only be labeled "SQL Server Business Intelligence Development Studio" under the SQL Server folder in the start menu. Your old visual studio icons won't change (they all point to the same thing).
You shouldn't have to reboot after installing SQL Server, but if you've done a repair, I'm not sure what state that puts you in. You might want to uninstall it all and start over again.
The Migration Wizard will be looking for the Microsoft.SqlServer.Exec80PackageTask assembly in the GAC - you might want to make sure it's there. This task gets installed when you select "Legacy Components" or the workbench, so doing a full install should give you all the bits you need.
|||That is the strange part. MS Visual Studio 2005 was not previously installed on this machine; it came across when I chose to install every component of the SQL Server 2005 Eval Edition installation. And the icon in the SQL Server folder didn't say BIDS, instead it was labeled MS Visual Studio 2005. Either way, I have now uninstalled MS Visual Studio 2005 and it looks like I will need to reboot my server before it recognizes the change as it will not allow me to re-install them at this point.|||I ended up uninstalling and reinstalling all of SQL Server 2005 and components. Then to address the missing Business Intellligence projects; I finallly found the answer, In MS Visual Studio (BIDS) click on Tools - Import and Export Settings - Import Selected Environment Settings - Yes, Save my current settings and then highlight "Business Intelligence Settings" and click on finish. This will add the Business Intelligence projects to BIDS.DTS load to multiple tables
I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I’m not sure if I am using the right tool for the job.I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database.Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables.There are no unique ids in the source data since it is coming from a 3rd party and the tables are not related to others.I’ve created a DTS Package and have the beginnings of several Transform Data Tasks.Each destination table has an Identity id column, which is calculated automatically.The roadblock I’ve run into is that I can’t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key.Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together.The Transform Data Task only allows me to reference one source and one destination, not multiple destinations.I hope this makes sense.Any suggestions would be appreciated.
Here’s a simple example that may help illustrate the problem.My database is much more complex.
Input table is called parcels and each row has three columns:Address, Owner, and Legal_Description.
Output database has two tables that will receive this data:Parcel table will have the Address, Owner and Parcel_ID (auto calculated).Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.
When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured.Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.
How can I do this through DTS?Thanks for any suggestions, code snippets, or references.
Due to the complexity of this task, I would suggest maybe doing this in a .NET winforms application. Set up ODBC connections to the two databases. Now write some queries in the Access database to divide the data up appropriately. Next, write insert procedures in the SQL Server database, which return an outparameter which is the id field. To get this, in the insert proc, use the @.@.IDENTITY or the SCOPE_IDENTITY calls to get the id value of the inserted row. Capture this in the .NET application, and pass this in to the insert proc in the related table. Alternatively, keep a cache of the data mappings, maybe in a temporary table or a dataset, and do the inserts as bulk inserts. Then run an update procedure which sets the foreign key based on the database mappings in the original database. So, for example, based on the Legal_Description in the legal table, update the Parcel_Id in the legal table using the owner and address values that used to share a row with the Legal_Description. Either approach should work.let me know if you need more guidance here. Some of the SQL database engine people, or the SSIS people may be able to point you to a DTS solution that can do this. Alternatively, you could create a DTS solution that inserts into one table, inserts into the other, then calls the update procedure as described above. There is a separate SSIS forum (the new DTS), you may be better posting the question there.
HTH
For more T-SQL tips, check out my blog:
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
>
Sunday, February 19, 2012
DTS in SQL 2005 Standard
I'm using SQL 2005 Standard Edition with the Management Studio Console CTP but I can't seem access DTS functions. Is DTS available in this edition of SQL?
I just got through the same issue. I upgraded from 2000 to 2005 and could no longer find DTS. It is now SSIS in 2005. I can point you in the right direction but thats it. I am still figuring it out in the new edition.
-M
|||here was my thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=349970&SiteID=1
DTS in SQL 2005
Hello,
This is kind of a stupid problem. I just installed SQL 2005 Standard and I am trying to use DTS to transform a Access DB to SQL. I am having a tough time finding where that feature is. Is it by default insatlled or is it an add-on?
Thanks!
In SQL 2005 it is no longer called DTS it is known as SSIS(SQL Server Integration Services). You can migrate DTS packages to SSIS or re-build them using the Business Intelligence Development Studio.|||Thanks!
So does SSIS transform a mdb file the same as the DTS did in 2000?
|||I have never transformed an mdb file; however, I am assuming it should work. Within the BI Development Studio tool, when you create an SSIS package there is a task that allows you to run SQL 2000 DTS packages. You might be able to get away with creating a one 'step/task' SSIS package that basically runs your SQL 2000 DTS package.
I used the migration wizard in the BI tool to 'convert' my DTS to SSIS and it worked fine; however, mine does not deal with mdb's. My package basically is a SQL statement as the source(in SQL 2005) and 'pushes' the results to a destination table in a SQL 2000 database.
P.S. My DTS was a simple task on the surface, but to migrate to SSIS and then test it as well as understand the new SSIS and BI tool and figure out how to run the package, etc. probably took me a week. Maybe I am not that good at it or I took the long road, but to move(and then understand) from DTS to SSIS is not a trivial half-hour job.
|||Thanks for the imput. He is my deal. I am new to SQL. I had a evalution version of 2000, and I literally had the MDB file converted into an SQL DB in 5 minutes with DTS. So, once I was ready to purchase and install the full SQL product, I figured that I should go with 2005. Everyone I spoke with said it was way more user friendly!! I still think it probably is, but it certainly is not as easy as the 2000 was.
I am getting stuck alot with my new project, but I am still really enjoying the hands on erxperience dealing with this new technology.
-Mac
|||You probably used the Import Wizard to do that in SQL Server Enterprise Manager 2000. Have you tried the same in SQL Server Managment Studio 2005? I just did and it took about 20 seconds.
-Jamie
|||Any luck!
Sounds like what you are trying to do is convert an MDB database to a SQL 2005 database. If this is just a one-ime deal then you can probably get this done as fast as Jamie states.
I assumed you were wanting to move data from MDB to SQL 2005 on a regularly scheduled basis. This too can be simple; however, I feel to truly understand SSIS and its power it may take more than just a one-time 20 second migration. That was my point of my previous post. Each time I 'play' with SSIS I learn something new. It si an on-going educational experience for me.
Swit
|||Thanks for the reply. I am not trying to import data into SQL on an ongoing basis. I am trying to upgrade our company from an MS Access DB and front-end to ande SQL DB with an ASP.NET website. So what I am trying to do is have SQL import all of the tables and columns and the data and create its own DB from it.
For that reason, the DTS in 2000 was great. I did it just to play with it and it instantly created to DB for me. So now instead of trying to figure out this SSIS in 205, I think I will use the DTS in 2000 and them import it into 2005.
Any feedback?
-Mac
|||Well, its really up to whats best for you now. SSIS is a much more robust application/platform than DTS was. However as this is a one time operation, your familiar with DTS, and perhaps your short on time, do what you have to do...and enjoy reading about all the great SSIS features later :)
When you get a chance you might want to look at the many webcasts there are for SSIS as a way to ramp up. http://msdn.microsoft.com/SQL/bi/integration/default.aspx
Also, you might explore an option inside Microsoft Access. The upsizing Wizard, which is specifically desinged to move your Access data to SQL. In the Access 2003 menues you would get it from here Tools>>Database Utilities>>Upsizing Wizard
Hope that helps
|||Thanks for the reply. I am in the process of using the "Upsizing" wizard to convert to the SQL. It is exactly what I was looking for.
-Mac
Subscribe to:
Posts (Atom)