RESOLVED - Help with SQL Query - "The multi-part identifier "alias field Name" co

Hi Everyone
This is the query and I am getting follwoing error message

"The multi-part identifier "InvDate.Account Reference" could not be bound."

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC

By the way its SQL Server 2005 Environment.
Mitesh
Well how about getting rid of:
- (select * from customer) -- just use a simple join to customer
- get rid of the collate statements in your where clauses.

Also, you'll need to add INVDATE.[LASTORDERDATE] to your group by statement.

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]

FROM CUSTOMERCONTACTNOTES AS CCN,

CUSTOMER AS CC,

(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]

GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE]
ORDER BY CC.COMPANY ASC|||Thanks Phill,

Your solution was just spot on.

Just out of curosity, how do you read any SQL Query, for e.g. like mine and find what is wrong.

Mitesh|||Experience, I guess. When you work with it enough, you can just "read" SQL and understand what's going on.

I really don't think you need the "select distinct [account reference] from invoicedata" query in your where clause though. You already have a distinct list from the INVDATE query in your FROM statement. Your where clause should probably be:

WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] = INVDATE.[ACCOUNT REFERENCE]

Resetting the Identity field

I have a composite pk in a table 'table' in ms sql server. value in one field 'table.a' is fk to another table 'table1.a'
value in field table.b is a id field. i need to reset this field 'table.b' to 1 each time the 'table.a' changes.

Any suggestions.Not sure what you mean.
if table.a is part of the primary key it should never change otherwise it shouldn't be part of the primary key.
It sounds like you might want a trigger but maybe you could post an example.|||i see what you mean. I have changed it and i have a field table1.a and table1.b. both .a and .b are not in the keys, though .a is a fk to table2.a.
i need to increment .b by 1 on each input of .a where .a = 'x' (say). as soon as .a = 'y' (say) i need to reset .b to 0 and auto increment as new values for .a='y' are inserted.
hope this makes sense.
thanks|||ok
you have table1(a,b)
a is an id and you want b to be the sequence number within a?

put a trigger on the table

create trigger tr_table1_ins on table1 for insert
as
set rowcount 1
while exists(select * from table1 where b is null)
begin
update table1
set b = (select max(b)+1 from table1 t1 where table1.a = t1.a)
where b is null
set rowcount 0
go

if you only ever insert one row at a time then you can just do the update without the loop.

Another option is to put the current value for b on table2 and increment it within a transaction on inserts and use it with the insert.|||We've done something like this at our site. When we needed to know the occurence of a record, example "2 of 5". We implemented a TRIGGER like nigelrivett has suggested. To use a trigger you should JOIN with the INSERTED table to update only those records that were Inserted.

SET NOCOUNT ON
GO
CREATE
TABLE Occurrence
(
syID int IDENTITY (1, 1) NOT NULL ,
colA char(3),
colB int NOT NULL DEFAULT 0
)
GO
CREATE
TRIGGER tri_Occurrence
ON Occurrence
FOR Insert
AS

--
-- If no records were effected then return
--
IF (@.@.ROWCOUNT = 0) BEGIN
RETURN
END

UPDATE o
SET colB = (SELECT MAX(o.colB) + 1 FROM Occurrence o WHERE i.colA = o.colA)
FROM Occurrence o,
Inserted i
WHERE o.syID = i.syID

RETURN
GO

INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('B')
INSERT Occurrence (colA) values ('A')
INSERT Occurrence (colA) values ('C')
INSERT Occurrence (colA) values ('C')
GO

SELECT *
FROM Occurrence

syID colA colB
---- -- ----
1 A 1
2 A 2
3 B 1
4 A 3
5 C 1
6 C 2|||Unfortunately that only works for single row inserts.
And assumes an ID on the table.
Apart from that is the same as my trigger.|||You are one to get the last word in. I'm sorry that I replied to the posting with my answer. I felt that a person could cut and paste this and see a working example.

But I forget that once nigelrivett answers, we should lock the posting, case closed.|||Sorry - just thought I'd point out a problem, which is quite common, with the trigger you posted.

resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1

resetting id values

hi guys i was wondering if anyone could help me, i have a table with a field called id that did have numbers 1,2,3,4,5,6,7,8 and so on! but after some tinkering i have removed a few value and added more so i now have 1, 4,8,19,20 and so on!

i was wondering if i can run a query to update those value and return them to 1,2,3,4,5,6,7,8 and so on?

Cheers

Tupps

This article might help

http://www.juliankuiters.id.au/article.php/sql2000-reset-identity

|||

sorted thanks!! couldnt get an answer! but thats cos i wasnt using the correct term cheers!!

Tupps

resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have an INT field that auto increments by 1, now that I am approaching
the
> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have an INT field that auto increments by 1, now that I am approaching
> the
> > end of the testing phase, I need to delete all the records and reset the
> > "count" to 1.
> >
> > Any help?
> >
> > Mitch
> >
> >
>|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Just truncate the table (or drop and recreate it) since you don't need
the
> > data. Truncate will reset the identity.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I have an INT field that auto increments by 1, now that I am
approaching
> > the
> > > end of the testing phase, I need to delete all the records and reset
the
> > > "count" to 1.
> > >
> > > Any help?
> > >
> > > Mitch
> > >
> > >
> >
> >
>|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...
> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
> > Sorry, I'm kind of new to SQL server, what does "truncate the table"
mean
> > and how do I do it?
> >
> > SQL Server 2000
> >
> > Thanks!
> >
> > Mitch
> >
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
> > > Just truncate the table (or drop and recreate it) since you don't need
> the
> > > data. Truncate will reset the identity.
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "mitchel" <mitch_001@.yahoo.com> wrote in message
> > > news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > >
> > > > I have an INT field that auto increments by 1, now that I am
> approaching
> > > the
> > > > end of the testing phase, I need to delete all the records and reset
> the
> > > > "count" to 1.
> > > >
> > > > Any help?
> > > >
> > > > Mitch
> > > >
> > > >
> > >
> > >
> >
> >
>

resetting count on INT type field (autonumber field)

Hi,
I have an INT field that auto increments by 1, now that I am approaching the
end of the testing phase, I need to delete all the records and reset the
"count" to 1.
Any help?
MitchJust truncate the table (or drop and recreate it) since you don't need the
data. Truncate will reset the identity.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi,
> I have an INT field that auto increments by 1, now that I am approaching

---

the

quote:

---

> end of the testing phase, I need to delete all the records and reset the
> "count" to 1.
> Any help?
> Mitch
>

---

|||Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
and how do I do it?
SQL Server 2000
Thanks!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Just truncate the table (or drop and recreate it) since you don't need the
> data. Truncate will reset the identity.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:ez5tT1U2DHA.2396@.TK2MSFTNGP09.phx.gbl...
> the
>

---

|||In Query Analyzer run the following in your database
TRUNCATE TABLE name
Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"mitchel" <mitch_001@.yahoo.com> wrote in message
news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...

quote:

---

> Sorry, I'm kind of new to SQL server, what does "truncate the table" mean
> and how do I do it?
> SQL Server 2000
> Thanks!
> Mitch
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%23r8XWCV2DHA.2792@.TK2MSFTNGP09.phx.gbl...
the[QUOTE]
approaching[QUOTE]
the[QUOTE]
>

---

|||Thanks!
Worked perfectly!
Mitch
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23703ihV2DHA.1532@.TK2MSFTNGP10.phx.gbl...

quote:

---

> In Query Analyzer run the following in your database
> TRUNCATE TABLE name
> Have a look at TRUNCATE TABLE in BOL (Books on Line - the SQL Server help)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "mitchel" <mitch_001@.yahoo.com> wrote in message
> news:OWOSvYV2DHA.1704@.tk2msftngp13.phx.gbl...
mean[QUOTE]
> the
> approaching
> the
>

---

Reseting the unique id if a table.

I'm building a web site. there is a database.

I've set a primary key and unique of a field.also I set it to auto numbering.

Everytime I insert a record , that field will increase 1 (type bigint , start from 1).

After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

Run query Truncate Table YuorTableName

Reset the Identity Increment

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.

Ryan KennedyCheck out the DBCC CHECKIDENT command in google^h^h^h^h^h^h Books Online|||Also look at the TRUNCATE statement.

"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:53dPb.1926$kH2.252@.nwrdny01.gnilink.net...
> Reset the Identity Increment

Reset Primary ID back to 1

Reset the Identity Increment
------------------------

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.The only way to do that is to use truncate table instead of delete.
You need additional rights to be able to execute truncate table statement.

Good Luck.

Irina.|||Yes, of course you can use truncate. It will delete and reseed the identity columns.
It is also more efficient way to save the resources.

But, if you also want to use delete, you can reset the identity column by running the following command:

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Hope to help.

Reset Identity Question

SQL Server 2000
Is there a way to reset an identity field of an empty field back to one
without createing a temp table and renaming?
TIA
Tim MorrisonDBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
"Tim Morrison" wrote:

> SQL Server 2000
> Is there a way to reset an identity field of an empty field back to one
> without createing a temp table and renaming?
> TIA
> Tim Morrison
>
>|||On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:

>SQL Server 2000
>Is there a way to reset an identity field of an empty field back to one
>without createing a temp table and renaming?
Hi Tim,
DBCC CHECKIDENT, with the RESEED option.
Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
deleting the last row - TRUNCATE TABLE automatically resets the identity
seed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||For some tables this is what i do, but I have several parent-child tables,
and it appears I cannot do a TRUNCATE when there are child tables, even if
they are empty.
Tim Morrison
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qhvu21p88vbenjpop7e92h6pj0fubtrueq@.
4ax.com...
> On Wed, 9 Mar 2005 16:07:35 -0600, Tim Morrison wrote:
>
> Hi Tim,
> DBCC CHECKIDENT, with the RESEED option.
> Another way would be to use TRUNCATE TABLE instead of DELETE FROM when
> deleting the last row - TRUNCATE TABLE automatically resets the identity
> seed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Cool... seems to work... EXCEPT the next record that is inserted has a value
of 2 instead of 1. Its no big deal, I can deal with 2.
Tim Morrison
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
> DBCC CHECKIDENT (<tableNamehere>, RESEED, 1)
>
> "Tim Morrison" wrote:
>|||reseed to 0 instead if you want 1
Simon Worth
"Tim Morrison" <sales_nospam_@.kjmsoftware.com> wrote in message
news:eeK316PJFHA.2852@.TK2MSFTNGP09.phx.gbl...
> Cool... seems to work... EXCEPT the next record that is inserted has a
> value of 2 instead of 1. Its no big deal, I can deal with 2.
> Tim Morrison
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:5FF180CC-BFB3-40FE-B2AC-2FE0F5F5291D@.microsoft.com...
>

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
You can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>
|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.zaYou can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>sql

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.zaYou can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>

Re-set identity field

Hi:

I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?

Can you assist?

Thanks

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

reset Identity

I have an integer field set identity ON. How can I reset the identity number
to 1? Thanks Anyway!
You need to use the DBCC CHECKIDENT command. DBCC CHECKIDENT(TableName,
RESEED, IdentityValue)
ie. To reseed the Identity of the Employees table to 0:
DBCC CHECKIDENT(employees, RESEED, 0)
- Peter Ward
WARDY IT Solutions
"kinloan" wrote:

> I have an integer field set identity ON. How can I reset the identity number
> to 1? Thanks Anyway!
>

reset Identity

I have an integer field set identity ON. How can I reset the identity number
to 1? Thanks Anyway!You need to use the DBCC CHECKIDENT command. DBCC CHECKIDENT(TableName,
RESEED, IdentityValue)
ie. To reseed the Identity of the Employees table to 0:
DBCC CHECKIDENT(employees, RESEED, 0)
- Peter Ward
WARDY IT Solutions
"kinloan" wrote:
> I have an integer field set identity ON. How can I reset the identity number
> to 1? Thanks Anyway!
>

Reset Identity

