Resetting User Password

Hi,
I've created a user which is attached to a database. This
user exists only in SQL 2000. (no NT login in domain or
on the local box).
This user owns this database and i can't remember the
password. Therefore i'm unable to connect to it through
another computer using the ODBC connection.
I've looked everywhere to see where i can reset the
password. But i can't find it. I'm guessin that using the
T-SQL command prompt stuff is my best bet. But i've also
had problems with this SQL box and i can't connect to it.
Please let me know if there's anyway i can reset the
password. I've tried creating another user and giving it
full access to the db. As a custom application runs ontop
of the SQL database it needs specific permissions which
are builtin to that user
ThanksA sysadmin or a security admin will need to reset the
password using sp_password, e.g.
EXEC sp_password NULL, 'NewPassword', 'SQLLogin'
Use NULL for the old password argument to bypass the
checking of the old password.
You can find more information in books online under
sp_password.
-Sue
On Tue, 20 Jul 2004 18:34:26 -0700, "Jeremy Pond"
<jpond@.railcu.org.au> wrote:

>Hi,
>I've created a user which is attached to a database. This
>user exists only in SQL 2000. (no NT login in domain or
>on the local box).
>This user owns this database and i can't remember the
>password. Therefore i'm unable to connect to it through
>another computer using the ODBC connection.
>I've looked everywhere to see where i can reset the
>password. But i can't find it. I'm guessin that using the
>T-SQL command prompt stuff is my best bet. But i've also
>had problems with this SQL box and i can't connect to it.
>Please let me know if there's anyway i can reset the
>password. I've tried creating another user and giving it
>full access to the db. As a custom application runs ontop
>of the SQL database it needs specific permissions which
>are builtin to that user
>Thanks|||Hi Jeremy,

> This user owns this database and i can't remember the
> password. Therefore i'm unable to connect to it through
> another computer using the ODBC connection.
> I've looked everywhere to see where i can reset the
> password. But i can't find it. I'm guessin that using the
> T-SQL command prompt stuff is my best bet. But i've also
> had problems with this SQL box and i can't connect to it.
> Please let me know if there's anyway i can reset the
> password. I've tried creating another user and giving it
> full access to the db. As a custom application runs ontop
> of the SQL database it needs specific permissions which
> are builtin to that user
Can you stop the MSSQL server on the user's machine? Then you can copy the
database files and attach to another MSSQL Server engine without any
problems, you don't need any password to attach these files. Next, if you
can detach the database from the user's machine and then attach again, you
probably get all passwords disabled...) If you can't detach, then stop the
server, move the original database files and start again, you probably will
be able to kill the dissappeared database from the database list and attach
a new one using your files.
I have got my head broken trying to find a method to protect the MSSQL
database and couldn't and gave up. All newsgroups were keeping the silence
how to increase the security of the database about 15 months ago. I found
finally Sybase ASA and it allows me to keep a higher security of the
database, especially if these files are encrypted. If you were using Sybase
with encryption you should pray to open these files or reset the password, I
suspect that it's impossible, but MSSQL Server allows to walk through the
database without any password if you have a physical access to the server
and can stop the server for a while. That was the main reason why we refused
using the MSSQL server on our client's remote machines. It's not safe. Maybe
a new version... We're still hoping... And we're still using MSSQL Server
for our consolidated database..
Thanks,
D.|||Hi,
In addition to my previous message about detach/attach:
http://www.databasejournal.com/feat...cle.php/1438491
Thanks|||hey,
thanks for the reply, can't try it now during office
hours. But will certainly give it a go. It never occured
to me to do that. But i've now built a spare SQL box to
do that with.
That is an excellent solution. Very very smart. Good
work
you're a champion
thank yousql

Resetting the Suspect

Hi ,
How can I Resetting the Suspect of the database in Sqlserver database ?
Regards ,
ChuongYou can use sp_resetstatus as described in the Books Online:
EXEC sp_resetstatus N'MyDatabase'
Also, review the SQL Server error log for messages indicating why the
database was marked as suspect.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Trang" <trangtran86@.hotmail.com> wrote in message
news:exFNr34dDHA.3592@.tk2msftngp13.phx.gbl...
> Hi ,
> How can I Resetting the Suspect of the database in Sqlserver database
?
> Regards ,
> Chuong
>

