从Azure Stream Analytics将数据插入Azure SQL数据库表时出现'OutputDataConversionError.TypeConversionError' [英] 'OutputDataConversionError.TypeConversionError' when inserting data into Azure SQL Database table from Azure Stream Analytics

问题描述

我正在尝试学习Azure IoT.我正在尝试将MQTT消息发送到IoT中心.从物联网中心，我正在使用流分析将数据输出到SQL数据库.但是目前在Streaming Analytics输出中，我遇到此错误:

I am trying to learn Azure IoT. What i am trying to is to send MQTT message to IoT Hub. And from IoT hub, i am using Streaming Analytics to output the data into SQL Database. But currently in Streaming Analytics Output, i have this error:

[9:12:30 AM]源'OUTPUTSQL'在处理时间'2019-05-23T01:12:30.5631321Z'和'2019-05-23T01:12之间有1次出现'OutputDataConversionError.TypeConversionError'类型: 30.5631321Z'.

[9:12:30 AM] Source 'OUTPUTSQL' had 1 occurrences of kind 'OutputDataConversionError.TypeConversionError' between processing times '2019-05-23T01:12:30.5631321Z' and '2019-05-23T01:12:30.5631321Z'.

这是我要保存的数据

{"ActionBy":"admin"，"ActionDate":"2019-05-22T16:00:00.0000000"，"Topic":"IoT"，"Message":"Hello World"，"QoS":"1，" EventProcessedUtcTime:" 2019-05-23T00:19:31.8287610Z，" PartitionId:0，" EventEnqueuedUtcTime:" 2019-05-23T00:19:31.7170000Z，" IoTHub:{" MessageId :null，" CorrelationId:null，" ConnectionDeviceId:" hartaMQTT-Device01，" ConnectionDeviceGenerationId:" 636936650733289038，" EnqueuedTime:""2019-05-23T00:19:31.6820000Z"，"StreamId":null }}

{"ActionBy":"admin","ActionDate":"2019-05-22T16:00:00.0000000","Topic":"IoT","Message":"Hello World","QoS":"1","EventProcessedUtcTime":"2019-05-23T00:19:31.8287610Z","PartitionId":0,"EventEnqueuedUtcTime":"2019-05-23T00:19:31.7170000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"hartaMQTT-Device01","ConnectionDeviceGenerationId":"636936650733289038","EnqueuedTime":"2019-05-23T00:19:31.6820000Z","StreamId":null}}

我认为错误在于IoTHub部分.

I think the error is in IoTHub part.

这是我要保存的Azure Sql Server中的表:

This is the table in Azure Sql Server that i am trying to save:

CREATE TABLE [dbo].[IoTMQTT](
[EventID] [bigint] IDENTITY(1,1) NOT NULL,
[ActionBy] [nvarchar](400) NOT NULL,
[ActionDate] [datetime] NOT NULL,
[Topic] [nvarchar](400) NULL,
[Message] [nvarchar](400) NULL,
[QoS] [nvarchar](400) NULL,
[EventProcessedUtcTime] [datetime] NULL,
[PartitionId] [nvarchar](400) NULL,
[EventEnqueuedUtcTime] [datetime] NULL,
[IoTHub] [nvarchar](max) NULL,
)

出什么问题了?是数据类型吗?请帮我.谢谢！

What is the problem? Is it the datatype? Please help me. Thank you!

推荐答案

Azrue SQL数据库可以将"2019-05-23T00:19:31.8287610Z"转换为datetime2，smalldatetime，time，date ，但datetime除外.这是错误消息:

Azrue SQL Database can convert "2019-05-23T00:19:31.8287610Z" to datetime2 , smalldatetime, time, date, except datetime. Here is the error massage:

根据您的数据，我认为数据类型datetime2最适合您.

According your data , I think the datatype datetime2 best for you.

请修改表格，将ActionDate，EventProcessedUtcTime，EventEnqueuedUtcTime列更改为datetime2.

Please modify your table, alter ActionDate, EventProcessedUtcTime, EventEnqueuedUtcTime columns to datetime2.

希望这会有所帮助.

这篇关于从Azure Stream Analytics将数据插入Azure SQL数据库表时出现'OutputDataConversionError.TypeConversionError'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！