FIX: Visual C++ ATL Provider Fails when Used with SQL DTS

• Microsoft Visual C++, 32-bit Enterprise Edition, version 6.0
• Microsoft SQL Server version 7.0

SYMPTOMS

SQL Server version 7.0 Data Transformation Services (DTS) works with an OLE DB provider as a source or destination. If you use DTS Import Wizard with a Visual C++ ATL wizard-generated OLE DB Provider as source, when you execute the package the following error occurs:

Required connection properties have not been specified in a connection object.

CAUSE

This is caused by a bug in the ATL Provider templates. The ATL Provider templates incorrectly returns initialization properties when IDBProperties::GetPropertyInfo() is called with the property set GUID is set to DBPROPSET_DBINITALL.

RESOLUTION

The ATL implementation of IDBProperties::GetPropertyInfo() has been fixed in Visual Studio 6.0 Service Pack 3.0 which is available at www.microsoft.com/vstudio.

Also, to get DTS to import from the default ATL wizard-generated provider, do the following:

1. In the Select Source Tables window, the SQL Destination table name is shown as [databasename].[dbo].[*]. Change the table name "*" to another valid identifier.



2. Change the destination data-types. This can be accomplished in one of two ways:



• Change the T-SQL Syntax
  
  
  1. Click Transform in the Select Source Tables window. The Wizard shows decimal destination type for FileAttributes, FileSizeHigh, and FileSizeLow columns in the source.
  
  
  
  2. Change data type of FileAttributes to Varchar(4).
  
  
  
  3. Change data type of FileSizeHigh and FileSizeLow to int.
  
  
  



• Change the provider column map
  
  In the file that has the declaration for CFilesWindowsFile (FilesRS.h) change the property map for the rowset to more accurately specify the source datatypes.

  
  PROVIDER_COLUMN_ENTRY_FIXED("FileAttributes", 1, DBTYPE_I4, dwFileAttributes)
  PROVIDER_COLUMN_ENTRY_FIXED("FileSizeHigh", 2, DBTYPE_I4, nFileSizeHigh)
  PROVIDER_COLUMN_ENTRY_FIXED("FileSizeLow", 3, DBTYPE_I4, nFileSizeLow)
  PROVIDER_COLUMN_ENTRY_STR("FileName", 4, cFileName)
  PROVIDER_COLUMN_ENTRY_STR("AltFileName", 5, cAlternateFileName) 




3. Complete the remaining steps and the data should be copied correctly.

Select * from Openrowset ('MyProv.MyProv', '','c:\*.*') 

STATUS

This bug was corrected in Visual Studio 6.0 Service Pack 3.0.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create an ATL project using the ATL COM AppWizard.



2. Create an ATL OLE DB provider by clicking New ATL Object on the Insert menu, and then select Data Access for the Category and Provider for the Object.



3. Build the ATL OLE DB Provider.



4. Register the provider on a machine with SQL Server 7.0 installed.



5. Run DTS Import Wizard and select the ATL provider as Source (you don't need to specify a Server, UserID, or Password) and choose SQL Server as the Destination.



6. In the Select Source Tables window, "MSSQL7\BINN\*.*" is shown as the source table. You can preview all data retrieved by the ATL provider. In the Destination field box, the SQL table name is shown as "[<databasename>].[dbo].[*]".

Required connection properties have not been specified.

REFERENCES

For additional information about fixes needed to use ATL OLEDB provider with SQL 7.0 distributed query, please see the following articles in the Microsoft Knowledge Base:

Q200921 OpenQuery with ATL Provider Fails When Set Up with SEM

Q198520 ATL OLE DB Provider Fails When Called from SQL 7.0 Query

Additional query words: kbDatabase kbAtl kbOLEDB

Keywords : kbDatabase kbOLEDB kbVC600bug kbATL300bug kbSQLServ700 kbVS600sp2bug kbVS600sp3fix kbGrpVCDB kbVS600SP1bug
Version : WINDOWS:6.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbbug