PRB: SQL Server Agent Job Fails with Error Message "...Driver's SQLSetConnectAttr failed..." (315005)

SYMPTOMS

A SQL Server Agent job may fail with the following message in the job history:

Unable to start execution of step 1 (reason: The ?? subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

If the preceding error message occurs, look in the Sqlagent.out file for this error message:

<WWERRORMESSAGE>
5/10/00 4:17:12 PM - ! [165] ODBC Error: 0, Driver's SQLSetConnectAttr failed[SQLSTATE IM006]
5/10/00 4:17:12 PM - + [164] ODBC Message: 0, Packet size change not honored by server, server size used
</WWERRORMESSAGE>

RESOLUTION

To resolve the problem, make sure that the SQL Server ODBC driver is version 3.70.000, or later.

To determine the version, look in the Sqlagent.out file. For example:

5/10/00 4:17:12 PM - ? [102] SQL Server ODBC driver version 2.65.240

Search the local disks of the SQL Server computer for all occurrences of the Sqlsrv32.dll file, then:

If the Sqlsrv32.dll file in the Winnt\System32 folder is earlier than 3.70.000, you must update it to a newer version. Install the Microsoft Data Access Components (MDAC) version that is provided with SQL Server 7.0 by navigating to the \x86\Other folder on your SQL Server 7.0 CD, and then execute the Mdac_type.exe file. For example, type: " F:\x86\OTHER\mdac_typ.exe " (without the quotation marks)

-or-
If the Sqlsrv32.dll file in the Winnt\System32 folder is version 3.70.000, or later, check the Driver value in the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\Odbcinst.ini\SQL Server

The value may point to a DLL other than the Sqlsrv32.dll file in the WINNT\System32 folder. For example, the following is incorrect:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\Odbcinst.ini\SQL Server\Driver\D:\Apps\Seagatecrystalreports\Sqlsrv32.dll

You must change the registry value to point to the SQlsrv32.dll file in your Winnt\System32 folder. For example, if your Sqlsrv32.dll file is located in C:\Winnt\System32, change the value to:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\Odbcinst.ini\SQL Server\Driver\C:\Winnt\System32\Sqlsrv32.dll

If you also have the SETUP value in the SQL Server key, make the same changes you made previously to update the path to point to the correct Sqlsrv32.dll file:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\Odbcinst.ini\SQL Server\Setup\C:\Winnt\System32\Sqlsrv32.dll

REFERENCES

For more information about MDAC, or to obtain a later version of MDAC, see the following Web address at:

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28001860

For more information about DLL files, see the DLL Help database located at:

http://support.microsoft.com/servicedesks/fileversion/dllinfo.asp

SQL Server 2000 Books Online; topic" "SQL Server Agent Properties"