ACC2: ODBC Specification Error Attaching to SQL Server View (131586)

SYMPTOMS

When you attach to a view in a Microsoft SQL Server database created from a table that resides in a different Microsoft SQL Server database and contains a field with a user-defined data type, you receive the following error message:

ODBC Specification Conformance Error [-7731]. This error should be reported to the ODBC driver vendor.

RESOLUTION

To avoid this error message, use one of the following methods:

Recreate the view and use the Microsoft SQL Server CONVERT() function to change the user-defined data type to any system data type in the view's database.

For example, you can use the following syntax to create a view based on the Microsoft SQL Server sample database named PUBS and convert the au_id field to a VARCHAR data type:
```
      create view test as
      select 'au_id'=convert(varchar(11), au_id), from pubs..authors
						
```
Add the user-defined data type to the database that contains the view by using the sp_addtype system stored procedure in Microsoft SQL Server.

For example, if you want to attach to a view in the MASTER database based on the Authors table from the PUBS sample database that contains a user-defined data type called ID, you can run the sp_addtype system stored procedure to add the ID data type to the MASTER database.

MORE INFORMATION

Steps to Reproduce Problem

In the Microsoft SQL Server sample database PUBS, create a view in the MASTER database using the following syntax:
```
      create view test as
      select * from pubs..authors
						
```
Start Microsoft Access.
Open the sample database NWIND.MDB.
On the File menu, click Attach. Because the Authors table in the PUBS database contains a user-defined data type called ID, you receive the following error message:
ODBC Specification Conformance Error [-7731]. This error should be reported to the ODBC driver vendor.