Unable to run a job in SQL server 2000 service pack 4

  • Article

Problem Description in our scenario:

============================

We have a default instance of SQL Server 2000 Service pack 4. When we try to run a job, the job doesn’t run. We don’t see any failure messages either. When we click the job properties and then click on “Job History” it gives a message stating "No History information"

In the Enterprise Manager, when we click on Jobs we see the Status Column as “Not Running”; Last run Status as “Unknown”; Next Run Date as “Date and time not available” .At the same time, few other jobs on the same instance are running successfully.

Error Message :

===========

Nothing specific. We just see that the job doesn’t run.

In the Enterprise Manager, when we click on Jobs we see the Status Column as “Not Running”; Last run Status as “Unknown”; Next Run Date as “Date and time not available” .

Environment :

===========

SQL Server 2000 Standard Edition SP4(8.00.2039)

Windows 2003 Standard R2, sp2,x64 bit 3790

No Cluster configuration.

No Replication Configuration.

Note: 64 bit box with SQL Server 2000 32 bit.

Cause of the issue in our scenario:

==========================

We checked the version of XPSTAR.DLL from the following location: [C:\WINDOWS\SYSTEM32\XPSTAR.DLL]

File Description: Extended Stored Procedure DLL for SQL Enterprise Manager

Product Version: (8.0:1.94)

File Version: (2000.80:194.0)

The “ XPSTAR.DLL” was still on RTM(Release to Manufacture) Version of SQL server 2000, though Service Pack 4 was applied.

Workaround in our scenario:

===========================

SO AT THIS POINT, THE ONLY FILE WHICH WE ARE CONCERNED IS :- C:\PROGRAM FILES (X86)\MICROSOFT SQL SERVER\MSSQL\BINN\XPSTAR.DLL

We did a replace of this file as shown below:

1. We stopped the SQLServer instance.

3. We extracted SQL server 2000 service pack 4 and replaced the XPStar.DLL file from the following location “C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Binn” with service pack 4 version of XPStar.DLL.

4. Started the SQLServer and SQLServer agent.

We were then able to start the job and the job ran fine and the history tables were updated.

Note: We can also reapply the service pack 4 to resolve this.

Note: Use Checksym.exe to find the version of all the SQLServer DLL’s and EXE’s . If there are more than 1 DLL or EXE’s which are below sp4 then we recommend you reapplying the service Pack.

CheckSym.exe -P SQLProcessID -R -O C:\SQLServr.txt
CheckSym.exe -P SQLProcessID -R -O C:\SQLagent.txt

Manikandan
Support Engineer, Microsoft SQL Server

Reviewed By
Karthick Krishnamurthy
Technical Lead, Microsoft SQL Server