| Groups | Blog | Home |
sql server programming : Possible Corruption?
I am using a this procedure to insert a record and return the value of the newly inserted record's ID. ID is an Int Identity field and PK. ***************** Create Procedure [dbo].[NewResRxOrder_SP] @ResID int, @AdmitDateID int As Insert Into [ResRxOrders] ([ResID], [AdmitDateID]) Values (@ResID, @AdmitDateID) /*** Return New Int ID [ID] ***/ Select SCOPE_IDENTITY() As NewID *************** One of my beta testers is getting a value returned that is a already exists in the table; and when the value is used in another relational table, it crashes with a constraint error (as it should). I have used DBCC DBREINDEX on all the tables, but the error persists. I have never seen SQL Server do this, especially with a single install on a computer for testing. Anyone have any ideas how to fix this and/or prevent it? We are using SQL Server 2000. TIA, -Steve-
Any big difference between the real value that should be returned and the
one that your beta tester is getting? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) [quoted text, click to view] "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... > Hello, > > I am using a this procedure to insert a record and return the value of the > newly inserted record's ID. ID is an Int Identity field and PK. > > ***************** > Create Procedure [dbo].[NewResRxOrder_SP] > @ResID int, > @AdmitDateID int > As > Insert Into [ResRxOrders] > ([ResID], > [AdmitDateID]) > Values > (@ResID, > @AdmitDateID) > /*** Return New Int ID [ID] ***/ > Select SCOPE_IDENTITY() As NewID > *************** > > One of my beta testers is getting a value returned that is a already > exists in the table; and when the value is used in another relational > table, it crashes with a constraint error (as it should). > > I have used DBCC DBREINDEX on all the tables, but the error persists. > > I have never seen SQL Server do this, especially with a single install on > a computer for testing. > > Anyone have any ideas how to fix this and/or prevent it? > We are using SQL Server 2000. > > TIA, > > -Steve- >
Steve
It looks very strange because it must not happen. Does the tester open only one session to un your SP? We need more details . Can you reproduce the problem? [quoted text, click to view] "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... > Hello, > > I am using a this procedure to insert a record and return the value of the > newly inserted record's ID. ID is an Int Identity field and PK. > > ***************** > Create Procedure [dbo].[NewResRxOrder_SP] > @ResID int, > @AdmitDateID int > As > Insert Into [ResRxOrders] > ([ResID], > [AdmitDateID]) > Values > (@ResID, > @AdmitDateID) > /*** Return New Int ID [ID] ***/ > Select SCOPE_IDENTITY() As NewID > *************** > > One of my beta testers is getting a value returned that is a already > exists in the table; and when the value is used in another relational > table, it crashes with a constraint error (as it should). > > I have used DBCC DBREINDEX on all the tables, but the error persists. > > I have never seen SQL Server do this, especially with a single install on > a computer for testing. > > Anyone have any ideas how to fix this and/or prevent it? > We are using SQL Server 2000. > > TIA, > > -Steve- >
Yes, only one session is used. We are using ADO with 1 connection.
No I can't reproduce the problem here. That's why I think there must be some corruption someplace within SQL Server. -Steve- [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:%232vQh4pmHHA.4552@TK2MSFTNGP04.phx.gbl... > Steve > It looks very strange because it must not happen. Does the tester open only > one session to un your SP? We need more details . > Can you reproduce the problem? > > > > "Steve Zimmelman" <skz@charter.nospam.net> wrote in message > news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... >> Hello, >> >> I am using a this procedure to insert a record and return the value of the >> newly inserted record's ID. ID is an Int Identity field and PK. >> >> ***************** >> Create Procedure [dbo].[NewResRxOrder_SP] >> @ResID int, >> @AdmitDateID int >> As >> Insert Into [ResRxOrders] >> ([ResID], >> [AdmitDateID]) >> Values >> (@ResID, >> @AdmitDateID) >> /*** Return New Int ID [ID] ***/ >> Select SCOPE_IDENTITY() As NewID >> *************** >> >> One of my beta testers is getting a value returned that is a already exists >> in the table; and when the value is used in another relational table, it >> crashes with a constraint error (as it should). >> >> I have used DBCC DBREINDEX on all the tables, but the error persists. >> >> I have never seen SQL Server do this, especially with a single install on a >> computer for testing. >> >> Anyone have any ideas how to fix this and/or prevent it? >> We are using SQL Server 2000. >> >> TIA, >> >> -Steve- >> > >
Yes. The value that seems to getting stuck is 302. 302 exists in the table and
was added back in April. The number returned number should be around 340. I should add we are using ADO with 1 connection object and placing a single transaction around this SP and other updates that are related to this insert. What's strange is that the crash doesn't occur until the value that is returned by NewResRxOrder_SP is used to create a related record in another table. And the returned value is clearly a duplicate. -Steve- "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:uGwr5LpmHHA.1340@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] > Any big difference between the real value that should be returned and the one > that your beta tester is getting? > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: sylvain aei ca (fill the blanks, no spam please) > > > "Steve Zimmelman" <skz@charter.nospam.net> wrote in message > news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... >> Hello, >> >> I am using a this procedure to insert a record and return the value of the >> newly inserted record's ID. ID is an Int Identity field and PK. >> >> ***************** >> Create Procedure [dbo].[NewResRxOrder_SP] >> @ResID int, >> @AdmitDateID int >> As >> Insert Into [ResRxOrders] >> ([ResID], >> [AdmitDateID]) >> Values >> (@ResID, >> @AdmitDateID) >> /*** Return New Int ID [ID] ***/ >> Select SCOPE_IDENTITY() As NewID >> *************** >> >> One of my beta testers is getting a value returned that is a already exists >> in the table; and when the value is used in another relational table, it >> crashes with a constraint error (as it should). >> >> I have used DBCC DBREINDEX on all the tables, but the error persists. >> >> I have never seen SQL Server do this, especially with a single install on a >> computer for testing. >> >> Anyone have any ideas how to fix this and/or prevent it? >> We are using SQL Server 2000. >> >> TIA, >> >> -Steve- >> > >
Steve
Restore the backup ( from the days that you are sure that db was OK) and try to re-run the query [quoted text, click to view] "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:%23wEqQc6mHHA.668@TK2MSFTNGP05.phx.gbl... > Yes, only one session is used. We are using ADO with 1 connection. > > No I can't reproduce the problem here. That's why I think there must be > some corruption someplace within SQL Server. > > > -Steve- > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:%232vQh4pmHHA.4552@TK2MSFTNGP04.phx.gbl... >> Steve >> It looks very strange because it must not happen. Does the tester open >> only one session to un your SP? We need more details . >> Can you reproduce the problem? >> >> >> >> "Steve Zimmelman" <skz@charter.nospam.net> wrote in message >> news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... >>> Hello, >>> >>> I am using a this procedure to insert a record and return the value of >>> the newly inserted record's ID. ID is an Int Identity field and PK. >>> >>> ***************** >>> Create Procedure [dbo].[NewResRxOrder_SP] >>> @ResID int, >>> @AdmitDateID int >>> As >>> Insert Into [ResRxOrders] >>> ([ResID], >>> [AdmitDateID]) >>> Values >>> (@ResID, >>> @AdmitDateID) >>> /*** Return New Int ID [ID] ***/ >>> Select SCOPE_IDENTITY() As NewID >>> *************** >>> >>> One of my beta testers is getting a value returned that is a already >>> exists in the table; and when the value is used in another relational >>> table, it crashes with a constraint error (as it should). >>> >>> I have used DBCC DBREINDEX on all the tables, but the error persists. >>> >>> I have never seen SQL Server do this, especially with a single install >>> on a computer for testing. >>> >>> Anyone have any ideas how to fix this and/or prevent it? >>> We are using SQL Server 2000. >>> >>> TIA, >>> >>> -Steve- >>> >> >> > >
If possible, try to backup/restore (or even better, duplicate the database
using a software like RedGate) to another database and see if nothing change. Is this beta tester using the same database as everyone else or is he using a private copy? Did you try to force a recompilation of the stored procedures (or even better, to clear all caches: DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS). Finally, are you using things like « SET IDENTITY_INSERT table ON » that could have stick on? Does anything change is you set a « SET IDENTITY_INSERT table OFF » at the beginning of the SP? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) [quoted text, click to view] "Steve Zimmelman" <skz@charter.nospam.net> wrote in message news:OycIRg6mHHA.588@TK2MSFTNGP06.phx.gbl... > Yes. The value that seems to getting stuck is 302. 302 exists in the > table and was added back in April. The number returned number should be > around 340. > > I should add we are using ADO with 1 connection object and placing a > single transaction around this SP and other updates that are related to > this insert. > > What's strange is that the crash doesn't occur until the value that is > returned by NewResRxOrder_SP is used to create a related record in another > table. And the returned value is clearly a duplicate. > > -Steve- > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:uGwr5LpmHHA.1340@TK2MSFTNGP04.phx.gbl... >> Any big difference between the real value that should be returned and the >> one that your beta tester is getting? >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: sylvain aei ca (fill the blanks, no spam please) >> >> >> "Steve Zimmelman" <skz@charter.nospam.net> wrote in message >> news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... >>> Hello, >>> >>> I am using a this procedure to insert a record and return the value of >>> the newly inserted record's ID. ID is an Int Identity field and PK. >>> >>> ***************** >>> Create Procedure [dbo].[NewResRxOrder_SP] >>> @ResID int, >>> @AdmitDateID int >>> As >>> Insert Into [ResRxOrders] >>> ([ResID], >>> [AdmitDateID]) >>> Values >>> (@ResID, >>> @AdmitDateID) >>> /*** Return New Int ID [ID] ***/ >>> Select SCOPE_IDENTITY() As NewID >>> *************** >>> >>> One of my beta testers is getting a value returned that is a already >>> exists in the table; and when the value is used in another relational >>> table, it crashes with a constraint error (as it should). >>> >>> I have used DBCC DBREINDEX on all the tables, but the error persists. >>> >>> I have never seen SQL Server do this, especially with a single install >>> on a computer for testing. >>> >>> Anyone have any ideas how to fix this and/or prevent it? >>> We are using SQL Server 2000. >>> >>> TIA, >>> >>> -Steve- >>> >> >> > >
[quoted text, click to view]
> Is this beta tester using the same database as everyone else or is he using a It's private copy installed on a laptop.> private copy? [quoted text, click to view] > Did you try to force a recompilation of the stored procedures (or even better, No. But I guess that's a next step. Although these SP's haven't changed for > to clear all caches: DBCC > FREEPROCCACHE, DBCC DROPCLEANBUFFERS). months. [quoted text, click to view] > Finally, are you using things like « SET IDENTITY_INSERT table ON » that could No, I don't use these statements anywhere.> have stick on? Does anything change is you set a « SET IDENTITY_INSERT table > OFF » at the beginning of the SP? [quoted text, click to view] > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: sylvain aei ca (fill the blanks, no spam please) > > > "Steve Zimmelman" <skz@charter.nospam.net> wrote in message > news:OycIRg6mHHA.588@TK2MSFTNGP06.phx.gbl... >> Yes. The value that seems to getting stuck is 302. 302 exists in the table >> and was added back in April. The number returned number should be around >> 340. >> >> I should add we are using ADO with 1 connection object and placing a single >> transaction around this SP and other updates that are related to this insert. >> >> What's strange is that the crash doesn't occur until the value that is >> returned by NewResRxOrder_SP is used to create a related record in another >> table. And the returned value is clearly a duplicate. >> >> -Steve- >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote >> in message news:uGwr5LpmHHA.1340@TK2MSFTNGP04.phx.gbl... >>> Any big difference between the real value that should be returned and the >>> one that your beta tester is getting? >>> >>> -- >>> Sylvain Lafontaine, ing. >>> MVP - Technologies Virtual-PC >>> E-mail: sylvain aei ca (fill the blanks, no spam please) >>> >>> >>> "Steve Zimmelman" <skz@charter.nospam.net> wrote in message >>> news:%23Yp4dXmmHHA.3496@TK2MSFTNGP03.phx.gbl... >>>> Hello, >>>> >>>> I am using a this procedure to insert a record and return the value of the >>>> newly inserted record's ID. ID is an Int Identity field and PK. >>>> >>>> ***************** >>>> Create Procedure [dbo].[NewResRxOrder_SP] >>>> @ResID int, >>>> @AdmitDateID int >>>> As >>>> Insert Into [ResRxOrders] >>>> ([ResID], >>>> [AdmitDateID]) >>>> Values >>>> (@ResID, >>>> @AdmitDateID) >>>> /*** Return New Int ID [ID] ***/ >>>> Select SCOPE_IDENTITY() As NewID >>>> *************** >>>> >>>> One of my beta testers is getting a value returned that is a already exists >>>> in the table; and when the value is used in another relational table, it >>>> crashes with a constraint error (as it should). >>>> >>>> I have used DBCC DBREINDEX on all the tables, but the error persists. >>>> >>>> I have never seen SQL Server do this, especially with a single install on a >>>> computer for testing. >>>> >>>> Anyone have any ideas how to fix this and/or prevent it? >>>> We are using SQL Server 2000. >>>> >>>> TIA, >>>> >>>> -Steve- >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |