Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rreynolds
Starting Member
3 Posts |
 Posted - 2004-12-06 : 11:53:51
|
| OK folks, I think I have a weird one here...Oh, by the way, this is my first post here so thanks to the folks who host and support these forums. Always nice to have a place to go and scratch your head with folks who know more than you. {;^>} Sorry the first post is so long winded...Here's the problem:The system configuration is SQL 7 running on Business Server 2003, that's weird enough since SQL 7 is not supported on Server 2003. Even says so when you install, but I'm working with an extremely disjointed legacy system here. Truth is, I'm surprised and somewhat impressed that the thing runs at all. The front-end applications are all in Access 2000 (converted from Access 97). I would say that 99% of the SQL processing is done either in Access queries or in-line code in Access VBA. In other words, there are very few SPROCs.My project, to convert SQL 7 to SQL 2000. My plan, just do the upgrade. I have even had the opportunity to test the upgrade process on another server with Server 2003 and SQL 7 in place. Everything went smooth as far as the upgrade was concerned. The problem arose during testing the Access front-end applications.It involves the following Access query:UPDATE Batches SET Batchs.BatchProcess = 0, Batchs.BatchDone = -1WHERE Batchs.BatchProcess = -1"Batchs" is a linked SQL table.The results of this query are as follows:1. Executed from Query Analyzer - Query works correctly.2. Executed from Access - It selects no records, so the update obviously does not take place, and reports no error.We have tried replacing the -1 with 1 and there is no difference in outcome from either Query Analyzer or Access. We did however discover, by accident, that changing the "Fail On Error" property of the Access query to Yes seems to solve the problem. This is great but leaves us wondering what other gottchas we are going to run into. We do not understand why this change fixes the problem and would like to know if anyone can explain it to us so we have a better handle on our planned upgrade.Thanks in advance for any assistance on this issue.Rick ReynoldsDatabase Systems ManagerWarranty Corp.rreynolds@warrantycorp.com |
|
|
rreynolds
Starting Member
3 Posts |
Posted - 2004-12-06 : 12:26:02
|
| Well, sorry to waste everyone’s time here. But we've found the explanation. The reason I apollogize is that we found the answer in the Access help files. The one place no one looked and I still wouldn't have expected the answer we found. {;^>}Just for FYI in case any one else runs across this.It seems that this is exactly what the FailOnError property of an Access Update query is for. To quote the Access help topic "FailOnError Property":"You can use the FailOnError property to specify whether an update or delete query that is run against an ODBC data source will terminate if an error occurs. The FailOnError property lets you optimize bulk update queries run against ODBC data sources so that they are executed on the server if the partial-failure behavior of the Microsoft Jet database engine isn't desired."I was unaware of this partial-failure behavior of Jet. Go figure...Oh well, hope this helps someone and sorry for wasting everyone’s time.Thanks again,Rick ReynoldsDatabase Systems ManagerWarranty Corp.rreynolds@warrantycorp.com |
 |
|
|
|
|
|
|
|
A weird one... SQL 7 to SQL 2000 Access front-end