Resetting the pagenumber at new every group

I need to print multi-paged invoices. Therefore I'd like to
reset the page number to 1 whenever the customer changes
(i.e. pagenumbering on group-level). The whole print-job
encompasses hundreds of pages.
How can I do this?This cannot be done in the current release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel Aebi" <maus2001@.bluewin.ch> wrote in message
news:Ompzea5eEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I need to print multi-paged invoices. Therefore I'd like to
> reset the page number to 1 whenever the customer changes
> (i.e. pagenumbering on group-level). The whole print-job
> encompasses hundreds of pages.
> How can I do this?
>
>

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 the count on a Primary Key at a certain record? Impossible?

Hello,
Several consecutive records have been deleted out of an MSDE database
and the program that uses the database doesn't like it. I'm curious
if it's possible to... say... reset the primary key count so that the
records all shift to fill the "dead space" left by the deleted
records.
I have the feeling that this may be humorous to some of you, but any
input (including "you can't do this. that's the point of a primary
key") is appreciated.
Thanks!
Matt
hi Matt,
Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED
option specified..
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote:

>Hello,
>Several consecutive records have been deleted out of an MSDE database
>and the program that uses the database doesn't like it. I'm curious
>if it's possible to... say... reset the primary key count so that the
>records all shift to fill the "dead space" left by the deleted
>records.
>I have the feeling that this may be humorous to some of you, but any
>input (including "you can't do this. that's the point of a primary
>key") is appreciated.
Hi Matt,
You can't do this. That's the point of a primary key.
Seriously - a primary key is intended to identify an entity. Think of
what would happen if you would change your name. Or get a new SSN.
Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED
will affect new IDENTITY values, not existing values. I think that you
were asking about the latter.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
ooopppsss...
Hugo is right, completely right... I did not read with enougth attention and
only saw the "identity" resetting requirement... I'm sorry..
I'm just curious why "..program that uses the database doesn't like it.." :D
Agreeing completely with Hugo about the "keys" immutability, you can perhaps
workaround that..
you could create a "temporary" table where you insert all the current valid
rows of your table, truncate the original table in order to empty it and to
reset the idientity table's value, and finally refill it with the "orphaned"
rows..
so you can write something like
INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
TRUNCATE TABLE dbo.YourTable;
INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
SELECT all_columns_but_the_identity_col
FROM tempTable
WHERE... ORDER BY...;
DROP TABLE tempTable;
but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
or then.. and this "workaround" does not work if you have declarative
referential integrity set on that table, if it's referenced by other rows in
other tables...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> Matt Brown - identify wrote:
>
>
> ooopppsss...
> Hugo is right, completely right... I did not read with enougth attention and
> only saw the "identity" resetting requirement... I'm sorry..
> I'm just curious why "..program that uses the database doesn't like it.." :D
> Agreeing completely with Hugo about the "keys" immutability, you can perhaps
> workaround that..
> you could create a "temporary" table where you insert all the current valid
> rows of your table, truncate the original table in order to empty it and to
> reset the idientity table's value, and finally refill it with the "orphaned"
> rows..
> so you can write something like
> INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
> TRUNCATE TABLE dbo.YourTable;
> INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
> SELECT all_columns_but_the_identity_col
> FROM tempTable
> WHERE... ORDER BY...;
> DROP TABLE tempTable;
> but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
> or then.. and this "workaround" does not work if you have declarative
> referential integrity set on that table, if it's referenced by other rows in
> other tables...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I haven't checked this is a while. That's actually perfect. I can
remove some recordsets, then reset the identity key count with this
here DBCC CHECKIDENT WITH RESEED, then everything should progress as
normal.
Righto!
Thanks,
Matt

Resetting stats for Performance Dashboard

I have installed the reports for the performance dashboard and really like it!

However, I'd like to be able to clear the stats in order to run specific procedures etc and see the most inefficient parts in the specific procedure. The documentation says like this about it:

The lifetime of the DMV data depends on the lifetime of the plan in cache. You can determine how long the plan has been cached, and thus the time frame over which these resources have been consumed, by looking at the Plan Cached column

What Plan Cashed column? Can someone explain to me how to clear the stats?

Regards Andreas

take a look at 'dbcc freeproccache' in book online.

|||

Thank you!

Think it was exactly what I needed!

Regards Andreas

sql

Resetting SA password

Hello all.

This is the best suited forum I found for my problem, if there's a better suited one please direct me.

I am running SQL Server 7.0 on Windows 2000 Server, and the SA's password has been forgotten and no other employee knows it.
I searched the net for a solution, and I found various pages describing how to reset the SA's password by logging in an administrator, connecting to the server and using a sp_password command.

For some reason, I cannot connect to the server even when I'm logged on as administrator.
Here are the steps I took:

1) I logged in as DOMAIN\Administrator at my domain controller.
2) I opened Enterprise Manager and clicked on the server labeled "(LOCAL)". The server is registered to use "Windows NT authentication".
3) Received the following error:"A connection could not be established to (LOCAL) - Login failed for user 'DOMAIN\Administrator'.. Please verify that SQL Server is running and check your SQL Server registration properties ... and try again". If there was an option to attach a screenshot, I would, but...
4) I have verified that SQL Server is running.

