Azure Synapse Error: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type VARCHAR to DATETIME.

Anirudh Mounasamy 101 Reputation points
2023-06-06T07:18:05.4366667+00:00

I'm trying to create an external table for csv files stored in Azure Data Lake Gen2.

Also these csv files are under linked section in azure synapse analytics.

The table is created, when running a query to see the values I'm getting an error.These are screenshots and queries I used to create external table.

Can anyone help me out. My primary focus is to extract data from dataverse to Dedicated SQL pool in azure synapse. Previously I was using DES, which microsoft stopped that service on 31/05/2023. So I want the data sync lively, which happens in lake Database. But, I wanted to have that live sync in Dedicated SQL pool Database. So I created an External table in the databse to sync the data lively. but I'm getting these errors.

I'm ready to provide any further details if needed.

Screen shot of the error:
error

query used to create external table:

in this query I didn't included the source.

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat') 
	CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 
	WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
	       FORMAT_OPTIONS (
			 FIELD_TERMINATOR = ',',
			 STRING_DELIMITER = '"',
			 USE_TYPE_DEFAULT = FALSE
			))
GO

CREATE EXTERNAL TABLE [dbo].[accis_msfp_project_run]
(
	[Id] [nvarchar](100) NULL,
	[SinkCreatedOn] [datetime] NULL,
	[SinkModifiedOn] [datetime] NULL,
	[statecode] [bigint] NULL,
	[statuscode] [bigint] NULL,
	[msfp_status] [bigint] NULL,
	[createdby] [nvarchar](100) NULL,
	[createdby_entitytype] [varchar](256) NULL,
	[createdonbehalfby] [nvarchar](100) NULL,
	[createdonbehalfby_entitytype] [varchar](256) NULL,
	[modifiedby] [nvarchar](100) NULL,
	[modifiedby_entitytype] [varchar](256) NULL,
	[modifiedonbehalfby] [nvarchar](100) NULL,
	[modifiedonbehalfby_entitytype] [varchar](256) NULL,
	[owningbusinessunit] [nvarchar](100) NULL,
	[owningbusinessunit_entitytype] [varchar](256) NULL,
	[owningteam] [nvarchar](100) NULL,
	[owningteam_entitytype] [varchar](256) NULL,
	[owninguser] [nvarchar](100) NULL,
	[owninguser_entitytype] [varchar](256) NULL,
	[ownerid] [nvarchar](100) NULL,
	[ownerid_entitytype] [varchar](256) NULL,
	[createdbyname] [varchar](1026) NULL,
	[createdbyyominame] [varchar](1026) NULL,
	[createdon] [datetimeoffset](7) NULL,
	[createdonbehalfbyname] [varchar](1026) NULL,
	[createdonbehalfbyyominame] [varchar](1026) NULL,
	[importsequencenumber] [bigint] NULL,
	[modifiedbyname] [varchar](1026) NULL,
	[modifiedbyyominame] [varchar](1026) NULL,
	[modifiedon] [datetime] NULL,
	[modifiedonbehalfbyname] [varchar](1026) NULL,
	[modifiedonbehalfbyyominame] [varchar](1026) NULL,
	[msfp_customerinsightsconfiguration] [varchar](max) NULL,
	[msfp_dataclassification] [varchar](400) NULL,
	[msfp_description] [varchar](max) NULL,
	[msfp_environmentid] [varchar](400) NULL,
	[msfp_environmentregion] [varchar](400) NULL,
	[msfp_name] [varchar](1800) NULL,
	[msfp_permanentid] [varchar](400) NULL,
	[msfp_projectid] [nvarchar](100) NULL,
	[msfp_templateid] [varchar](400) NULL,
	[msfp_templateversion] [varchar](4000) NULL,
	[overriddencreatedon] [datetime] NULL,
	[owneridname] [varchar](640) NULL,
	[owneridtype] [varchar](8000) NULL,
	[owneridyominame] [varchar](640) NULL,
	[owningbusinessunitname] [varchar](640) NULL,
	[timezoneruleversionnumber] [bigint] NULL,
	[utcconversiontimezonecode] [bigint] NULL,
	[versionnumber] [bigint] NULL
)
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,174 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Vinodh247 27,386 Reputation points MVP
    2023-06-07T13:36:47.8033333+00:00

    Hi,

    Thanks for reaching out to Microsoft Q&A.

    I see few columns has been declared as datetime in the create table statement but the error says you are trying to insert varchar records into them due to which it's getting failed. You have to find a way to cast/convert to appropriate datatype before pushing ingesting the records or declare the susceptible columns simply as nvarchar to avoid the error and then use cast/convert in your select or data retrieval methods.

    Please Upvote and Accept as answer if the reply was helpful, this will be benefitting the other community members who go through the same issue.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.