Modify Table To Turn OFF Autoincrement ID

Oct 4, 2013

I've a table with an ID column, it is an auto increment id column that increases by 1.

ID
--
9724
9725

When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.

My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)

So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?

example:
id, place, locstion, gps, v2, v3, v4, comment
================================================
9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat
9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photos

I'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?

View 5 Replies


ADVERTISEMENT

Determine If Autoincrement Is Set In A Table, Form Asp.net

Nov 15, 2006

I want to access sqlserver table properties from asp.net .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you

View 6 Replies View Related

Adding Autoincrement With Alter Table.

Jan 28, 2000

Is there a way to use the SQL alter table command to modifiy the identity
increment?

View 2 Replies View Related

How To Increment Data Of Same Table With Autoincrement

Jan 3, 2014

How to Increment Data of the Same table with AutoIncrement .i have a table with 4 columns

id type no amount
1 type1 a1 1000
2 type1 a2 2000
3 type2 b1 3000
4 type3 c1 4000

using Loop how can i increment them to hundered rows same data .

id type no amount
1 type1 a1 1000
2 type1 a2 2000
3 type2 b1 3000
4 type3 c1 4000
5 type1 a1 1000
6 type1 a2 2000
7 type2 b1 3000
8 type3 c1 4000
9 type1 a1 1000
10 type1 a2 2000
11 type2 b1 3000
12 type3 c1 4000

View 4 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related

How To Modify Colum (alter Table) Of Table That Is Replicated?

Jul 20, 2005

Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern

View 1 Replies View Related

I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

May 29, 2008

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it


I need that query for alter table

in it


reply me soon

View 14 Replies View Related

How To Modify Table Structure

May 31, 2002

Hello
I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode.
Can any body help me to sort out this problem.
Thx
Bilal

View 1 Replies View Related

Using Modify Table Command

Nov 22, 2007

Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?

View 1 Replies View Related

Trigger To Modify A Table

Mar 19, 2008

I have a 3rd party program that creates some tables in my sql server2005 database, then it does inserts on the tables. Is there a way Icould create a system level trigger that would immediately andautomatically modfiy the table structure after the table is created(hopefully before the inserts occur)? I need to change a field fromnumeric(5, 0) to numeric(15, 0).Thanks,Roger

View 3 Replies View Related

ALTER TABLE MODIFY

Jan 19, 2006

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

View 3 Replies View Related

ALTER / MODIFY / OverWrite Table?

Apr 10, 2008

Hi all I was wondering how to do an ALTER command on a Table but without specifying Column Names but rather attempting to overwrite the Table itself with the new fields specified? For instance if I have Table_1 consisting of the following fields:

IDFirstNameSurname

Then use the following ALTER command:




Code Snippet
ALTER Table Table_1
(
ID Int,
FirstName VarChar(50)
)
This would then drop Surname from the Table and leave only ID and FirstName inside it. Is this possible? I have been searching google but can't seem to find what I am looking for.

Thank for comments + suggestions. Regards, Onam.

View 2 Replies View Related

How To Modify Column In A Table With Replication?

Mar 2, 2007

Dear all

I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?

Please solution.

Thank.

PC Kaveesin.

View 4 Replies View Related

Modify Table Structure Via Stored Procedure(?)

Jun 9, 2008

Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.

They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.

This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.

Is it possible to do stuff like that in a SP?

View 1 Replies View Related

Read, Modify Table (locking) Question

Feb 3, 2006

I am quite new to MS SQL, and I want to read rows from a todo table, andwhen a row has been processed, I want to delete that row.Ages ago in MySQL I would probably have locked the table, select, processa row, delete a row, unlock the table.I have been reading through the documentation from MS SQL, but it's notclear what exactly I should do.Since I want to lock only one table in the select (the others just providedata, and are not modified), what's a good solution?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html

View 2 Replies View Related

Open Table With Query To Modify Data

Jul 23, 2007

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.



In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?



I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.



Please help, it seemed so much easier in 2000!

View 7 Replies View Related

Can A View Be Used To Modify More Than One Underlying Table With A Single Statement In MS SQL?

Mar 20, 2008

What's the answer for this and how ? If it is yes then How ?

View 4 Replies View Related

Does Anyone Know How To Modify Table Structure In Sql Server Management Console(2005)? Thanks

Jun 16, 2005

does anyone know how to modify table structure in sql server management console(2005)? thanks

View 1 Replies View Related