As I am new to SQL Server, I'm not sure about this, but the SQL Server is called SERVERNET, and the domain I'm logged into is called DOMAIN.
Should I login as an administrator to SERVERNET and not to DOMAIN? Is that possible? "SERVERNET" does not show on the login screen, only "DOMAIN" is listed there.

Any help will be appreciated.

Thanks

You need to login into sql server with an id that has sysadmin privilege to change the password.........In your case i think the Id domain is an OS-level id and might not be created in sql as a result of which login has failed for the Id.....if you have client tools installed in your local machine, just login into it and change pwd.....

You can refer this link for changing pwd without using client tools for sql 2000 and sql 2005

http://www.sql-articles.com/ - Sql logins password change utility is the topic name......

|||Thanks for the quick reply.

Can you elaborate on how to login to the SQL Server with "sysadmin" privileges?
As I understand from your response, DOMAIN\Administrator isn't considered to have high enough privileges when the SQL Server is concerned. How do I fix that? How can I manage the list of users of the SQL Server?

I thought that any member of the Administrators group has total control.
Again let me remind that I am logged into the "DOMAIN" domain, and the server is called SERVERNET.
I guess I'm looking for a way to login as SERVERNET\Administrator, and not DOMAIN\Administrator...

I tried the tool you suggested, but it requires the old SA password in order to work. I'm looking for a way to reset the existing password.

Thanks again.
|||

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188106,00.html

https://websvc06.cern.ch/winservices/Help/?kbid=050140

pls check this if it helps ..........

the tool i sugested is for sql 2000 and sql 2005.............just in case you need it for those you can use them....

Resetting rows count

I have a table in Database where I have added and removed rows, during the
period of developing applications. Now I have removed all row, but when new
are added, their IDs don't start from 1.
Is there a way to reset some counter or something?
hi Nikolay,
"Nikolay Petrov" <johntup2@.mail.bg> ha scritto nel messaggio
news:%23AvGpW5gEHA.1184@.TK2MSFTNGP12.phx.gbl...
> I have a table in Database where I have added and removed rows, during the
> period of developing applications. Now I have removed all row, but when
new
> are added, their IDs don't start from 1.
> Is there a way to reset some counter or something?
>
you are probably referring to a table colum's property known as IDENTITY...
in order to reset it's internal value, you can have a look at the DBCC
CHECKIDENT
(..)http://msdn.microsoft.com/library/de.../en-us/tsqlref
/ts_dbcc_5lv8.asp action...
if you want to delete all rows from a user table and reset the IDENTITY
property on the same time, you can issue a TRUNCATE TABLE statement instead
of DELETE FROM...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Resetting Page numbers on each Group

