Re: SQL 2000 - Connection Error with DTS Packages

I have found a workaround/solution to the problem...

I was accessing the SQL Server in EM via the server registered as
"(local)" - default registration if memory serves correct, when you
initially install it on the box... been a while so I may be incorrect.
Anyhow, I set up an alias called "Production" using the Client Network
Utility and registered that alias in ME. Now, for some reason, I can open,
run, save, etc. all my DTS packages.

Can anyone explain why I can't use the default "(local)" registration? What
would have caused that to start erroring? Any ideas?

-- Andrew



"Andrew" <AndrewR2k1@xxxxxxxxxxx> wrote in message
news:ePYLV3J6GHA.4552@xxxxxxxxxxxxxxxxxxxxxxx
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...
rror: -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




.