SQL 2000 - Connection Error with DTS Packages

Up until last night, everything with our production SQL Server was running
just fine, but a few jobs failed and now I have a problem I can't figure
out....

Actually, there were three things that went South last night.... don't know
if any of them relate to my problem, but I'll just state them for the
record.

1) Database Backup job (created using the Maintenance Plan wizard) failed -
event viewer gives the error:
"18210 :
BackupMedium::ReportIoError: write failure on backup device
'<BackupFileName>'. Operating system error 2(The system cannot find the file
specified.)."

The above job backs up all User Databases and the above happened about
halfway through the list of DB's, so each following DB got the same error in
the event viewer.

2) 5 Scheduled Jobs that call DTS packages failed. The times were 3, 4,
4:15, 5, and 5:30am this morning. The first four failed because I don't
think it liked me putting a "-" in the name of the DTS Package as I got the
error:

"Executed as user: SQLPRO001\SYSTEM. ...Tables The parameter is incorrect.
DTSRun: Invalid command options ...<the rest cut for brevity>...."

The last job failed because of the third thing....

3) The TempDB log file filled up completely. This happened at a couple
minutes after 5am, and the event viewer had:
"Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space."

It was the full tempdb log file that first brought this whole thing to my
attention as when I tried to refresh the Jobs screen in EM, it gave me that
error message. I did the back it suggested by running "BACKUP LOG tempdb
WITH TRUNCATE_ONLY" in QA. Then when I refreshed the Job screen, I saw all
the red X's. I was able to, by hand, backup all the databases, so I am not
sure what the deal is there. Then I saw that four of the failed jobs all
had the "-" in their name, where the fifth failed due to the tempdb issue. I
also saw that other jobs had run, prior to the tempdb issue, that did not
contain the "-" so that's why I am thinking it is an issue there.

So I went to go rename (re-save with a new name actually) the packages but I
now cannot open any DTS package. When I double click a package, there is a
long pause and then:

Package Error
Error Source : Microsoft OLE DB Provider for SQL Server
Error Source : [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or access denied.

When I click the "OK" button, then I get a second error message:

DTS Designer Error
The selected package cannot be opened. The DTS Designer has been closed.

I thought maybe they all had been corrupted somehow, and was glad I had
previously saved them off to disk as .dts files. So, I do an "Open
Package...", the thing comes right up, but when I go to save the package on
the server, I get the first error message above. I check the connection
information I am giving -- Server set to "(local)" and using "Windows
Authentication".... first error above. I try switching to "SQL Server
Authentication" with it still pointed to "(local)" and give it an admin
account info.... first error above. Switching the server from "(local)" to
the name of the machine.... now it works and saves the DTS to the server.
However, if I close the designer window, and try to open up that same
package right away, I get the two error messages above.

I opened the .dts file as described above, reset all the DB Connections to
use the server name instead of "(local)", save the package on the server
without any "-" or spaces, updated the Job with the new DTS names, and ran
it. This time it didn't error immediately, but when it got to the step that
connects to the SQL Server it failed (about 19 seconds into it) giving the
error:

"Executed as user: SQLPRO001\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed."

I know DTS packages are run on the machine that is opening/calling them, but
I am remoted in to the SQL Server, doing nothing different than I did
yesterday, the day before, or anytime in the past. I have tried stopping
and restarting SQL Server, but still having the issues. I have not tried
rebooting the machine, but as this is our Production SQL Server, I was
hoping to avoid the dozen or so minute down-time. I can if I have to, but
is there something else I can do to fix this situation? Does anyone know
what is the problem? How to fix it? What may be the possible cause of it?
Anything? I really need to get this resolved as quickly as possible.

Thanks for any help you can offer.

-- Andrew


.

Loading