Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gbatta
Starting Member
26 Posts |
 Posted - 2006-05-23 : 13:47:07
|
| Hi,I have been running the same DTS package for over a year. It copies data from all of my tables in my live database to my dev. Like I said, this have been working perfectly fine for over a year, and I just went to run it today, and on one of the tables I get an error stating "The number of failing rows exceeds the mazimum specified. (Microsoft OLE DB Provider for SQL Server (80004005): Unspecified error)".If I run each DTS package individually, they run fine, but when I execute them all, I get this error. No changes have been made to this database's structure or data types and it isn't a matter of permissions or security.Does anyone have any ideas what may be causing this all of the sudden?Many thanks in advance...Ginger |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-23 : 13:58:05
|
| I don't have a solution to your problem, but I do have an alternative.Rather than using DTS to refresh your development database with production, you should instead use BACKUP/RESTORE. Just grab the nightly production backup file and copy it over to development. Then restore this file onto the development server. The advantage to this method is that it is an exact replica of production and it'll also be a lot faster.Tara Kizeraka tduggan |
 |
|
|
gbatta
Starting Member
26 Posts |
Posted - 2006-05-23 : 14:04:52
|
| That is good option; however, the person who had the job before me, set up our servers with SQL installed locally on each. So my live server has my live version of the database and likewise with the dev installed. Can you back up from an external source, if so, how do you connect to that location? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-23 : 14:10:55
|
| It doesn't matter that they are on separate servers. In fact, you shouldn't have development on the same server as production anyway. The best way to do this is to grab the production backup file. There should be a job that backs it up nightly. So you just need to xcopy the latest file to your development server. Then you just perform a local restore.You could do a restore without the xcopy. You can point to a remote backup location if you specify a UNC path in the FROM DISK option of the RESTORE command. But I would recommend the xcopy route so that you don't burden the production server with the remote restore.Tara Kizeraka tduggan |
|
|
|
gbatta
Starting Member
26 Posts |
Posted - 2006-05-23 : 15:02:25
|
| Thank you for your suggestions; however, I would like to fix the DTS problem. After it is fixed, I may look into one of your options.Does anyone have any other ideas on what may have caused/how to fix this problem? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-23 : 15:13:37
|
| To fix the problem, you'll first need to figure out the error (which DTS isn't giving you). To find the error, run SQL Profiler on the development database server. Be sure to include Errors and Warnings:Exception event class in the trace. By default, the package will fail if it encounters any errors. You can change this in the transform data task options tab. But that'll mean it'll skip the error, which means you'll have missing data. Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
DTS package error