What do I need to be aware of when using ADT with Microsoft SQL Server 2005 versus SQL Server 2000?
Article ID: 53389
Updated On:
Products
Issue/Introduction
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 in | Database hosted by | Microsoft ODBC driver | ADT Patch required |
| Idb Database | SQL Server 2000 | SQL Server | None |
| MDB Database * | SQL Server 2000 | SQL Server | QO75756, QO78806 |
| Idb Database | SQL Server 2005 | SQL Native Client | QO78998 |
| MDB Database ** | SQL Server 2005 | SQL Native Client | QO79245 |
* 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 version | ADT 2.2 Profile Interface | Access method | Microsoft ODBC driver |
| SQL Server 2000, 7.0, 6.5 | Microsoft SQL Server | dblib | N/A |
| SQL Server 2000 | Microsoft SQL Server/ODBC | ODBC | Microsoft SQL Server |
| SQL Server 2005 | Microsoft SQL Server/ODBC | ODBC | Microsoft SQL Native Client |
See TEC301615 for detailed information on completing ADT Profile properties for all interfaces.
See TEC476048 for a list of ADT tables.
Environment
Component: DWM
Feedback
