| Author |
Topic |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2004-07-11 : 19:27:25
|
| I'm having continuing problems with the failure of a certain DTS package. The package is run via DTSRUN in a SQL Server Agent Job at 6am every morning. It FTP's a file from another server, uploads it ot the database and performs a bunch of validation and manipulation tasks.The FTP task itself runs OK, but the package seems to always fail at the second task. This task simply inserts a line into an import status table. The error I'm getting is:Cannot Open database requested in login 'ChestNut'. Login Fails. (Error code 80004005).The DTS Package contains a Connection object to the database. The connection uses SQL Server autho, and is logging in as 'sa' (it was originally using integrated security, but I changed it to SQL autho out of desperation...). The job that executes the package is run under the domain admin account. The package runs ok when executed manually from a workstation. It fails when we manually run the related job.This particular DTS package has never been particularly reliable. We have had it failing regularly when things such as the domain admin password changes. Can anyone help? I'm at my wit's end....Tim |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-07-12 : 01:59:22
|
| Can the server that is running the job resolve the host name of the connection object ?Damian |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-12 : 02:17:31
|
| I'm assuming it can, judging by the fact that the package can be run manually without problems |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-07-12 : 02:32:42
|
| But you said you ran the package from a workstation.When you run it that way, it's actually running at the workstation, when you schedule it, it's running on the server.So if you were using a hostname that the server couldn't resolve, but the workstation could, you would see these symptoms.Damian |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-12 : 02:40:19
|
| Ah - good point. Didn't see what you meant originally.Will try and run it via the server and see what happens...I don't hold out much hope, though. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-12 : 18:59:37
|
| Yep - It was the server name not resolving. I opened up the SQL Connection object, changed the Server Name to {local} then back to the server name, and it worked. Something I never would have considered. Thanks Damian. Good bit of lateral thinking..... |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-14 : 20:25:07
|
| The problem has surfaced again.The DTS package in question ran once after I made the change, then has failed for the past 2 days. Also, a VB app that runs on the database server threw the same errors yesterday. The app uses a DSN connection to the database.It got me thinking - is there a way that the security settings in SQL can be corrupted in a similar way that NT accounts can? Or can the server get confused as to what it's name is?Given the problem is intermittent I thought this might be the case, but I've no idea where to start to fix it.Any ideas?TIA,Tim |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-14 : 23:56:17
|
| Is this a backup server that you're restoring the databases onto periodically, then trying to connect to it like this?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-15 : 00:01:40
|
| No - it's a production server. We have a DTS pckg that runs (or is meant to..) daily. |
|
|
|
|
DTS Package failure