July 23, 2004 at 5:22 am
This problem was initially identified because SQL Agent was running jobs successfully but failing to write completion status to sysjobhistory in msdb.
The box is a stand-alone W2K3 running SQL2K sp2 (Enterprise)
SQL Jobs all run OK and complete successfully. I know this because most of the jobs in question have additional custom reporting, error trapping and email generation - all of which works. However, the writing of the job completion data within msdb appears to fail so, for example, the last run time as viewed in EM is wrong (although matches what we actually have in sysjobhistory)
The server in question is a DR box that, to reduce costs, will also be running a couple of .NET components although they've not been fired up yet. As part of the pre-release work yesterday, our Ops guys added a Client Network Utility entry for the server using the server name and correct port, which is mapped to the IP in the hosts file. Prior to this, in setting up the databases etc, all connections were made using "(local)". It was only after the server entry was added to the client network utility that the connection problems started.
Here are some other facts that may help:
This is the default (& only) instance of SQL and has never been re-named.
The server name used in the hosts file and the client network entry match.
TCP/IP is the only library enabled in server network utility although on a non-default port
The port specified in the client network entry matches that used by the server
If we remove the client network utility entry SQL Agent is able to successfully log job completion to msdb.
I've also identified that with client network entry in place, I can connect to the server in QA using the names (local) and "." but if I specify the server name (as listed in client network) I get a "server does not exist" failure.
SQL Server and SQL Agent Services both run under a local account with local administrator privileges.
Connections into SQL on this box across the network also fail with "access denied or server does not exist"
Incidentally, we have another SQL 2K box (this time Developer with sp3) on W2K3 in our test environment that is set up the same (hosts file entry and client network entry all in place) but both SQL Agent job reporting and QA connections (via netlib) all work OK. This also has a couple (different) .NET components running on it.
I understand that if a "(local)" connection is used, SQL bypasses the network communication and does everything "in-process" so if we try to connect to SQL using the server name specified in client network utility we are forcing the call out via the TCP/IP network library. Other connections into the server such as ping and ftp are unaffected. This seems to some kind of SQL network library issue but I am at a loss as to how I should investigate this further. Can anyone help?
Many Thanks in advance,
. . Greg
Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa
July 23, 2004 at 6:43 am
have you tried diabling TCP/IP and using another protocol to test ?
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply