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 |
|
sogomez
Starting Member
4 Posts |
 Posted - 2009-04-17 : 18:43:01
|
| Hello everybody,I have an issue with sql server 2000 and web service called in .Net 3.5The next instruction is executed in web service:string insertStr = "INSERT INTO proveedor (accion, idEmpresa, echaTran, cadena, sucursal, terminal, fechaSucursal, no_emp, folio, telefono, importe)" + " VALUES (" + string.Format("{0}, {1}, '{2}', (3}, {4}, '{5}', '{6}', {7},{8},{9}, {10})", accion, LaEmpresa, theTimeStr, LaCadena, LaSucursal, LaTerminal, theTimeStr, ElEmpleado, ElFolio, cuenta, Importe) + ";SELECT SCOPE_IDENTITY()";SqlCommand cmd = new SqlCommand(insertStr, conn);idTran = Convert.ToInt32(cmd.ExecuteScalar());There is a IDENTITY field named idTran in this table without triggers.Right now there are 450,000 rows in the table and 9000 new rows added daily with same numbers of execution of the Web Service In the lifetime of this web service I noticed a problem TWICE:Two inserted rows at the same time for the same web service called at two different sessions, returned the same IDENTITY value. In the table the values are two sequential numbers. (ex 421,321 and 421,322), but the values returned with SCOPE_IDENTITY() instruction in the Web Service is the last number (in this case 421,322).The first time (one month ago) I replace @@identity for scope_identity().Rigth now I'm going to replace the instructions with a procedure to insert the values and retrieve the IDENTITY value, expecting to resolve this problem.Any idea what is happening? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-18 : 15:22:11
|
| are you doing this insert in a loop? |
 |
|
|
sogomez
Starting Member
4 Posts |
Posted - 2009-04-20 : 15:23:32
|
| Nope,I think I didn't explain well.This system is in production for 4 months and for each session calling this web service there is exactly one record inserted.Some times two or more different sessions call this web services exactly at the same times (I record the insert time in the table).I have 2 cases when the web services called by 2 different sessions at the same time return the same IDENTITY value for both sessions, but in the table there are two different values (sequential).The problem is not the value at the table but the value returned by SCOPE_IDENTITY() instruction.In more that 450,000 times the web services is called in 4 months, this happened twice.Right now I'm thinking about a bug in the way .Net 3.5 connect to SQL Server 2000 (Of course the bug can be in my coding).For that reason I changed the code for a Store Procedure where I insert the row and return the IDENTITY value, thinking to confine the instruction to SQL Server.If you can wait another 4 months I will reply the verdict (or maybe sooner if the problem is other part).Thanks for you question and your interest. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-20 : 16:16:33
|
When replacing @@IDENTITY with SCOPE_IDENTITY(), did you also change the batch separator "GO" to ";"?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sogomez
Starting Member
4 Posts |
Posted - 2009-04-20 : 18:29:01
|
| The separator was ";" with both @@IDENTITY and SCOPE_IDENTITY(). The first time I got the value repeated I was using @@IDENTITY, then I replaced with SCOPE_IDENTITY(), but after a few weeks and more than a 100,000 rows I got another value repeated.Right now I'm using SCOPE_IDENTITY() inside a Procedure, replacing the .NET calling from "INSERT" to "EXEC" and reading the returned value like a "SELECT" instruction.It is in in production for a few days and working fine. |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2009-04-21 : 11:25:13
|
| I am going to guess that your connection is being pooled (normal behavior) and you are in the same scope, therefore both @@Identity and Scope_Identity() are in a race condition. Moving the execution to a stored proc should solve the solution because the scope of execution has changed. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-04-21 : 14:29:12
|
| I'm not sure if this is the same issue you are having, but there is an open bug with MS:[url]https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811&wa=wsignin1.0[/url]Ahh crap, just realized I was in the SQL 2000 section. I think my link only applys to 2005. |
|
|
|
sogomez
Starting Member
4 Posts |
|
|
|
|
|
|
|
Same Identity value retrieved twice.