When deploying a new data container, you might encounter an exception stating that the RDBMS "Cannot find data type datetime2".
This issue only occurs with a subscription version of Talend MDM 5.2.x, using RDBMS persistence with Microsoft SQL Server 2005.
This error occurs when you deploy a data container bound to a data model using some xsd:date elements. Talend MDM tries to map it to the SQL Server type datetime2, which exists only in SQL Server 2008.
The following message is shown to the user in the Studio, when deploying a new Data Container to Talend MDM.
On the server side, the following exception is added to the server.log file:
10:22:48,299 ERROR [SOAPFaultHelperJAXRPC] SOAP request exceptionjava.rmi.RemoteException: Unable to physically create the data cluster Product: javax.ejb.TransactionRolledbackLocalException: Could not create storage 'Product' with data model 'Product'.; [Caused by]: Could not create storage 'Product' with data model 'Product'. [Caused by]: Could not create storage 'Product' with data model 'Product'. [Caused by]: Could not create storage for container 'Product' (MASTER) using data model 'Product'. [Caused by]: java.lang.RuntimeException: Could not prepare 'Product'. [Caused by]: Could not prepare 'Product'. [Caused by]: Exception occurred during Hibernate initialization. [Caused by]: Could not prepare database schema: Column, parameter, or variable #2: Cannot find data type datetime2.
Create a datetime2 type that extends the standard datetime type. Don't forget to select the Allow NULLs attribute checkbox.
The new type is added to the list of "User-Defined Data Types".
Deploy the Data Container again. You will get the same exception again, for the STAGING database this time. Repeat the above steps for the STAGING database.
Redeploy the Data Container. The deployment should now be successful.
You can also use a proprietary DDL to carry out the previous operations:
USE [Product_MASTER] GO CREATE TYPE [dbo].[datetime2] FROM [datetime] NULL GO