Database status: Loading

After restoring my old transaction logs, the database
showing a status: 'Loading'. The database status still
remain 'Loading' even after 3 days.
Is there anyway I can stop the loading and will it cause
any problem to my data?Perhaps the last log was restored with NORECOVERY. In this case, you
can recover the database with the command below. This will not
introduce any data problems.
RESTORE DATABASE MyDatabase
WITH RECOVERY
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"TEO" <teo.yongjiew@.eonbank.com.my> wrote in message
news:067501c3a80b$73cf54e0$a401280a@.phx.gbl...
> After restoring my old transaction logs, the database
> showing a status: 'Loading'. The database status still
> remain 'Loading' even after 3 days.
> Is there anyway I can stop the loading and will it cause
> any problem to my data?sql

Database sql server2000 very slow

I have a database that had worked under msde 1.0 until reached the 2GB
of dimension and the dB was blocked. To make possible the work i had
deleted old data from some table. The database restart, but the
answers from server become very slow. So i decided to pass to Sql
Server 2000 without success. May i perform a check of this database?
if is a indexs problem there is a way to rebuild them?
Thanks in advance
Andy Wet"Andy Wet" <andy_wet@.yahoo.it> wrote in message
news:cbd71cd0.0311040107.31de9ec5@.posting.google.c om...
> I have a database that had worked under msde 1.0 until reached the 2GB
> of dimension and the dB was blocked. To make possible the work i had
> deleted old data from some table. The database restart, but the
> answers from server become very slow. So i decided to pass to Sql
> Server 2000 without success. May i perform a check of this database?
> if is a indexs problem there is a way to rebuild them?
> Thanks in advance
> Andy Wet

Can you explain what you mean by 'passing to SQL Server 2000'? Did you try
to upgrade MSDE to Standard Edition, did you backup/restore your MSDE
database on another version, etc.?

It might be worth using sp_updatestats to update the statstics in the
database, and also DBCC CHECKDB, if you think the database may be corrupted.

Simon

Database recovery freezing server?

I'm currently running Windows Server 2003 and just recently installed SQL 2005. I had a few databases that I was attaching from the old SQL 2000 server. I encountered a hard error and when I restarted Management studio, I found 4 of my databases were in recovery. Thinking this would be a short process, I let it run and did work on one of my other machines. Nearly 2 hours later, the databases are still in recovery and nothing appears to have changed. I'm new to sql 2005 and don't know what the best method of bringing those databases back up would be. If it is possible to just detach the databases (or blow them away completely) I have backups that are plenty recent enough, however I find I am unable to make any changes to the databases while one of the system db's is in recovery. Any ideas?

KCanterucci,

I think you can find more accurate answer in sql server engine forum.

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.
If the transaction has NOT YET committed, but you are in code AFTER the
insert, you will get good results from @.@.error @.@.identity. But then later in
the same transaction, you are aborted the row will be removed...A deadlock
error would look like this, a batch abort would look like this also...
Use Profiler to see if the transaction is being aborted... Also look for
Erland Sommerskog on Google.. His web site has the best information availabe
about aborts...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1au$bh4@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.
please show us your exact stored procedure code.
thanks.
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1an$qp31@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>

Database record disappear

I has a strange question.

My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY?? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times."salamol" <salamol@.hotmail.com> wrote in message
news:chm1a4$bb2@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY?? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.

My guess is something is causing the transaction to rollback. Best bet is
to post the code, DDL and repro script if at all possible.

Remember, @.error gets set after every statement, not just at the end.
|||On Wed, 8 Sep 2004 12:20:12 +0800, salamol wrote:

>I has a strange question.
>My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
>The system is busy and handle a lot of SELECTs and INSERTs all the time.
>Sometimes, some transactions are blocked by some other transactions.
>For those INSERT transactions, we usually call a stored procedure and pass
>parameters in to do the INSERT, at the end of the INSERT stored procedure,
>we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
>auto number primary key) to confirm the INSERT is success. But the strange
>things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
>@.@.IDENTITY but when I fetch the record by the primary key (the value of
>@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
>key is skipped! I found that this happens usually when the INSERT execute
>at the time when some other transactions are blocking. Anyone knows why the
>record is disappear while @.ERROR = 0 and the stored procedure can return
>value from @.@.IDENTITY?? Anyone has such case happen in their server as
>well? Please tell me some solutions on how to solve this, thank you x
>10000000 times.

Hi salamol,

If you were using SQL Server 2000, I'd advise you to use SCOPE_IDENTITY
instead. Unfortunately, @.@.IDENTITY gives you the last identity value used,
not limited to the current scope. I guess the scenario is something like
this:

* Connection 1 starts an insert.
* While connection 1 is processing, connection 2 initiates an insert as
well. This one is blocked.
* When connection 1's insert is finished, connection 2's insert starts.
* When processing of connection 1 reaches the statement where @.@.IDENTITY
is retrieved, the insertion of connection 2 is already finished and the
@.@.IDENTITY value returned to connection 1 will be for conenction 2's row.
* After that, connection 2 has to do a rollback. There now no longer is a
row with the @.@.IDENTITY value saved by conneciton 1 (that actually
belonged to connection 2).

Since you don't have SQL Server 2000, you can't use SCOPE_IDENTITY
instead. Another alternative is to read back the data. Your table should
have a column (or combination of columns) that form the natural key for
the data in the table. Use this in a WHERE clause to SELECT the identity
value assigned to the row just inserted.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> * Connection 1 starts an insert.
> * While connection 1 is processing, connection 2 initiates an insert as
> well. This one is blocked.
> * When connection 1's insert is finished, connection 2's insert starts.
> * When processing of connection 1 reaches the statement where @.@.IDENTITY
> is retrieved, the insertion of connection 2 is already finished and the
> @.@.IDENTITY value returned to connection 1 will be for conenction 2's row.

Sorry Hugo, but this is plain wrong. @.@.identity is global to the connection,
not global to the server. This cannot happen.

What can happen is that you insert a row into a table and that table has
a trigger which also inserts a row into a table with the IDENTITY property.
In this case @.@.identity will return the value of the row into the second
table, and you have no good way of getting the value for the first table.

In SQL7 RTM there was also a bug which, if memory serves, caused @.@.identity
to be NULL, if your trigger inserted into a non-identity table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||salamol (salamol@.hotmail.com) writes:
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions. For
> those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored
> procedure, we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it
> is usually the auto number primary key) to confirm the INSERT is
> success. But the strange things is, sometimes, @.@.ERROR is equals to 0
> and I can get a value from @.@.IDENTITY but when I fetch the record by the
> primary key (the value of @.@.IDENTITY) it returns nothing!! The record
> is disappear and the primary key is skipped! I found that this happens
> usually when the INSERT execute at the time when some other transactions
> are blocking. Anyone knows why the record is disappear while @.ERROR = 0
> and the stored procedure can return value from @.@.IDENTITY?? Anyone has
> such case happen in their server as well? Please tell me some solutions
> on how to solve this, thank you x 10000000 times.

There is just far too little information to say something really useful.

But just checking @.@.error and @.@.identity is not enough. If you have
transactions they can be rolled back, for instance because of deadlock.

Also, a nasty thing which may happen in a busy system which is not properly
implemented is that blocking leads to clients timing out. Now, assume that
a client calls a stored procedure, which starts a transaction, and after
30 seconds the default timeout of the client sets in, so the query is
cancelled. This does *not* rollback the transaction! This means that the
client may job along with the transaction and everything looks fine -
and then, there is a deadlock or some other error which cancels the batch,
and everything since 10 o'clock this morning is rolled back.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The problem I am facing is neither cannot get an identity (identity not
null) nor getting a wrong identity.
the server returns a correct identity value which should belongs to the
record that I just insert, but when I fetch the record by that identity
value, the record is missing
i.e. the identity for the last record is 100, and I do an insert, the system
return 101 as @.@.identity and 0 as @.@.Error, however if i select the record
from 95 - 105 it will returns records 95, 96, 97, 98, 99, 100, 102, 103,
104, 105 (i.e. 102-105 is other successfully insert after 101)

