If my ASP.Net application crashes just after HOLDLOCK is issued?

Will the database get locked if my ASP.Net application that is calling a stored procedure in which a HOLDLOCK for table1 is issued to SQL Server, suddenly crashes just after the stored procedure is called?

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

|||So, it seems that thesafestpractice as far as preventing SQL Server being held up in above scenario, is to provide rollbacks/commits inside stored proecedures rather than in ASP.Net code through ADO.Net.Is that correct?|||

Is it safest? Yes.

However, like I said, I haven't tested myself to see how long it will take SQL Server to detect a dead connection. It could be seconds, it could be hours. And it only really makes a difference if you are running in a web-farm environment, or you have other applications (that don't also run on the web server machine) that need access to the table data in a quick manner -- AND you can't tolerate the database to be down should a machine totally crash (Which is pretty darn rare). I would suggest that if you are deploying to such an environment you test this out yourself.

if condition with stored procedure

i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @.id or order = @.fdfdfd.

i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.First of all, i am not clear with your question.

If you want to insert based on some condition, then specify the insert statment inside an if statement.

Regards
Ravi

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2
.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:

> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Tabl
e2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:

> Hi,
> Some thing new
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off throu
gh
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:

>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table
2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:

>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:

> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...[vbcol=seagreen]
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>|||Lines: 24
Organization: Posted via Supernews, http://www.supernews.com
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:10634
On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:

>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
Hugo Kornelis, SQL Server MVP

Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new :)
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:
> Hi,
> Some thing new :)
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off through
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> > Hi
> >
> > I am facing a big problem. please help.
> > We r having SQL Server 2000 Enterprise.
> >
> > I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> > delete.
> > These triggers are inserting the audit for these actions into another
> > Table2.
> > Table2 has an identity column which increment by 1 automatically.
> >
> > Problem:
> > Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> > As identity column increases automatically, I am helpless.
> >
> > Is there any patch/service pack for this.
> >
> > Please Suggest.
> >
> >
> > --
> > Regards
> > Sameer Gupta
> > C# Designer & Developer
> > Siemens
> > Bracknell
> > UK
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:
>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
--
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:
> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
> >Thanks Hari for ur prompt reply.
> >
> >ya there r some possibilities of rollbacks.
> >i really appreciate ur guess.
> >Can u please explain what happens to a identity column & its values when
> >rollback comes into picture?
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>> >Thanks Hari for ur prompt reply.
>> >
>> >ya there r some possibilities of rollbacks.
>> >i really appreciate ur guess.
>> >Can u please explain what happens to a identity column & its values when
>> >rollback comes into picture?
>> Hi Sameer,
>> Since the identity value generation is performed outside of the
>> transaction context, the values generated for the inserts that were
>> rolled back are not re-used. See the repro below.
>> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
>> OtherCol int NOT NULL CHECK (OtherCol > 0));
>> -- First row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (1);
>> -- Second row explicitly rolled back
>> BEGIN TRAN;
>> INSERT INTO Demo (OtherCol) VALUES (2);
>> ROLLBACK TRAN;
>> -- Third row implicitly rolled back because constraint is violated
>> INSERT INTO Demo (OtherCol) VALUES (-3);
>> -- Fourth row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (4);
>> -- Check results
>> SELECT * FROM Demo;
>> DROP TABLE Demo;
>>
>> --
>> Hugo Kornelis, SQL Server MVP|||On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:
>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
--
Hugo Kornelis, SQL Server MVP|||Thanks Mr. Hugo, Mr. Hari & Mr. Roger for ur kind suggestions.
I am fully able diagnose what's happening. I just come out of a potential
turn around for the issue.
Thanks very much. I am grateful for ur prompt replies.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UK

Identity

In a table(say table1) I have a identity column(say Col1).I want to remove the identity property of the Col1 but int property will remain same.
How can I do it by Query Analyzer?
Subhasishcreate new table without identity col , copy the data , drop the old table and rename the new table to the old table name :mad:

Identity

Suppose I have a table named table1 which has a identity field named "Col1".Now i want to have a backup of this table by running this script
select * into table1_backup from table1
I get the backup in the table table1_backup but i miss the identity property for the field col1.
My question is
1) How can I get the identity property by running that script?
2)How can i have the constraints of table1 in table1_backup?
SubhasishLooks like your trying to make an exact copy of the original table including the identity seed?

To import the identity turn on identity insert like so:

Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Brent|||Thanks Bren.
But what is about my second question?|||Brent
This script will not work
Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Because the table isgetting created in the second stape(Select * into table1_backup from table1)
So before creating the table how can it's IDENTITY_INSERT property set to on or off?
Subhasish|||Sorry bout that, this one should work,, takes awhile longer as you need to define the datatypes for each column such as col1 int, col2 varchar(20)

Create Table table1_backup (col1 col1type, col2 col2type, etc)

Set IDENTITY_INSERT table1_backup ON
Insert into table1_backup (col1, col2, etc)
select col1, col2, etc
from table1
Set IDENTITY_INSERT table1_backup OFF

As for the second question, not sure off the top of my head on importing contraints. I'll look around, but best bet would be to put that question in a new thread here in dbforums.

Brent

Originally posted by subhasishray
Brent
This script will not work
Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Because the table isgetting created in the second stape(Select * into table1_backup from table1)
So before creating the table how can it's IDENTITY_INSERT property set to on or off?
Subhasish