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

Overview

When deploying a new data container, you might encounter an exception stating 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.

image2013-4-23 11-22-44.png

 

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.

 

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

    image2013-4-23 11-30-32.png


  4. Right-click User-Defined Data Types, then select New User-Defined Data Type.
  5. Create a datetime2 type that extends the standard datetime type. Don't forget to select the Allow NULLs attribute checkbox.

    image2013-4-23 11-33-0.png


    The new type is added to the list of "User-Defined Data Types".

    image2013-4-23 11-34-35.png


  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.

    image2013-4-23 11-39-20.png


 

Using an SQL script

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

 

Version history
Revision #:
2 of 2
Last update:
‎06-20-2017 05:28 PM
Updated by:
 
Labels (1)
Tags (1)