Exception when Patch ' Type datetime2 is not a defined system type'

Like (0) Share

Report

Posted on 13 Dec 2019 15:25:01 by Yakimo

I am trying to add a record to a SQL Servers 2005 table

using:

Patch('[dbo].[SADailyCR2]', Defaults('[dbo].[SADailyCR2]'),{PD: 10 } , {MD: 110 }, {UD: 1110 }, {Date23: dtpD_1.SelectedDate })

and I get an error: The requested operation is invalid. Server Response: Microsoft SQL: Type datetime2 is not a defined system type."

The field Date23 is of a type [datetime],

SQL Server is v. 2005

Any help out there?

Thanks, Yakimo

How can I overcome that error?

If I avoid to add date in Date23, adding a new record if just fine

Categories:

General Questions

Capture.PNG

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

v-yutliu-msft on 16 Dec 2019 at 09:49:11

Like (1)

Report
Re: Exception when Patch ' Type datetime2 is not a defined system type'

Hi @Yakimo ,

Actually, using powerapps to update datetime2 in SQL Server works.

I've made a similar test, my time column is datetime2.

The formula that I use:

Patch('[dbo].[food_info]',Defaults('[dbo].[food_info]'),{type:"aa",time:DatePicker1.SelectedDate})

And it updates successfully.

So I think that should be your SQL Server version problem.

My SQL Server version: 2017

What's more, if you want to filter based on datetime2 in powerapps, it is not supported.

The following data types cannot be used as query option predicates:

date

datetime

datetime2

smalldatetime

If you want to filter, you need to use this formula to transfer:

YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])

Anyway, updating is ok. So that should not be your formula or powerapps problem.

Here's a doc about this connector for your reference:

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

Was this reply helpful? Yes No
Verified answer

mcolbert 126 on 13 Dec 2019 at 15:51:28

Like (1)

Report

Re: Exception when Patch ' Type datetime2 is not a defined system type'

@Yakimo

SQL Server on-prem does behave a bit odd sometimes, especially with older versions of SQL.
I would first check that the selecteddate has a value, if not you need to send blank() to update as a null.
Also, check that you are using the latest gateway version (I am assuming this is an on-prem DB being that its 2005)

One alternative you may try is calling a flow that executes a stored procedure to insert the row. This is the approach I typically use as it eliminates a lot of the issues you are seeing. If you try and use a datetime column in a query predicate it will either error or return 0 records on what appears to be a perfectly normal query.

The other obvious benefit to calling a stored procedure is any additional work you may want to do in the sp that would otherwise be difficult or impossible in PowerApps (like updating multiple tables in a transaction). I also often use a single stored proc that will handle upserts (update/insert) as needed.

There is more info here regarding the connector.

Hope this helps.

Was this reply helpful? Yes No