java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

[Anonymous]

Talend throws the following error in a 2 step job.

java.sql.SQLException: Unable to convert between [B and TIMESTAMP.

The job is:

MSSqlInput --> tLogRow

The source is MS SQL Server 2005, and several columns in the table are defined as DATETIME -- to which Talend tries to assign a Date type.
What am I doing wrong?

[briansms]

Hi Eric,
You're not doing anything wrong, rather Talend is incorrectly tries to map a MS SQL timestamp field to a datetime, which it shouldn't. You need to change the mapping_MSSQL.xml file (Preferences > Talend > Metadata of TalendType), under the <TalendToDbTypes> and <dbToTalendTypes> nodes.
Change the <TalendToDbTypes> node for the id_String mapping to add dbType of TIMESTAMP

			<talendType type="id_String">
					<dbType type="VARCHAR" default="true" />
					<dbType type="NVARCHAR" />
					<dbType type="NCHAR" />
					<dbType type="NTEXT" />
					<dbType type="TEXT" />
					<dbType type="TIMESTAMP" />
				</talendType>

The change the <dbToTalendTypes> for TIMESTAMP:

				<dbType type="TIMESTAMP">
					<talendType type="id_String" default="true" />
				</dbType>

- Brian

[Anonymous]

Change the <TalendToDbTypes> node for the id_String mapping to add dbType of TIMESTAMP

			<talendType type="id_String">
					<dbType type="VARCHAR" default="true" />
					<dbType type="NVARCHAR" />
					<dbType type="NCHAR" />
					<dbType type="NTEXT" />
					<dbType type="TEXT" />
					<dbType type="TIMESTAMP" />
				</talendType>

The change the <dbToTalendTypes> for TIMESTAMP:

				<dbType type="TIMESTAMP">
					<talendType type="id_String" default="true" />
				</dbType>

I have added the TIMESTAMP dbType and modified the dbType entries as illustrated above, but Talend is not updating the schema. After changing the xml file I have clicked the "Reset DB Types" button in the Schema, I have removed the instance of the MSSQL Input object from the job and replaced it, and I have restarted Talend -- and the DATE datatypes have still not updated to String.
The two entries in mapping_MSSQL.xml now look like this:

				<talendType type="id_String">
					<dbType type="VARCHAR" default="true" />
					<dbType type="NVARCHAR" />
					<dbType type="NCHAR" />
					<dbType type="NTEXT" />
					<dbType type="TEXT" />
					<dbType type="TIMESTAMP" />
				</talendType>
				<dbType type="TIMESTAMP">
					<talendType type="id_String" default="true" />
				</dbType>

Is there something else I need to do?

[briansms]

Sorry Eric. Check the <talendType> for id_Date under <talendToDbTypes>, you might have to remove the entry for TIMESTAMP there.

	<talendType type="id_Date">
		<dbType type="DATETIME" default="true" />
		<dbType type="SMALLDATETIME" />
	</talendType>

- Brian

[Anonymous]

Sorry Eric. Check the <talendType> for id_Date under <talendToDbTypes>, you might have to remove the entry for TIMESTAMP there.

Got it. Thanks.
FYI -- "Reset DB Types" on the Schema in the Repository still didn't work. I actually had to delete the Table Schema from the repository and re-add it from the DB (Retreive Schema).