cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yakimo
Level: Powered On

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

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

 
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
mcolbert
Level 8

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.

 

View solution in original post

Community Support Team
Community Support Team

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
mcolbert
Level 8

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.

 

View solution in original post

Community Support Team
Community Support Team

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Yakimo
Level: Powered On

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

My SQL Server is 2005, so it seems that is the problem

As far as I know, DATATIME2 data type appeared in SQL Server 2008. 

 

Since my server is older, it seems that Patch get confused - too bad for me

Now for such a simple operation, I have to do work arounds to insert a date value 😕

Highlighted
Yakimo
Level: Powered On

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

Yes, my SQL server is 2005 and on-prem

My SQL Server is 2005, so it seems that is the problem

It seems that Patch get confused - too bad for me

 

 

Helpful resources

Announcements
thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (5,223)