Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 193339 - Last Review: December 3, 2003 - Revision: 3.1
PRB: Unexpected Errors Using OLE DB Provider for SQL Server
This article was previously published under Q193339
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.
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.
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.
This behavior is by design.
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:
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.
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.
For additional information, please see the following article in the
Microsoft Knowledge Base:
137636Â
(http://kbalertz.com/Feedback.aspx?kbNumber=137636/EN-US/
)
INF: Relationship of the ODBC Driver to INSTCAT.SQL
APPLIES TO
- Microsoft ODBC Driver for Microsoft SQL Server 3.6
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft Visual Basic Enterprise Edition for Windows 6.0
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
| kbdatabase kbprb KB193339 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate