If drive D is hidden path when install sql server, can i still see?

Hi,
I can not see my d drive in the enterprise manager. My d drive is a
hidden path when i install sql server but i am administrator and i have
right to view the d drive. D drive is a harddisk drive. Even now i make the
d drive not hidden, i still can not see th path. Can anyone help me?
Thanks a lot
regards,
florenceDid you try just typing it in and hitting "next"? What happened?
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
> Thanks a lot
> regards,
> florence
>|||Hi,
Sorry...maybe i let you confuse...i have successfully install the sql
server into my c: drive..but later if i want to do backup or anything
else...i can not see my d drive.
--
Thanks a lot!
regards,
florencelee
"ChrisR" <chris@.noemail.com> wrote in message
news:uKAUTJJwEHA.3088@.TK2MSFTNGP12.phx.gbl...
> Did you try just typing it in and hitting "next"? What happened?
>
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > I can not see my d drive in the enterprise manager. My d drive is a
> > hidden path when i install sql server but i am administrator and i have
> > right to view the d drive. D drive is a harddisk drive. Even now i make
> the
> > d drive not hidden, i still can not see th path. Can anyone help me?
> >
> > Thanks a lot
> >
> > regards,
> >
> > florence
> >
> >
>|||Florencelee wrote:
> Hi,
> Sorry...maybe i let you confuse...i have successfully install the sql
> server into my c: drive..but later if i want to do backup or anything
> else...i can not see my d drive.
>
> florencelee
Why is drive D hidden? Even so, directly accessing the drive should
work. How are you running backup?
--
David Gugick
Imceda Software
www.imceda.com|||It doesn't matter if you are an administrator or not. What matters is what
the MSSQL service account has, or does not have, access to.
If you make any changes to security, you will have to recycle the MSSQL
service. This will force the service account to log out and back into the
server, thus acquiring any new permissions and or settings.
Nevertheless, David is correct, if the appropriate permissions are set, but
just haven't been recognized by the SQL Server, it does not matter if the
server "sees" it or not, you should still be able to "specify" the path and
have it work.
Just because the GUI is not behaving like you'd wish, does not mean you can
not get the job done. You should be using scripts more often anyway. It is
easier to track and control your activities. SQLEM is for newbies cutting
their teeth as they ween themselves from MS Access.
Sincerely,
Anthony Thomas
"David Gugick" wrote:
> Florencelee wrote:
> > Hi,
> >
> > Sorry...maybe i let you confuse...i have successfully install the sql
> > server into my c: drive..but later if i want to do backup or anything
> > else...i can not see my d drive.
> >
> >
> > florencelee
>
> Why is drive D hidden? Even so, directly accessing the drive should
> work. How are you running backup?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
Is the D: drive a local drive or a mapped drive?
> Thanks a lot
> regards,
> florence
>|||Hi,
Thanks for reply. Normally when i backup i will seelect device and
want to choose the drive d. But i can not see drive d. I have check my sql
server service and it use local system account and i always log in to my
server use administrator access right. Can you teach me where i can check ?
For eg. security checking?
--
Thanks a lot!
regards,
florencelee
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:AB75247D-A42E-41F7-85A3-A969F2DA19D6@.microsoft.com...
> It doesn't matter if you are an administrator or not. What matters is
what
> the MSSQL service account has, or does not have, access to.
> If you make any changes to security, you will have to recycle the MSSQL
> service. This will force the service account to log out and back into the
> server, thus acquiring any new permissions and or settings.
> Nevertheless, David is correct, if the appropriate permissions are set,
but
> just haven't been recognized by the SQL Server, it does not matter if the
> server "sees" it or not, you should still be able to "specify" the path
and
> have it work.
> Just because the GUI is not behaving like you'd wish, does not mean you
can
> not get the job done. You should be using scripts more often anyway. It
is
> easier to track and control your activities. SQLEM is for newbies cutting
> their teeth as they ween themselves from MS Access.
> Sincerely,
>
> Anthony Thomas
>
> "David Gugick" wrote:
> > Florencelee wrote:
> > > Hi,
> > >
> > > Sorry...maybe i let you confuse...i have successfully install the sql
> > > server into my c: drive..but later if i want to do backup or anything
> > > else...i can not see my d drive.
> > >
> > >
> > > florencelee
> >
> >
> > Why is drive D hidden? Even so, directly accessing the drive should
> > work. How are you running backup?
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >

