What do I need to be aware of when using ADT with Microsoft SQL Server 2005 versus SQL Server 2000?

Document ID : KB000053389
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Advantage Data Transformer supports Microsoft SQL Server as an IDB host and as a source or target of data movement, but using a SQL2005 IDB requires an ADT patch and a SQL2005 source or target requires a new interface.

Solution:

ADT Tables - the IDB

The tables containing metadata and ADT programs are referred to as the IDB, the Internal DataBase. You may be using IDB tables that are predefined in the CA MDB database, or you may have created a separate IDB database. Some patch or combination of patches will be required for an IDB hosted by Microsoft SQL Server 2005.

ADT as a primary product or ADT as a component of Unicenter APM will require patches. ADT as a component of CMDB is already patched, so the patches to support SQL2005 are not required.

ADT 2.2 Tables reside inDatabase hosted byMicrosoft ODBC driverADT Patch required
Idb DatabaseSQL Server 2000SQL ServerNone
MDB Database *SQL Server 2000SQL ServerQO75756, QO78806
Idb DatabaseSQL Server 2005SQL Native ClientQO78998
MDB Database **SQL Server 2005SQL Native ClientQO79245

* QO75756 and QO78806 are MDB patches related to the ADT tables.
** Downloading QO79245 provides you with the above two MDB patches and the ADT patch to support SQL2005.

Microsoft SQL Server Profile Interfaces

Advantage Data Transformer includes two Profile Interfaces for accessing Microsoft SQL Server, the older "Microsoft SQL Server" interface and the new "Microsoft SQL Server/ODBC" interface introduced by ADT r2.2.

When the source or target database is hosted by SQL Server2000, you may use either interface. When the source or target is hosted by SQL Server 2005, use the Microsoft SQL Server/ODBC interface. And when configuring ODBC DataSources to act as "server" for the Microsoft SQL Server/ODBC interface, be sure to use the correct Microsoft ODBC driver.

Microsoft SQL Server versionADT 2.2 Profile InterfaceAccess methodMicrosoft ODBC driver
SQL Server 2000, 7.0, 6.5Microsoft SQL ServerdblibN/A
SQL Server 2000Microsoft SQL Server/ODBCODBCMicrosoft SQL Server
SQL Server 2005Microsoft SQL Server/ODBCODBCMicrosoft SQL Native Client

See TEC301615 for detailed information on completing ADT Profile properties for all interfaces.
See TEC476048 for a list of ADT tables.