Microsoft KB Archive/256040

From BetaArchive Wiki

Article ID: 256040

Article Last Modified on 10/31/2003



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft OLE DB Provider for Jet 4.0



This article was previously published under Q256040

SYMPTOMS

You may receive the following error message when you run a Data Transformation Services (DTS) Package if both the Source and Designation tables have the exact same structure:

-2147213275 Microsoft Data Transformation Services (DTS) Data Pump DTSTransformCopy: ValidateSchema failed; see Extended Error information.

CAUSE

The OLE DB providers may differ in the way they set the MAYBENULL flag of the DBCOLUMNFLAG bitmask that DTS uses to determine if the columns are exactly alike.

RESOLUTION

To work around the error message, set the DTSTransformFlags DTS Package property to something less stringent. For example, set DTSTransformFlags to DTSTransformFlag_Default.

STATUS

This behavior is by design.

MORE INFORMATION

The DTSTransformFlags property is used to ensure data integrity for the Designation table. DTSTransformFlags can be set at various levels of strictness. The most restrictive is the constant setting of DTSTransformFlag_RequireExactType. DTSTransformFlag_RequireExactType requires the Source and Designation tables to have the same column type, length (including fixed versus variable length), scale, precision, sign and nullablity. If anyone of these values differs between the Source and the Designation tables, the error message shown in the "Symptoms" section occurs.

DTS determines if the column metadata is the same between the Source and Designation tables by calling the IColumnsInfo::GetColumnInfo method for the OLE DB providers. DTS then compares the DBCOLUMNFLAGS portion of the DBCOLUMNINFO structure that is populated by the method call. DBCOLUMNFLAGS is a bitmask that describes the column's datatype, length, nullabilty and other characteristics of the column.

DTS looks at the ISNULLABE and MAYBENULLABLE elements of the DBCOLUMNFLAGS bitmask to determine if the column is nullable. Some OLE DB providers always set the MAYBENULLABLE element regardless if the field is nullable or not, in the interest of performance. These providers cause DTS to fail on the exact comparison of the columns between Source and Designation tables, thus causing the "ValidateSchema Failed" error message. The Microsoft Jet Provider and Microsoft Access ODBC Driver are examples of a provider and driver that set the MAYBENULLABLE flag regardless of the query.
To determine the DBCOLUMNFLAGS settings for your provider perform the steps that follow.

NOTE: The example uses the Microsoft Jet 4.0 Provider.

  1. Run the OLE DB RowsetViewer utility that is shipped with the Microsoft Data Access (MDAC) SDK.
  2. Perform a full connect and specify Jet 4.0 as the provider and Northwinds.mdb as the DataSource.
  3. Once a session is established, type Select * from Customers in the Session window.
  4. Click the Command menu option, point to ICommand, click Execute, and then click OK in the dialog box.
  5. Once the Rowset is created, click the Rowset menu option, point to IColumnsInfo, and then click GetColumnInfo.

The meta-data for each column displays. Note the MAYBENULL value.

Steps to Reproduce Behavior


To reproduce the error message, use these steps:

  1. Run the following script on SQL Server to create a sample table:

    Use Pubs
    Create table test (Col1 varchar(25) not null, Col2 varchar(25))
                        
  2. Create a table in Microsoft Access named Acctest that has the exact structure as the test table. Set the Required property for Col1 so that it does not allow nulls. Populate the table with some test data.
  3. Create an ODBC data source name (DSN), which points to the SQL Server database and the Microsoft Access database.
  4. Find the DTSEXMP3 sample that is shipped on the SQL Server 7.0 CD-ROM. The sample is located in the D:\MSSQL7\DevTools\Samples\Dts folder. In order to see the error message display, you must follow the instructions in the following Microsoft Knowledge Base article:

    240221 INF: How To Handle Errors in DTS Package and Step Objects

  5. Run the sample.
  6. The sample first requests the DSN of the Source table. Type the name of the DSN that points to the Microsoft Access MDB file.
  7. The sample then request the DSN of the Designation. Type the DSN name for the SQL Server.
  8. Once connected you then choose the SQL Statement option button.
  9. In the Statement Window type the following:

    Select * from Acctest
                        
  10. For the Designation Table name, type the following:

    Pubs..Test
  11. Click OK.

The error message appears in the output window.

To make the sample work correctly change the following lines in the sample from:

.TransformFlags = DTSTransformFlag_AllowLosslessConversion
                

-to-


.TransformFlags = DTSTransformFlag_Default
                

This allows the sample to run without causing the error message and the transformation does occur.

REFERENCES

For additional information on IColumnsInfo::GetColumnInfo, see the OLE DB SDK Programmers's Reference.

For additional information on DTSTransformFlags, see the SQL Server Books Online topic "DTSTransformFlags".

Keywords: kbdatabase kbjet kbprb KB256040