Deletion And Identity Reset

Oct 13, 2005

Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty.  I've copied all my data from my test DB over to my live DB (didn't mean to but I did).  I would like to remove all the data and the identity values, resetting them back at their original values.  Is there a simple way or do I have to do it the hard way.  That being going in and removing Identity, saving and then placing identity back on the DB Table.

View 3 Replies


ADVERTISEMENT

How To Avoid Non-published Columns Deletion On Subscriptions' Reset

Apr 26, 2007

Hi all!
I have been doing some testing with transactional replication. I have a table (TEST) with the following columns:

[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
[stock] [int] NOT NULL CONSTRAINT [DF_Prueba1_stock] DEFAULT ((0)),

I want the table rows to have an independent stock value for each database. So,
I made a transactional publication with updatable subscriptions, to replicate all the table columns, except the stock column.

The problem is when I reset subscriptions: the table is deleted an created again on the subscriber, so all the data stored at the stock column is lost. I tried to solve this changing the "Action if name is in use" option at the publication. I choose to keep the current object without changes, but I began having problems with the generation of the identity values at the subscriber.

Any suggestions will be welcomed

Thanks in advance ;-)

View 8 Replies View Related

Can I Reset Identity?

Jun 16, 2008

I have a table where I delete all the records, then reload.  can I reset the identity to (1,1)  Is there a SQL command for that?
 -smcirish

View 3 Replies View Related

How To Reset Identity ?

Mar 11, 2004

In my application , DB has a large table. I write a small program to clear the table whenever the size of table is over 50 MB. At that time , I want to reset identity as 1. How can I do that?
Currently , my program delete old table and generate a new one with the same schema when the table is too large.But this is kind of ugly.

View 1 Replies View Related

Reset Identity?

Jun 27, 2001

Can a column of Identity type reset after it reaches its maximum value?

Thanks,
Ben

View 4 Replies View Related

The Publisher Failed To Allocate A New Set Of Identity Ranges For The Subscription After Another Publication Deletion

Jan 17, 2007

Hi.

First of all, I apologize for my english

I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.

But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"


If i'm watching the status of the leftover replication through the replication monitor, i get this error message :

"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"


I checked the given links but they're useless.


So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :

"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

Failed to pr"


I didnt have any idea to correct this issue, so I would appreciate any help.

Thanks.

David.

View 7 Replies View Related

Reset Identity DataType

Mar 15, 2004

How can I reset an identity Int column back to start with 1 if I remove all rows from the table?

Thanks,

View 1 Replies View Related

Reset IDENTITY Seed

Aug 26, 2005

Hello,

Can I reset the IDENTITY seed of a Table column without delete/drop the table?

I want to delete all the table rows, restore de seed, and restore a
backup made on a XML (using SET  IDENTITY_INSERT Table ON)

I cant drop the table due to acount restricctions.

regards,
Edu

View 3 Replies View Related

Reset Identity Seed

Jun 1, 1999

I would like to set the identity seed to a different value. How do I do that? Please help!

Sam

View 3 Replies View Related

Identity Set , How To Reset Record No

Mar 28, 2006

i have a table with the following fiels ,
Column NameDataType
Sno intIdentity = Yes , Identity Speed =1 , Identity Increament =1
namechar

Now i entered data and coneected to database, worked.Now after cheking all the data entered with the form , now i have to send the table to client place.
The problem is , the sno column has the value which i entered last. now if i delete all records , then also the record no doesnot become 0.
what i have to do, to set the sno column to 1 again.

View 3 Replies View Related

Reset The Identity Increment

Jul 20, 2005

Reset the Identity IncrementHello:I have a table with a bigint type column (field) that has an identity seedof 1 and an identity increment of 1. The column is the primary key for thetable.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 1automatically. (start counting at 1 again). How does one do that, becauseas it is now, the DB keeps increasing the value of the column from where itleft off, regardless of the fact that I deleted all of the data in thetable.The DB is MS SQL Server 2000.Thanks and appreciate any help.Ryan Kennedy

View 2 Replies View Related

How To Reset An Identity Column

Nov 27, 2007

Hi


I have a table with Identity column starting from value 1 and autoincrementing 1 for every new value. I inserted (5) rows and then deleted these rows. But, when i insert a new row, it was taking the last identity value(5)and inserting the rows with next identity value i.e., 6 for this column. I dont want that. Everytime, I delete and insert rows in to this table.I wanted the rows to start with 1 for this column.

Any help on this is highly appreciated.

Thanks!

