Job Error. Need help ASAP.

Question

Job Error. Need help ASAP.

Dane_Jurkovic

SSC Enthusiast

Points: 142
More actions
February 5, 2003 at 7:45 am

#357700

Does anyone know what could be my problem here?
Let me explain….
I have two servers, one in a DMZ and the other on our local network. I have a DTS package running every night form the server on the DMZ. I can verify this by looking in the jobs. Here is the kicker. I can run the DTS package from Enterprise Manager (EM) and the package works just fine. But, every night the “job” fails (job error below). The job is running under the SA account so it has the permissions but fails. I am thinking that it could be something with the firewall between the DMZ and the local network. Some port that is being blocked or something. Does anyone know if “jobs” are using a special port or something? The reason why I am going down this path is that using EM and sitting on the local network I must be acting as some kind of “middle” man and that is why the DTS package is working when I fire it off. But, when the server in the DMZ is doing all the work there is no middle man helping it out. I.e. I can see the server in the DMZ and it can see the server on the local network through me. I know, sounds dumb. But, that is all I can think the problem could be some port being blocked on the firewall. Any help would be so wonderful.
*******************
Start Of Job Error
*******************
DTSRun: Loading...
Error: -2147008507 (80074005);
Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 713
Error: -2147467259 (80004005);
Provider Error: 1326 (52E)
Error string: Client unable to establish connection
Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0
Error: -2147467259 (80004005);
Provider Error: 1326 (52E)
Error string: [DBNMPNTW]ConnectionOpen (CreateFile()).
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0. Process Exit Code 1. The step failed.
*****************
End Of Job Error
*****************
Thanks,
Dane-

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Firstly check out the following article, it'll give you an idea on working out what security context the DTS package is executing under.

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

Secondly, the text of the error message usually indicates that the server wasn't located. Try running ODBCPing to check connectivity,

http://support.microsoft.com/default.aspx?scid=kb;en-us;138541[

Thanks

Phill Carter

Edited by - phillcart on 02/05/2003 3:15:27 PM

--------------------
Colt 45 - the original point and click interface

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

When you run the package in em, it runs as you. On the server it runs as SQLAgent. Log into the server as the SQLAgent account and run the package. Most likely it will fail. The ref's above should help.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Sounds like your server is trying to connect using Named Pipes. Use Client Network Utility on the server to create alias to other server and make sure network library is set to TCP/IP.

Far away is close at hand in the images of elsewhere.
Anon.