November 16, 2005 at 10:30 am
I have a cursor based on a view which has schema binding. If a user uses an Access adp to rearrange the column view order of the base table using Datasheet View then Access updates the extended properties of the base table. This causes the cursor to fail with Error 16943 "Could not complete cursor operation because the table schema changed after the cursor was declared"
Note that the definition of the columns in the base table have not changed, nor have their order in the table definition - just some extended properties used by Access to record column order for its Datasheet.
Is Schema binding worthless? I thought it was supposed to prevent these changes. How do I prevent this problem.
[Please no lectures on not using cursors - I am performing a LIFO stock allocation. I sent a whole week trying to get a non cursor solution and only got with 1/10 the speed of the cursor approach. I have 1.5M rows to allocate so a x10 sped up is not to be lightly discarded.]
November 16, 2005 at 12:21 pm
So, if you choose to use a cursor, why are you not doing it in TSQL (i.e. in a stored proc)? If you are using Access and some Access/client side cursor process, shouldn't this question be in a different forum?
(And, just because you can't figure out how to do it without a cursor does not mean it can't be done or done faster.)
November 16, 2005 at 2:04 pm
I am using T-SQL in a stored procedure. The interference comes from an Access user doing Access user sort of things.
Aside:
I would love a non-cursor solution. The problem is this:
1. Batches of movements containing positive and negative inventory movements are allocated against existing unallocated movements. +ve against -ve and -ve against +ve.
2. When matching movements in a batch, only the most recent available movement must be chosen for the match for the location/item in question.
3. When a match is found the unallocated amounts on the two movements are reduced (and the cost price is noted).
4. Once a movement is fully allocated it is not considered for any further allocations.
5. The cost price allocated is taken as the weighted average of the movements which were used to allocate against it.
The rub is that SQL lacks an efficient TOP n per group operator. i.e give me the most recent movement for this list of Location/items. (Perhaps the new Rank operator in SQL Server 2005 might achieve this).
November 16, 2005 at 2:16 pm
To do things like this, I wrote a utility proc that will quickly numerically sub-sequence within a sort order. i.e.
key1, subkey1, 0
key1, subkeyB, 0
key1, subkeyX, 0
key2, subkeyF, 0
key2, subkeyX, 0
key3, subkeyA, 0
gets updated into:
key1, subkey1, 1
key1, subkeyB, 2
key1, subkeyX, 3
key2, subkeyF, 1
key2, subkeyX, 2
key3, subkeyA, 1
From there any joins, grouping, ranking, gets much easier. This kind of processing was used to change a 24+hour cursor process into a 5 minute run. These "subkeys" could be datetimes, decimal distances along a line, etc.
November 16, 2005 at 3:07 pm
Nice trick - I'll remember it. I don't think that it will help me in this case as a batch could contain the same item multiple times which would necessitate constant renumbering. Oh well.
To clarify the original point. I have a T-SQL stored procedure running using a Declare/Open/Fetch type of cursor from Terminal X, say. A user on another terminal Y (in this case, at the other end of the state) used Access to view the base table in view mode (not design mode) and altered the column order or sort sequence in datasheet view. This killed the job running on Terminal X. At no point was the basic table definition changed. Schema binding should protect against this non-change (but doesn't).
I should note that users are not allowed to view tables directly. This was really a programmer trying to track down a problem. Public flogging is one solution - but not very technical.
p.s. I havn't given up on non-cursor solution. If anyone has an outline of how to proceed I would be grateful.
November 17, 2005 at 4:31 pm
I've replaced the fetch cursor with the following key chaining approach:
Set @OldUniqueKey = 0
while 1=1
Select Top 1 @UniqueKey=UniqueKey,... from Movements
Where UniqueKey > @OldUniqueKey
Order by UniquueKey
if @@RowCount 1 break
.....
Set @OldUniquekey = @UniqueKey
end
This runs very nearly as fast as the Fetch cursor and is not affected by Access users changing extended properties on the Movements table.
p.s. Would still prefer a set at a time approach. Any suggestions?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply