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
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
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 😕
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:
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,
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.
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
Super User 2025 Season 2
mmbr1606
275
Super User 2025 Season 2