View 4 Replies View Related

How To Reset Identity Column's Value?

Jul 15, 2006

Hello friends,
I have a table in SQL server 2005. it contains one identity column named EmpID
it's datatype is int isidentity and auto increment by 1.

I deleted all the records from this table.
Now I want that EmpID should start again from 1 how can I do it ?


Thanks & Rgds,
Kiran Suthar.

View 9 Replies View Related

Not Able To Reset The Identity Column

Sep 25, 2007



Changing the seed and increment values on the identity column

- CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50))
- INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation')
- ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
When i excute the Alter command, following error comes:"Incorrect syntax near the keyword 'IDENTITY'."I've picked the example from SQL SERVER 2005 books online.Please let me know if we can change the seed value on the identity column from a sql command.

View 1 Replies View Related

Identity Seed Reset In SQL Table

Feb 16, 2004

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

Thanks

View 5 Replies View Related

Reset Identity Column Counter.

Aug 6, 2007

Is there a way to delete all items from a table that has an identity column and to reset the counter for all new insertions so that they begin at '1' again?

View 5 Replies View Related

Reset IDENTITY After Table Data Import?

Jul 9, 2004

I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.

The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.

I am obviously missing the right bit on Books online

any suggestions?

many thanks

Steve

View 1 Replies View Related

Compact Method To Reset Identity Columns

Jul 20, 2007

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.



Any help is greatly appreciated!



Kind Regards,



Mat

View 7 Replies View Related

How To Reset The Identity Counter On A Table Referenced By A FOREIGN KEY Constraint?

Apr 4, 2006

I know that TRUNCATE TABLE can be used to reset the identity counter, but it can't be used on a table referenced by a foreign key. Anybody knows how to do that? Thanks.

View 4 Replies View Related

Deletion

Nov 6, 2007

Hi all,

I have a table in xyz database and there is no column in table like creation_date or modified_date.

The problem is I want to delete records which has been added in the table before 1st jan 2007.

The size of table is 85 GB

Immediate help would be appriciable.

Regards,
Frozen

View 9 Replies View Related

Restrict Deletion

Mar 11, 2007

What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
 
Thank you in advance.

View 8 Replies View Related

Deletion Problem

Jun 4, 1999

It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.

TIA,

Stella Liu

View 2 Replies View Related

Replication Without Deletion

Mar 19, 2007

Hello there,

We are currently setting up out production server to the following requirements:

1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.

The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.

Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.


Thanks for your time,

Ulrik Rasmussen

View 2 Replies View Related

Database Deletion

Apr 15, 2008

While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.

View 1 Replies View Related

DB Deletion Time

Jun 4, 2008

Is there an option to find out the deleted DBs on a server?

------------------------
I think, therefore I am - Rene Descartes

View 14 Replies View Related

Deletion In Trigger

Oct 12, 2013

table1 has a PK_tbl1 which matches FK_tbl1 in table2 and the relationship is one to one. Like this:

table1 has columns of UnitId, Code ...
table2 has columns of ItemID, UnitId ...

So if I need remove an item from table1 I must remove the matched one in table2 first.

delete from table2 where UnitId = xxx
delete from table1 where UnitId = xxx

Now I need create a trigger on table1 for deletion:

CREATE Trigger [dbo].[Table1_Delete]
ON [dbo].[Table1]
FOR DELETE
As

[code]....

View 2 Replies View Related

How To Prevent Db Deletion

Nov 22, 2006

Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View 8 Replies View Related

Deletion Of Duplicate Row

Jul 23, 2005

Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard

View 2 Replies View Related

Deletion Query

May 29, 2008

Ok, so I have an issue, was wondering if anybody else has any suggestions.

I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.

1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2. Table contains about 70 million records
3. Table has 6 indexes associated to it
4. Table has 2 views associated to it.
5. Table has 8 foreign keys associated to it.

I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.

When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.

The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.

Here's a DBCC SHOWCONTIG on our table

DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)

Any suggestions on how to delete records efficiently?

View 4 Replies View Related

0 KB File Deletion

Feb 7, 2008



Hello,
I want to delete a 0KB file on one of the disks in my server. Do any of you have any idea how to do it??
Thanks,
Narayan

View 11 Replies View Related

User Deletion Log SQL

Aug 15, 2006

Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.

View 1 Replies View Related

Deletion Of Duplicate Values

Jun 11, 2007

hi,

i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).

can you help?

View 14 Replies View Related

Database Still 'exists' After Deletion

Apr 14, 2006

hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved