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 |
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
 Posted - 2002-10-10 : 14:18:56
|
| First, let me explain what we need to do.SQL Server 2000 (Windows 2000 OS) – server A has two DTS packages to import data from a foxpro database. It also has another package to export that data to server B (same SQL Server 2000/Windows 2000). All tree packages were created at server A and successfully run at server A.We tried running the packages from:a) server A enterprise managerb) server A DTS designerc) dtsrun utility from command prompt d) scheduled job e) a remote workstationEverything works reporting success. After having run the packages, we always query the database on server B to check if the changes were actually made and we always see the change.Here is the problem we're having:Yesterday we ran the packages five times, all successful. The last time the job ran at night (~ 11 p.m.). When we came this morning, all the changes from yesterday were gone and the data went back to what it was on Monday. Does anyone have any idea of what could be happening on server B and where to even start looking?helena |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-10 : 14:25:27
|
| Hmmm, thats just screwy. Things to look at : -Any errors at all come back from the DTS packages?-Were the changes made on server A but not server B?-What all else affects the DB on a nightly schedual? Any other replication or DTS packages?-What do the log files say?I'm really not even sure where to start on this. If you ran the DTS package again manually right now would it go to what it should look like?-----------------------SQL isn't just a hobby, It's an addiction |
 |
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2002-10-10 : 15:08:29
|
| 1. No errors - everything is OK2. The changes are made on Server B (temporarely).3. There are no other replications.4. I just ran the packages - everything looks good (as usual).5. What shall I look for in log files?helena |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 15:10:40
|
| Could someone have run a RESTORE operation? |
|
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2002-10-10 : 16:06:50
|
| Someone as a person or a process?In case it's a person, it's not possible. If it happened during backup then why only those two entities, the rest of the database is OK.helena |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-10 : 16:18:41
|
| How about this: if the import from Foxpro necessitates a DELETE/INSERT operation, or universal UPDATE to ALL rows in the SQL Server table, what if the Foxpro data was somehow restored or set back to it's Monday status? In other words, existing SQL data is not preserved during the DTS job. In that case the SQL tables would've been wiped and inserted with old Foxpro data.That's the best guess I've got without more detail on what the DTS jobs do. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-10 : 17:29:33
|
| Definately agreeing with Rob here. I'm thinking that it was probably a restore of either the sql database or the Foxpro one. I don't think it's the DTS package that did it. Is it possible that theres 2 versions of the foxpro DB and you schedualed it against one that had a frozen copy of the foxpro from monday? I think this ones probably something like that then an error in your code-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
|
|
|
|
|
DTS changes are rolledback