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” .
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
Support Engineer, Microsoft SQL Server
Technical Lead, Microsoft SQL Server