Re: SQL 2000 - Connection Error with DTS Packages

And it finally comes down to fixing the problem in the classic Microsoft
fashion:

Reboot the box.

-- Andrew


"Andrew" <AndrewR2k1@xxxxxxxxxxx> wrote in message
news:uPfXLIL6GHA.3572@xxxxxxxxxxxxxxxxxxxxxxx
The saga continues.....

After I changed all SQL Server 'Database Connection' steps in each of my
DTS packages from "(local)" to "Production", they would run, but the
Scheduled Jobs still kept failing. Double checked all the spellings,
permissions, owners, etc. Fail, fail, fail. So, out of frustration, I
did the "Schedule Package" option when you right click a DTS package name
in ME. Manually kicked off the job it scheduled, and it ran successfully.

What I had been using to run the DTS package in the job step was:
"dtsrun /S(local) /U<username> /P<password> /N<packagename>"
This would fail .

When I used the "wizard" to schedule the package, I get:
"DTSRun
/~Z0x6979CA6C4337CB294E2580060F81D202277145396A4B6A736EB50D5BF774722B026301BC6A153202CFD05BBF8A4F09BC4F4B2330C1C7E7796F8DDD9296A28EDDC1619396DEA3828C9A613AFFA5D5E6E1870B11D30E9DB3B16B7943AE3C3B3A76BE151BEF4968B649EA2D4D2A3D86BD61509C0102380EEF17E672A135686A9A7A93D134"
This would run

But then (like a smack to the forehead) it hit me, and I changed my job
step to:
"dtsrun /SProduction /U<username> /P<password> /N<packagename>"
And this ran just fine.

So now it looks like there is nothing here to do with DTS, but rather
something has caused the reference "(local)" to no longer be
recognized/understood by the SQL Server.

Has anyone else seen/heard of this? How do you fix it? What could have
caused it in the first place?

Anyone?

-- Andrew

"Andrew" <AndrewR2k1@xxxxxxxxxxx> wrote in message
news:etrr6KK6GHA.4932@xxxxxxxxxxxxxxxxxxxxxxx
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...
or: -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








.