Cannot find data type datetime2 when deploying a data container to SQL Server 2005

Overview

With a subscription version of Talend MDM 5.2 using a Microsoft SQL Server 2005, you might encounter an exception when deploying a new data container. This error states that the RDBMS "Cannot find data type datetime2".

Environment

This issue only occurs with a subscription version of Talend MDM 5.2.x, using RDBMS persistence with Microsoft SQL Server 2005. 

Symptoms/Description

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:

server.log
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.

Resolution

Using Microsoft SQL Server Management Studio GUI

  1. Deploy the data container. You'll get the above-mentioned exception.
  2. In a Microsoft SQL Server Management Studio, locate the database related to your data container.
  3. Develop the following tree nodes: Programability > Types > User-Defined Data Types

  4. Right-click User-Defined Data Types, and then select New User-Defined Data Type...
  5. Create a "datetime2" type which 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".

  6. 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.

  7. Redeploy the Data Container. The deployment should now be successful.

Using SQL script

You can also use proprietary DDL to carry out the previous operations:

USE [Product_MASTER]
GO
CREATE TYPE [dbo].[datetime2] FROM [datetime] NULL
GO
Version History
Revision #:
1 of 1
Last update:
‎04-17-2017 10:55 PM
Updated by:
 
Labels (1)
Contributors