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 |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2004-12-06 : 22:03:18
|
I have the following (smiplified) code which works fine in QA, but doesn't work when I translate it into VB or JAVA. declare @id numeric(15)begin tran t1 exec proc_assign_id 'REP', @id OUTPUT insert into B.dbo.RPTD values(@id,'Value1') exec proc_assign_id 'REP', @id OUTPUT insert into B.dbo.RPTD values(@id,'Value2')commit tran t1 The proc_assign_id is a procedure which selects the largest ID from the table, and calculates what the next ID will be. Unfortunately, the IDs are required to be of a particular format, and must be generated "manually", but they also must be sequential for each table - hence proc_assign_id must look at the result of the previous insert (ie mid-transaction).The proc must be generic enough to take a logical model entity (in this case "REP"), and return the ID for a particular table ("RPTD") in the physical model, and so uses dynamic sql to find the next number. (I've "hard-coded" the table name into the QA code inserts for testing).If there are no records in the table, the proc should return BLABLABLA001if there is a record with BLABLABLA001, then the proc should return BLABLABLA002 (ie BLABLABLA001 + 1)and so on. Its just a sequential ID.So when I run it in QA, I get a nice sequence of numbers - BLABLABLA001 BLABLABLA002 etcBut when I run it from VB/JAVA I getBLABLABLA001Error - PK violation (trying to insert a duplicate BLABLABLA001)Basically, SQL Server doesn't appear to recognise that the proc is within the same transaction as the inserts.Finally, if I convert the actual proc code (which I'd rather not do) into VB/JAVA it all works fine again - so obviously it SQL Server recognises that this is all in the same transaction.So now to my question - is there someone out there who can explain what's going on? Is there a rule somewhere which says that you can't call an SP inside a transaction to read uncommitted data via ODBC?And apologies if I have not explained myself clearly enough.PS - before you ask, yes I've tried setting the isolation level of the transaction to READ UNCOMMITTED with no change, and I'm using WITH (NOLOCK) hints on the select inside the proc, and finally, yes it really does work fine in QA (without changing isolation levels etc).--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2004-12-07 : 02:58:59
|
| Hey!My only syggestion would be to put the whole transaction inside a procedure, but maybe that's not possible in this case?/Andraax |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-07 : 04:32:33
|
| Not sure what this has to do with isolation levels or trnsactions.Are you saying that it fails on all inserts or just when two connections are trying to insert at the same time?If the former then it's due to the passing o fthe parameters.If the latter then it's probably due to the way the transaction is created from the app or an misunderstanding about locking that is relyied on in the SPs (you would have to hold the lock when generating the id).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-12-07 : 17:29:23
|
| hi guysnigel - thanks for your response, but neither. Andraax - everything is already in one transaction.The failure is that it can't see that a new record has been created mid-transaction. There's only ever one connection - and one transaction - which is both trying to read and update from the same table. As I say, it works fine in QA, or if I translate the sp into VB/JAVA, but not if I call the sp from Java/VB as part of the transaction.any ideas?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-08 : 06:04:31
|
| Can you not compromise the design..?if you 'know the id' to be created in INSERT#1, then can you not control the id to be used in INSERT#2?also "Andraax - everything is already in one transaction."...ONE VB Transaction or ONE SP Transaction?Best advice is to do it all in SP...and not to use transactions at the client-level....especiallyu if there is a chance the transaction can be left open/uncommitted/locking for an inordinate time. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-12-08 : 17:33:48
|
| Hi AndrewYes, I can convert the sp into VB/JAVA - but to know what ID will be next requires me to keep track of what ID each of 30-40 tables may be up to - rather than one nice neat SP call. But importantly, I'm trying to understand why I need to.Everything is in one transaction - exactly as in the code above. The SP is called from within the transaction via ODBC. Doing it all in one SP is also problematic because I would have to pass in heaps of parameters for all the tables involved...Is calling an SP from within a series of ODBC calls known to be problematic? Does it end the current transaction?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
isolation level?