Hi, there is a way to reset identity field of many tables via storeprocedure
?
i try with the scripts below, but they don't work !
BACKUP LOG test_dbWITH TRUNCATE_ONLY
DBCC shrinkdatabase (test_db)
and also with
create table #table(
idTabella int,
nome varchar(4000)
)
insert into #table
SELECT dbo.sysobjects.id, dbo.sysobjects.name
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id =
dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype =
dbo.systypes.xtype
WHERE (dbo.syscolumns.status = 128)
declare @.NomeTabella as varchar(4000)
declare @.TabellaID int
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
while exists(select idTabella from #table)
begin
DBCC CHECKIDENT(@.NomeTabella, RESEED)
delete from #table where idTabella = @.TabellaID
select @.TabellaID =idTabella,@.NomeTabella =nome from #table
end"Alessandro" schrieb:
> Hi, there is a way to reset identity field of many tables via storeprocedu
re
> ?
> i try with the scripts below, but they don't work !
> BACKUP LOG test_dbWITH TRUNCATE_ONLY
> DBCC shrinkdatabase (test_db)
> and also with
> create table #table(
> idTabella int,
> nome varchar(4000)
> )
> insert into #table
> SELECT dbo.sysobjects.id, dbo.sysobjects.name
> FROM dbo.sysobjects INNER JOIN
> dbo.syscolumns ON dbo.sysobjects.id =
> dbo.syscolumns.id INNER JOIN
> dbo.systypes ON dbo.syscolumns.xtype =
> dbo.systypes.xtype
> WHERE (dbo.syscolumns.status = 128)
> declare @.NomeTabella as varchar(4000)
> declare @.TabellaID int
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> while exists(select idTabella from #table)
> begin
> DBCC CHECKIDENT(@.NomeTabella, RESEED)
> delete from #table where idTabella = @.TabellaID
> select @.TabellaID =idTabella,@.NomeTabella =nome from #table
> end
The follwing procedure reseeds all ID-cols in the db. Tables without an
ID-col return an error that you can ignore ...
declare @.table varchar(256)
declare cu cursor for select [name] from sysobjects where xtype = 'U'
open cu
fetch next from cu into @.table
while @.@.fetch_status = 0
begin
dbcc checkident (@.table, RESEED)
fetch next from cu into @.table
end
close cu deallocate cu

reset Identity

I have an integer field set identity ON. How can I reset the identity number
to 1? Thanks Anyway!You need to use the DBCC CHECKIDENT command. DBCC CHECKIDENT(TableName,
RESEED, IdentityValue)
ie. To reseed the Identity of the Employees table to 0:
DBCC CHECKIDENT(employees, RESEED, 0)
- Peter Ward
WARDY IT Solutions
"kinloan" wrote:

> I have an integer field set identity ON. How can I reset the identity numb
er
> to 1? Thanks Anyway!
>sql

Reset Id field

I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes

The tables have been used for testing while the application was developed.

I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?

Check out Books on line for DBCC CHECKIDENT.

|||

Excellent -thanks for that - searched using those terms and found what I needed.

Regards

Clive

RequestType field in the ExecutionLog table

Hi to all
someone can tell me what the RequestType field in the ExecutionLog table
means?
From some experiments seems that there are 2 values:
0 - Reports executed manually
1 - Reports execution due to a subscription event
there are other values? Is it possibile to know them? I'm tryin with write a
Report that will help to read the ExecutionLog table.
Davide MauriThere are only two values. 0 means that the report was run in the web
service process, 1 means it was run in the reportserver windows service
process.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davide Mauri" <mauri_davide@.libero.it> wrote in message
news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
> Hi to all
> someone can tell me what the RequestType field in the ExecutionLog table
> means?
> From some experiments seems that there are 2 values:
> 0 - Reports executed manually
> 1 - Reports execution due to a subscription event
> there are other values? Is it possibile to know them? I'm tryin with write
a
> Report that will help to read the ExecutionLog table.
> Davide Mauri
>|||Thanx a lot Daniel
just another one question:
can you also tell me what the values in the column "source" mean? i always
have a value of 1 here.
Thanx a lot again & in advance :-)
Davide
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uCVEZBasEHA.1016@.TK2MSFTNGP10.phx.gbl...
> There are only two values. 0 means that the report was run in the web
> service process, 1 means it was run in the reportserver windows service
> process.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Davide Mauri" <mauri_davide@.libero.it> wrote in message
> news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
>> Hi to all
>> someone can tell me what the RequestType field in the ExecutionLog table
>> means?
>> From some experiments seems that there are 2 values:
>> 0 - Reports executed manually
>> 1 - Reports execution due to a subscription event
>> there are other values? Is it possibile to know them? I'm tryin with
>> write
> a
>> Report that will help to read the ExecutionLog table.
>> Davide Mauri
>>
>|||1 = Live report
2 = Run from cache
3 = Run from snapshot
4 = run from history
More info can be found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/arp_rslogfiles_v1_7942.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Davide Mauri" <mauri_davide@.libero.it> wrote in message
news:O#ZcN#fsEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Thanx a lot Daniel
> just another one question:
> can you also tell me what the values in the column "source" mean? i always
> have a value of 1 here.
> Thanx a lot again & in advance :-)
> Davide
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uCVEZBasEHA.1016@.TK2MSFTNGP10.phx.gbl...
> > There are only two values. 0 means that the report was run in the web
> > service process, 1 means it was run in the reportserver windows service
> > process.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Davide Mauri" <mauri_davide@.libero.it> wrote in message
> > news:#yfeKaTsEHA.2780@.TK2MSFTNGP09.phx.gbl...
> >> Hi to all
> >>
> >> someone can tell me what the RequestType field in the ExecutionLog
table
> >> means?
> >>
> >> From some experiments seems that there are 2 values:
> >>
> >> 0 - Reports executed manually
> >> 1 - Reports execution due to a subscription event
> >>
> >> there are other values? Is it possibile to know them? I'm tryin with
> >> write
> > a
> >> Report that will help to read the ExecutionLog table.
> >>
> >> Davide Mauri
> >>
> >>
> >
> >
>