Regarding auto increment of id in sql table

I currently working asp.net with c# and inserting data from webpage into sqlserver 2000 data table. I have an auto increment ID in a table. Let's say 10 are inserted. Then you delete those 10. You add another and the auto increment sets the id at 11. How can I get that table to start back to 0? Plz help me and give code for increment id programmatically using c#. Thanks in Adv,

if you execute Delete * from yourTbale then you will see what are seeing, Use Truncate instead of delete, eg, Truncate table yourTableName (this will delete all the rows from your table)

|||

I reset the autoincrement with this but I've never tried running it from a stored procedure.

DBCC CHECKIDENT(MyTempTable1,RESEED, 0)

You might have to go this route. "EXEC ('DBCC CHECKIDENT(MyTempTable1,RESEED,0)')"

Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

Referential Integrity - Which Column violates this?

Hi All,

I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.

Any one can shed some light here? Many thanks!

CyherusDo you have any ddl that's behind the VB application? Can you match it with the values you want to insert?|||nope, I am basically connecting to the SQL server via odbc.. and using INSERT INTO queries to add records as I read from a text file..
Since the SQL Server is able to throw me an error description that says which table and column name violates the RI, I thought I would be able to manage this with codes. That is create the primary record in the primary table (affected table) and resume to insert the record in the foreign table again..

Cyherus|||It should be done with sp, not with FE-based action queries.|||The text of the error message (in the errors collection) will tell you which foreign key caused the problem. The code is the same for them all, so it only tells you that a foreign key was the problem.

-PatP|||If you do it in sp you can customize the way errors are returned to the client. And of course Errors (rdoErrors) collection should be looped to retrieve ALL the errors that came from the server.|||Alright.. as per your advise.. I am now performing these actions on the BE via sp.
now, I am using both return value, out parameters and capture @.@.ERROR.
Issue is that the system it prompting me before I can capture the errors returned.. sp example:

ALTER PROCEDURE dbo.sp_insert_bl
(
@.AAA varchar(12),
@.BBB int,
)
AS
DECLARE @.err int

SET NOCOUNT ON
BEGIN TRAN
INSERT INTO dbo.Table ([AAA], [BBB])
VALUES (@.AAA, @.BBB)
SELECT @.err = @.@.ERROR
IF @.err <> 0
BEGIN
ROLLBACK TRAN
RETURN @.err
END
ELSE
BEGIN
SELECT @.blid = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END

oh yes, using ado to execute exec the sp
cmd.parameter.........
cmd.execute
cmd.parameter("return value")

do you suggest using RAISERROR?|||Of course, so that you can interrogate Errors collection. How else were you planning to see the errors?

reeeeeally easy for you guys!

can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advancecreate table tablename2(
id int IDENTITY (1, 1),
value varchar(100)
)

CREATE TRIGGER Trigger ON TableName
FOR INSERT
AS
declare @.value1 varchar(100)
select @.value1=columnName from inserted

insert into tablename2 values (@.value1)

Originally posted by simple_simon
can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.

so that I can edit it to suit my own work like:

eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.

I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance|||I think I follow this, but my equivalent of identity and value are in different tables. I don't think I can apply this,

Which is the part of the code to increment new value by +1 of the last?|||Which is the part of the code to increment new value by +1 of the last?

The column 'id' is created as an IDENTITY field, which means the value in this column is automatically entered. It is set to start with 1 and increment by 1 each time a new record is inserted. Notice that the INSERT statement inserts data into the 'value' column only, the correct incremented integer for the 'id' column is calculated and inserted automaticlly.

This will work if you are creating new tables, if you are working with existing tables and data you would either have to ALTER the table or calculate the new value for 'id' yourself. Take a look at the following code. (I changed the name of your person_no column to people_id, standard naming conventions are a good habit to get into) The trigger will find the maximum current pay_id, increment it by 1, then insert it and the new people_id into the pay table

use northwind
go

CREATE TABLE people
(people_id int)
go

CREATE TABLE Pay
(pay_id int,
people_id int)
go

CREATE TRIGGER tr_insert_people ON people
FOR INSERT
AS
declare @.new_pay_id int
declare @.people_id int

select @.new_pay_id = ISNULL(max(pay_id),0) + 1 from pay
select @.people_id = people_id from inserted

insert into pay
(pay_id,people_id)
values
(@.new_pay_id,@.people_id)
go

INSERT INTO people
VALUES (1)

INSERT INTO people
VALUES (2)

INSER INTO people
VALUES (5)

SELECT * from people
SELECT * from pay

DROP TABLE people
DROP TABLE pay|||and what do you suppose will happen when you have this:

INSERT INTO people
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3

...or any other set based operation?