PRB: Unexpected Errors Using OLE DB Provider for SQL Server

View products that this article applies to.

This article was previously published under Q193339

SYMPTOMS

Visual Studio 6.0 and the Data Access 2.0 Software Development Kit (SDK) both install a version of the SQL Server ODBC Driver (and OLE DB Provider) that is more current than the file found in the latest SQL Server Service Pack. Additionally, neither setup invokes the Instcat.sql script that is needed to finish configuration of the SQL Server driver/provider. This creates unexpected errors in various products at different times.

For example, using the Visual Basic 6.0 Data Environment for connecting to SQL Server with the OLE DB Provider may cause an error. When you drag a table from the data view to the design window of a command, the following error occurs:

Query Designer encountered a MS Design Tools error:
Unspecified error.

When you click OK, you see another error:

Method '~' of object '~' failed"

Click OK again and repeat the operation. This time the operation is successful.

Back to the top

CAUSE

When a new version of the SQL Server ODBC Driver is installed by a SQL Server Service Pack (or by SQL Server), the Instcat.sql executes and helps configure the driver for operation. The SQL Server OLE DB Provider has the same dependency upon the execution of the Instcat.sql as the SQL Server ODBC Driver. Visual Basic 6.0, when installing either driver or provider, does not execute the Instcat.sql file, which then causes the preceding errors.

The latest SQL Server Service Pack is Service Pack 4. However, the version of the SQL Server ODBC Driver (3.60.0319) shipped with Visual Studio 6.0 and the Data Access Software Development Kit (SDK) 2.0 is newer than what is found in SQL Server 6.5 Service Pack 4.

Back to the top

RESOLUTION

To work around this problem, run the Instcat.sql file from your SQL Server. Instcat.sql is installed but not executed by Visual Studio 6.0 and the Data Access SDK 2.0. You may have more than one Instcat.sql file on your computer in different sub-directories. Make sure that you execute the file dated 6/5/98 or later.

Back to the top

STATUS

This behavior is by design.

Back to the top

MORE INFORMATION

If you are uncertain whether the latest Instcat.sql has been executed with your SQL Server, you can execute the sp_server_info system stored procedure with one parameter = 500. From ISQL run the following line in a new query:

sp_server_info 500

If the latest Instcat.sql dated 6/5/98 has been run, you should see the following information display:

   attribute_id     attribute_name     attribute_value
   ---------------------------------------------------

   500              SYS_SPROC_VERSION  7.00.481

If 6.50.193 is listed in the attribute_value column, the latest file has not been executed.

Back to the top

Steps to Reproduce Behavior

Start new Standard .exe project in Visual Basic 6.0 and add a DataEnvironment.
Add a connection and select the Microsoft OLE DB Provider for SQL Server.
Select or enter the appropriate values needed to make a connection such as server name, user name, password and database name. Select the Pubs database to test your connection.
Add a Command Object and click the General tab. Select SQL statement and then click the SQL Builder... button.
Expand the Tables node in the Data View window.
Drag one of the tables into the Command design window. The errors occur.
Repeat step 6, and this time the errors do not occur.
Stop your program.
Start your Microsoft SQL Enterprise Manager and run the Instcat.sql file from ISQL. During the execution of this file, you may see different error messages display. You can ignore these messages.
Run RECONFIGURE in a new query from ISQL. This is necessary to update your server.
Now, repeat steps 1 - 6. The errors do not occur.

Back to the top