Transactional Repln,how To Modify The Width Of A Column Of A Table Which Is Replicated

Aug 3, 2006

Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.

I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.

This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)

MH (source) -> MH1 (Replicate)

The column €ścol1€? had width of varchar(10) and this was altered to varchar(100).


insert into MH..test_mh values(4,'abcdeabcdefff')

select * from MH1..test_mh

exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go

exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go

alter table test_mh alter column col2 varchar(100) null OR

MH1..sp_help test_mh

exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go

exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go


Needless to say, help would be apreciated -
~Mihir

View 4 Replies View Related

SQL 2012 :: Script To Find Current Date Modify Table List?

Jun 10, 2015

Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.

View 7 Replies View Related

Autoincrement

Sep 20, 2006

hi,

does anybody know how to create an identity column in a query without having to use the into clause?

for example, i want to do the following:

select *,identity(int,0,1) as myId
from myTable

but this is not allowed

thank you

View 10 Replies View Related

How To Autoincrement ID

Mar 26, 2007

i have a table for storing username and password.
the following are the fields in table
ID -int
UserName-nvarchar(50)
Password-nvarchar(50)

i dont want to have a separate field for id
i want to autincrement them
plz give me an idea
am using SQLServer Express
am new to this field so plz forgive me for this simple question

thanks
Shruthi

View 8 Replies View Related

Sql Autoincrement

Apr 4, 2006

in mysql the primary key has the possibility tu autoincrement herself

can it be done in MS SQL ??? how

View 9 Replies View Related

SQL Autoincrement Id STOP

Apr 10, 2008

 Hi  All        I am using SQL server Database in one of my table there is a column which is set to Identity=Yes i.e., The ID is increment by one on every insert and if  the insertion failed then the id generated goes off then in the next generation it uses new id ..........EXfirst insertion id=1 then in the second insertion if while adding data to other rows if i get some error  then the id 2 is not used and when i correct the error and insert it then id=3? can any one give me the solution for this and NextWhen i delete the datafrom the table see the ids are upto 20 and i delete all the records from the table after insertion of new  record the id will be 21plese help me in this  

View 3 Replies View Related

Field Autoincrement

Dec 10, 2004

hi,
Is their anybody knows how to make field in Sql Server autoincrement?
pls. help me.

View 3 Replies View Related

Autoincrement Manually

Jun 24, 2008

Hi All,

This may sound quite easy for you, however I am a newbie in SQL.

So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.

So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1

Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2

The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.

can anyone help?

Thanks

Johann

View 12 Replies View Related

AutoIncrement Column

Oct 26, 2005

How can I know that a column is auto incremented or not? What are the commands used to know this??
Thanks guys

View 1 Replies View Related

Function Used To Set Autoincrement Id To 0

Dec 12, 2007

Hi all, i am deleting rows of a table and reinserting new ones. This table has an auto increment primary key. what is the function used to set it to zero each time i delete the rows in order to rebegin counting from 1 when i refill the table. Thanks in advance...

View 7 Replies View Related

Autoincrement Column

Mar 17, 2008

Hi,
I have an existing table (without PK). I want to output my SELECT statement with an auto-increment on the 1st column like this:

# Lastname Firstname Address
1 Obama Clinton xxxxx
2 Hillary xxxx xxxxx
...
Can someone help me on this pls?
Appreciate it.
Thanks
joseph

View 6 Replies View Related

SQLexpress And Autoincrement

Apr 7, 2006

I've been searching for an answer a long time now... but nowhere I found a clear solution...

Is it possible to implement autoincrement with SQLexpress edition?

View 8 Replies View Related

Modify String Data Base On Data In The Table

Jun 17, 2004

I need to get rid of the first char of data in a field f1 (in sql server)

I tried to use:

update myTable
set f1= substring(f1,1,f1.Len -1)

and get error "The column prefix 'f1' does not match with a table name or alias name used in the query.

Could anyone help? Thanks.

View 2 Replies View Related

Sql Server 2005 Autoincrement Value

Dec 25, 2006

I created a new table in sql server 2005. Primary key is autoincrement value of 1. But when I insert the row, it increments by 4. It goes like 1, 5, 9 etc..what could be the problem ?how can I correct it? Do I need to reseed it?
 Thanks!!

View 14 Replies View Related

Autoincrement With Alter Statement

Jul 3, 2000

looking for necessary syntax to alter table id to autoincrement adding identity statement, not sure on syntax for seed an increment, or if it is possible at all.

View 2 Replies View Related







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