FIX: Error messages after SQL Server has been running for some time on Windows Server 2003 or on Windows 2000: "Failed to load FileName.dll" and "Attempts to load any type of dll might fail within the SQL Server process"

Method 1

1. Use the sp_OACreate stored procedure to determine the DLLs that SQL Server loads into the SQL Server process. To do this, follow these steps:
   1. Open SQL Profiler, and then connect to the instance of SQL Server.
   2. In the Trace Properties dialog box, specify the name of the trace, specify the location where SQL Profiler saves the trace, and then click Run.
   3. After you run SQL Profiler for some time, analyze the trace to locate the occurrences of the sp_OACreate stored procedure.
   4. Notice the first parameter of each occurrence of the sp_OACreate stored procedure.
      
      Note The first parameter indicates the programmatic identifier (ProgID) of the OLE object or the class identifier (CLSID) of the OLE object.
   5. Determine the DLL that contains this OLE object.
   Note You can use SQL Profiler to monitor the instance of SQL Server when you start the SQL Server service.
2. Make sure that you have enabled the OLE Automation procedures option.
   For more information about how to determine whether the OLE Automation procedures option is enabled and how to enable the OLE Automation procedures option, visit the following Microsoft Develop Network (MSDN) website:
   http://msdn2.microsoft.com/en-us/library/ms191188.aspx
3. In SQL Server Management Studio of SQL Server 2008 R2, of SQL Server 2008, or of SQL Server 2005 or in SQL Query Analyzer of SQL Server 2000, run the following statements.
   
   Note  As an example, the following code shows how to load the Wbemdisp.dll file.

   DROP PROC sp_Load_Dll
   GO
   CREATE PROC sp_Load_Dll
   AS
   declare @WmiServiceLocator int, @hr int
   exec @hr = sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator output 
   if @hr = 0
    raiserror ( 'wbemdisp.dll loaded into sql address space by startup procedure sp_Load_Dll, Object Info: %d' , 10 , 1 , @WmiServiceLocator) --WITH LOG
   WHILE 1=1
    WAITFOR DELAY '01:00:00'
   GO
   EXEC sp_procoption 'sp_Load_Dll' , 'startup' , 'true' 
   GO
   

   The statements create a stored procedure. The statements also enable the stored procedure to run when the SQL Server service starts. This stored procedure performs the following operations:
   • Loads the Wbemdisp.dll file.
   • Keeps the Wbemdisp.dll file in memory because the statements do not exit.
   As long as the Wbemdisp.dll file is in memory, other statements do not load the Wbemdisp.dll file again. Therefore, SQL Server does not call the TlsAlloc function and the TlsFree function for the Wbemdisp.dll file.
   
   Note You must update the code to load the DLLs that you determined in step 1.
4. Add the statements to the sp_Load_Dll stored procedure in step 3. These statements load the DLLs that you determined in step 1.
5. Restart the SQL Server service.
6. Verify that SQL Server loads the Wbemdisp.dll file in memory. To do this, follow these steps:
   1. Open the SQL Server error log file.
   2. Search for the following message:
      wbemdisp.dll loaded into sql address space by startup procedure sp_Load_Dll
      Note This message indicates that SQL Server has loaded the Wbemdisp.dll file.
   3. At a command prompt, run the following command:

      tasklist /m

   4. In the result that you obtain, locate the Sqlservr.exe process, and then verify that the Wbemdisp.dll file is listed.
      
      Note If the Wbemdisp.dll file is listed, SQL Server has loaded the Wbemdisp.dll file.

Method 2

1. Use the sp_OACreate stored procedure to determine the DLLs that SQL Server loads into the SQL Server process. To do this, follow these steps:
   1. Open SQL Profiler, and then connect to the instance of SQL Server.
   2. In the Trace Properties dialog box, specify the name of the trace, specify the location where SQL Profiler saves the trace, and then click Run.
   3. After you run SQL Profiler for some time, analyze the trace to locate the occurrences of the sp_OACreate stored procedure.
   4. Notice the first parameter of each occurrence of the sp_OACreate stored procedure.
      
      Note The first parameter indicates the programmatic identifier (ProgID) of the OLE object or the class identifier (CLSID) of the OLE object.
   5. Determine the DLL that contains this OLE object.
   Note You can use SQL Server Profiler to monitor the instance of SQL Server when you start the SQL Server service.
2. Create an extended stored procedure. The extended stored procedure contains a function that loads the DLLs that you determined in step 1.
3. Add the extended stored procedure to SQL Server.
4. In SQL Server, use the sp_addextendedproc system stored procedure to register the function in the extended stored procedure.
5. Use the sp_procoption stored procedure to enable the extended stored procedure to automatically start when the SQL Server service starts.
6. Restart the SQL Server service.