SQL Server 2008.
I saw this in a previous post:
"SDAC generates update statements in the most efficient way: an update statement contains SET statements only for fields that were changed, and only with the primary key in the WHERE part. Only in case when a table doesn't have primary key, update statement is generated with all fields in the WHERE part. Also you can use the KeyFields property to make SDAC generate an update statement with all fields from the KeyFields property in the WHERE part."
However, our experience is that SDAC generates the update statements with all fields in the WHERE clause. This is not a problem, except when we have a datetime field that changes. Then, the update will succeed randomly, but mostly fails with the error, "Update failed: Found 0 records". In troubleshooting this it appears that the parameter for the datetime field is being set to the correct value, however I also noticed that the datetime size in the field definition is 8 and the parameter size is 7. Could that be what's causing the problem? If not, what can we do, other than removing all the datetime fields from the generated WHERE clause.
Thanks! Dave
Update uses all fields in WHERE and fails with datetime.
Update uses all fields in WHERE and fails with datetime.
Further testing has shown that if I change the datetime field in the table to a datetime2 to match the parameter used in the update, it works fine.
When the parameter type is chosen for the update, shoudn't it be based on the actual datatype of the field in the table?
Also, I have found that no matter what the datatype of the datetime field is, if it is left NULL, and we generate the UpdateSQL, then all subsequent updates will fail with the same error message. The difference between creating the UpdateSQL ourselves and letting SDAC create it, is that the the resulting SQL Server statments are different. The SDAC created version uses IS NULL in the select where when we fill in the UpdateSQL property on the query, it is evaluating DATE=NULL in the WHERE clause.
Please let me know if this can be corrected in the next update or if there is another way around it that I'm missing. This pretty much breaks our entire application as we have a datetime stamp field in every query.
Thanks,
Dave
When the parameter type is chosen for the update, shoudn't it be based on the actual datatype of the field in the table?
Also, I have found that no matter what the datatype of the datetime field is, if it is left NULL, and we generate the UpdateSQL, then all subsequent updates will fail with the same error message. The difference between creating the UpdateSQL ourselves and letting SDAC create it, is that the the resulting SQL Server statments are different. The SDAC created version uses IS NULL in the select where when we fill in the UpdateSQL property on the query, it is evaluating DATE=NULL in the WHERE clause.
Please let me know if this can be corrected in the next update or if there is another way around it that I'm missing. This pretty much breaks our entire application as we have a datetime stamp field in every query.
Thanks,
Dave
-
AndreyZ
Hello,
SDAC can use all fields for updating SQL queries in two cases: when your table doesn't have Primary Key or if the TMSQuery.UpdateAllFields property is set to True. Please check that your table has Primary Key, and that you didn't set the TMSQuery.UpdateAllFields property to True. If the problem persists, please post here or send to andreyz*devart*com a script to create your table.
SDAC can use all fields for updating SQL queries in two cases: when your table doesn't have Primary Key or if the TMSQuery.UpdateAllFields property is set to True. Please check that your table has Primary Key, and that you didn't set the TMSQuery.UpdateAllFields property to True. If the problem persists, please post here or send to andreyz*devart*com a script to create your table.