"Erland Sommarskog" <esquel@.sommarskog.se> ?
news:Xns955F4D9B18C7Yazorman@.127.0.0.1 ?...
> Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> > * Connection 1 starts an insert.
> > * While connection 1 is processing, connection 2 initiates an insert as
> > well. This one is blocked.
> > * When connection 1's insert is finished, connection 2's insert starts.
> > * When processing of connection 1 reaches the statement where @.@.IDENTITY
> > is retrieved, the insertion of connection 2 is already finished and the
> > @.@.IDENTITY value returned to connection 1 will be for conenction 2's
row.
> Sorry Hugo, but this is plain wrong. @.@.identity is global to the
connection,
> not global to the server. This cannot happen.
> What can happen is that you insert a row into a table and that table has
> a trigger which also inserts a row into a table with the IDENTITY
property.
> In this case @.@.identity will return the value of the row into the second
> table, and you have no good way of getting the value for the first table.
> In SQL7 RTM there was also a bug which, if memory serves, caused
@.@.identity
> to be NULL, if your trigger inserted into a non-identity table.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 8 Sep 2004 22:30:07 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> * Connection 1 starts an insert.
>> * While connection 1 is processing, connection 2 initiates an insert as
>> well. This one is blocked.
>> * When connection 1's insert is finished, connection 2's insert starts.
>> * When processing of connection 1 reaches the statement where @.@.IDENTITY
>> is retrieved, the insertion of connection 2 is already finished and the
>> @.@.IDENTITY value returned to connection 1 will be for conenction 2's row.
>Sorry Hugo, but this is plain wrong. @.@.identity is global to the connection,
>not global to the server. This cannot happen.
>What can happen is that you insert a row into a table and that table has
>a trigger which also inserts a row into a table with the IDENTITY property.
>In this case @.@.identity will return the value of the row into the second
>table, and you have no good way of getting the value for the first table.
>In SQL7 RTM there was also a bug which, if memory serves, caused @.@.identity
>to be NULL, if your trigger inserted into a non-identity table.

Hi Erland,

Thanks, Erland, for putting me straight. And apologies to the OP for the
confusion I may have caused. I guess it shows that I hardly use IDENTITY
in my own code...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||salamol (salamol@.hotmail.com) writes:
> The problem I am facing is neither cannot get an identity (identity not
> null) nor getting a wrong identity. the server returns a correct
> identity value which should belongs to the record that I just insert,
> but when I fetch the record by that identity value, the record is
> missing i.e. the identity for the last record is 100, and I do an
> insert, the system return 101 as @.@.identity and 0 as @.@.Error, however if
> i select the record from 95 - 105 it will returns records 95, 96, 97,
> 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> insert after 101)

If you have received 101 in @.@.identity for an inserted row, and the row
is later nowhere to be found, the insertion of the row has been rolled
back, as I discussed in my other post.

While you may already be aware of this, I like to point out that it lies
in the concept of the IDENTITY property that you never can trust the numbers
to be contiguous. If you attempt to insert a row into a table, and that
insertion fails for whatever reason, you have still consumed a number.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, so how can I check if the insertion is success or not?
should I fetch for that record again immediate after the insert?

"Erland Sommarskog" <esquel@.sommarskog.se> ?
news:Xns955F72B0A42D0Yazorman@.127.0.0.1 ?...
> salamol (salamol@.hotmail.com) writes:
> > The problem I am facing is neither cannot get an identity (identity not
> > null) nor getting a wrong identity. the server returns a correct
> > identity value which should belongs to the record that I just insert,
> > but when I fetch the record by that identity value, the record is
> > missing i.e. the identity for the last record is 100, and I do an
> > insert, the system return 101 as @.@.identity and 0 as @.@.Error, however if
> > i select the record from 95 - 105 it will returns records 95, 96, 97,
> > 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> > insert after 101)
> If you have received 101 in @.@.identity for an inserted row, and the row
> is later nowhere to be found, the insertion of the row has been rolled
> back, as I discussed in my other post.
> While you may already be aware of this, I like to point out that it lies
> in the concept of the IDENTITY property that you never can trust the
numbers
> to be contiguous. If you attempt to insert a row into a table, and that
> insertion fails for whatever reason, you have still consumed a number.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||salamol (salamol@.hotmail.com) writes:
> OK, so how can I check if the insertion is success or not?
> should I fetch for that record again immediate after the insert?

I have to ask a question: have you read my discussion about transaction
being rolled back? You seem to be obsessed with the problem that the
insertions may fail. But if insertion is successful does not help, if
the transaction is rolled back later.

It is difficult to add more to the subject, unless you show us some of the
code you are having problems with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
Im using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The users app connects with BDE but i coded a newer version with ADO
but this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks
errors arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like
memory+network+diskIO and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite)
a new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY' Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.If the transaction has NOT YET committed, but you are in code AFTER the
insert, you will get good results from @.@.error @.@.identity. But then later in
the same transaction, you are aborted the row will be removed...A deadlock
error would look like this, a batch abort would look like this also...
Use Profiler to see if the transaction is being aborted... Also look for
Erland Sommerskog on Google.. His web site has the best information availabe
about aborts...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1au$bh4@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY' Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>