Hi
I have designed a report on Invoice details and grouped by Invoice Numbers
and also applied page break on this group. I need to reset page numbers on
each Invoice number.
Is this possible?
Anyone know how to do this?
I am using SQL Server 2000 Reporting Services.
Thnx
DimpuHi Dimpu,
Not supported in RS 2000, but there is a workaround using a little code
- check out Chris Hay's RS Sleazy Hacks BLOG entry at
http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
It works great!
MattA
www.reportarchitex.com|||Hi Matt
It worked! Many thnx for your valuable information.
Regards
dimpu
"MattA" wrote:
> Hi Dimpu,
> Not supported in RS 2000, but there is a workaround using a little code
> - check out Chris Hay's RS Sleazy Hacks BLOG entry at
> http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
>
> It works great!
> MattA
> www.reportarchitex.com
>

Resetting Page Number in SQL Reporting Services

Does anyone know of a way to reset page numbering when the group changes?
Thanks,
DennyDenny:
Try this blog that I found on this same group.
http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx
Sher
Denny wrote:
> Does anyone know of a way to reset page numbering when the group changes?
> Thanks,
> Denny

Resetting page counter

I have a page counter in the page footer. How can I reset it once a whole group has been shown? I know its in the section expert, but what formula do I use?
ThanksOne way to do this is to format the section to add a new page after each group and go into the formula and type <> OnLastRecord. This will give a new page after each record (except for the last page). You can then use a running total that is evaluated on change of that group. If you show the running total at the bottom of the Crystal Report (http://www.shelko.com) page, you will get the effect that you are looking for.sql

resetting master.mdf

Hey! I downloaded the sql engine and made a bunch of tables in visual basic. They were called new1, new2, new3, and new4. So I deleted new1.mdf, new1.ldf, new2.mdf, etc... but I notice I still can't recreate them. So I'm thinking there's remnants in master.mdf or something?? How would I go about getting rid of all the old tables, even ones whose names I've forgotten, so I can have a clean slate again? (btw I don't have sql server, just the desktop engine)mdf's are not tables..they are databases...

Did you DROP the databases?

If so they are gone....|||thanks for the quick reply. I didn't drop the databases. In the VB code all I did was 'create database... etc'. So, how would I drop all the databases, even if I don't remember the names of all of the ones that I created? Like is there a way to reset all that stuff so it will be like I just installed it?|||SELECT * FROM master..sysdatabases

Just don't touch master, msdb, model, pubs or northwind

And you probably should do some maintenance

Check out

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx|||thanks! I'll check that site out.

Resetting Key Column Numeration from SQL Tables

I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?

You have to use the DBCC Checkpoint command

Refer this: http://www.sqlteam.com/item.asp?ItemID=8003

|||Check out BOL for DBCC CHECKIDENT command.|||

Another way to do this is to use Truncate Table Command rather than delete.

This will reset identity.

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

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 identity columns

Is there a way besides truncate table to reset the identity column of a tabl
e?
Thanks,
JoeLook at DBCC CHECKIDENT in Books Online.
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Jaylou,
Yes. DBCC CHECKIDENT ...RESEED argument.
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:109E8A34-65B2-4E8D-8FA3-C99D00C25D20@.microsoft.com...
> Is there a way besides truncate table to reset the identity column of a
> table?
> Thanks,
> Joe
>|||Thank you!
I knew there was something but I couldn't find it.|||Thank you!
I knew there was something but I couldn't find it.

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 Email Queue

Hi
I have a the database mail system parameters set to retry sending failed
emails every ten minutes. I want to leave these settings as is.
How can I reset the queue ? that is delete all items in the queue - even if
they have failed! I need to refresh/delete them because we send out the
emails manually if they failed tooo many times - and then eventually sql 2005
alse sends them - i have to prevent this.
Answered my quesiton myself!
One can look at the default system stored procedure and modify it to accept
a subject for example as a parameter.
Then just tweak the rest of the stored procedure to make it select only mail
items with similar subjects and delete those!
done!
"I.W Coetzer" wrote:

> Hi
> I have a the database mail system parameters set to retry sending failed
> emails every ten minutes. I want to leave these settings as is.
> How can I reset the queue ? that is delete all items in the queue - even if
> they have failed! I need to refresh/delete them because we send out the
> emails manually if they failed tooo many times - and then eventually sql 2005
> alse sends them - i have to prevent this.
sql