If drive D is hidden path when install sql server, can i still see?

Hi,
I can not see my d drive in the enterprise manager. My d drive is a
hidden path when i install sql server but i am administrator and i have
right to view the d drive. D drive is a harddisk drive. Even now i make the
d drive not hidden, i still can not see th path. Can anyone help me?
Thanks a lot
regards,
florence
Did you try just typing it in and hitting "next"? What happened?
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
> Thanks a lot
> regards,
> florence
>
|||Hi,
Sorry...maybe i let you confuse...i have successfully install the sql
server into my c: drive..but later if i want to do backup or anything
else...i can not see my d drive.
Thanks a lot!
regards,
florencelee
"ChrisR" <chris@.noemail.com> wrote in message
news:uKAUTJJwEHA.3088@.TK2MSFTNGP12.phx.gbl...
> Did you try just typing it in and hitting "next"? What happened?
>
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> the
>
|||Florencelee wrote:
> Hi,
> Sorry...maybe i let you confuse...i have successfully install the sql
> server into my c: drive..but later if i want to do backup or anything
> else...i can not see my d drive.
>
> florencelee
Why is drive D hidden? Even so, directly accessing the drive should
work. How are you running backup?
David Gugick
Imceda Software
www.imceda.com
|||"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
Is the D: drive a local drive or a mapped drive?

> Thanks a lot
> regards,
> florence
>

If drive D is hidden path when install sql server, can i still see?

Hi,
I can not see my d drive in the enterprise manager. My d drive is a
hidden path when i install sql server but i am administrator and i have
right to view the d drive. D drive is a harddisk drive. Even now i make the
d drive not hidden, i still can not see th path. Can anyone help me?
Thanks a lot
regards,
florenceDid you try just typing it in and hitting "next"? What happened?
"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
> Thanks a lot
> regards,
> florence
>|||Hi,
Sorry...maybe i let you confuse...i have successfully install the sql
server into my c: drive..but later if i want to do backup or anything
else...i can not see my d drive.
Thanks a lot!
regards,
florencelee
"ChrisR" <chris@.noemail.com> wrote in message
news:uKAUTJJwEHA.3088@.TK2MSFTNGP12.phx.gbl...
> Did you try just typing it in and hitting "next"? What happened?
>
> "Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
> news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> the
>|||Florencelee wrote:
> Hi,
> Sorry...maybe i let you confuse...i have successfully install the sql
> server into my c: drive..but later if i want to do backup or anything
> else...i can not see my d drive.
>
> florencelee
Why is drive D hidden? Even so, directly accessing the drive should
work. How are you running backup?
David Gugick
Imceda Software
www.imceda.com|||"Florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:e24bBFJwEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I can not see my d drive in the enterprise manager. My d drive is a
> hidden path when i install sql server but i am administrator and i have
> right to view the d drive. D drive is a harddisk drive. Even now i make
the
> d drive not hidden, i still can not see th path. Can anyone help me?
Is the D: drive a local drive or a mapped drive?

> Thanks a lot
> regards,
> florence
>sql

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 column

Hi all,
I backup database in Enterprise Manager (All Task->backup).
but after restoring (into another db server), all identity columns are
set to "NO" :(.
what should I do to reserve "YES" in identity columns?
should I use T-SQL or do I miss something?
thanks
anthonyMy first thought is that this is not possible.
Are you sure you did a backup/restore rather than a copy
via dts or something.
How are you looking at the identity property?
>--Original Message--
>Hi all,
>I backup database in Enterprise Manager (All Task-
>backup).
>but after restoring (into another db server), all
identity columns are
>set to "NO" :(.
>what should I do to reserve "YES" in identity columns?
>should I use T-SQL or do I miss something?
>thanks
>anthony
>.
>