Alternate Replication Partner and Identity Ranges

Hi,
I am trying to setup my merge replication to use Alternate Replication
Partners.
What I want to do is to have PubA as the main publisher and PubB as a named
pull subscriber to PubA and a republisher.
Then, there is SubA, which subscribes to PubA (pull/anonymous).
It works fine. I can switch SubA from PubA to PubB, however, the identity
ranges are not working. In other words, when SubA runs off IDs it doesn't
get new IDs from PubB.
Any ideas? Thanks, Jos.
Note that I followed the steps in
http://support.microsoft.com/default...b;en-us;321176 to set this
up.
Alternate Sync Partners do not support the incrementing of identity ranges.
From BOL in the section marked Alternate Synchronization Partners
a.. When using automatic identity range handling, a Subscriber must
synchronize with its primary Publisher to receive a new identity range.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OYyo9Qx1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am trying to setup my merge replication to use Alternate Replication
> Partners.
> What I want to do is to have PubA as the main publisher and PubB as a
named
> pull subscriber to PubA and a republisher.
> Then, there is SubA, which subscribes to PubA (pull/anonymous).
> It works fine. I can switch SubA from PubA to PubB, however, the identity
> ranges are not working. In other words, when SubA runs off IDs it doesn't
> get new IDs from PubB.
> Any ideas? Thanks, Jos.
>
> Note that I followed the steps in
> http://support.microsoft.com/default...b;en-us;321176 to set this
> up.
>
|||Thanks.
PS: My BOL doesn't have that clarification. I guess I have an outdated
version of the documentation (ugrhhhh).
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uQLFmA31FHA.2964@.TK2MSFTNGP09.phx.gbl...
> Alternate Sync Partners do not support the incrementing of identity
ranges.[vbcol=seagreen]
> From BOL in the section marked Alternate Synchronization Partners
>
> a.. When using automatic identity range handling, a Subscriber must
> synchronize with its primary Publisher to receive a new identity range.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OYyo9Qx1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> named
identity[vbcol=seagreen]
doesn't[vbcol=seagreen]
this
>

Altering the identity seed of a table

Hi,

Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.

This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?

cheers
Pete StoreyDBCC Checkident.|||Thanks!

Altering SQL Server 2000 table design

I'm trying to do a simple alteration to the table design of one of our
SQL 2k tables, simply changing an identity row so that its not 'not
for replication', and its taking absolutely ages to do so, and stops
the sql server from working.

Whilst it's attempting the update, no one can access the database, the
sqlservr.exe memory usage shoots up and enterprise manager reports a
not responding status. Eventually after about 10 minutes, it bombs out
reporting,

Unable to modify table
Could not allocate space for object 'Tmp_TableName' in database
'DBNAME' because the 'PRIMARY' filegroup is full.

The table i'm attempting to change has only about 4000 records so
there's not a huge amount of data.

Any ideas what's causing this and how i can get around it?

A similar thing happens when i attempt to change the length of a
varchar too.

Thanks in advance for any suggestions

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406040735.5d88d033@.posting.google.c om...
> I'm trying to do a simple alteration to the table design of one of our
> SQL 2k tables, simply changing an identity row so that its not 'not
> for replication', and its taking absolutely ages to do so, and stops
> the sql server from working.
> Whilst it's attempting the update, no one can access the database, the
> sqlservr.exe memory usage shoots up and enterprise manager reports a
> not responding status. Eventually after about 10 minutes, it bombs out
> reporting,
> Unable to modify table
> Could not allocate space for object 'Tmp_TableName' in database
> 'DBNAME' because the 'PRIMARY' filegroup is full.
> The table i'm attempting to change has only about 4000 records so
> there's not a huge amount of data.
> Any ideas what's causing this and how i can get around it?
> A similar thing happens when i attempt to change the length of a
> varchar too.
> Thanks in advance for any suggestions
> Dan Williams.

Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
there's no way to remove the NOT FOR REPLICATION option without recreating
the table. Behind the scenes, Enterprise Manager will create a new table,
set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
original table, then rename the new one. Tmp_TableName is the 'working'
table that will be renamed after the existing TableName is dropped.

With a large table, this can be a slow process requiring a lot of disk
space, but 4000 rows doesn't sound like much data (unless you have
text/image columns perhaps). Anyway, the error message is clear - no more
space in the filegroup. So you need to add space by expanding the existing
database file(s). If you can't do this for some reason, then one solution
might be to use bcp.exe or DTS to export the data to a flat file, drop and
recreate the table yourself, then import the data.

Finally, as a general remark, Enterprise Manager hides a lot of what it's
really doing from you, so many people prefer to use Query Analyzer as much
as possible, since then you have complete control over what you're doing.

Simon|||Thanks for the response.

Having done a bit more research on Google i managed to find this:-

run this in your publication database.
Here I am setting the identity column for the jobs table to NFR

sp configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat &
0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
GO
sp configure 'allow updates', 0

Anyone know the value to set colstat too, so as to disable the NFR,
and just make it a normal IDENTITY value?

I also found this web site which was a good reference.

http://www.winnetmag.com/SQLServer/...2080/22080.html

Having clicked on the 'Save Change Script' button of Enterprise
Manager when attempting to do this, I see what you mean about the
amount of work that EM actually does.

Thanks again

Dan.

> Unfortunately, ALTER TABLE doesn't allow you to modify IDENTITY columns, so
> there's no way to remove the NOT FOR REPLICATION option without recreating
> the table. Behind the scenes, Enterprise Manager will create a new table,
> set IDENTITY_INSERT ON, INSERT the rows from the existing table, drop the
> original table, then rename the new one. Tmp_TableName is the 'working'
> table that will be renamed after the existing TableName is dropped.
> With a large table, this can be a slow process requiring a lot of disk
> space, but 4000 rows doesn't sound like much data (unless you have
> text/image columns perhaps). Anyway, the error message is clear - no more
> space in the filegroup. So you need to add space by expanding the existing
> database file(s). If you can't do this for some reason, then one solution
> might be to use bcp.exe or DTS to export the data to a flat file, drop and
> recreate the table yourself, then import the data.
> Finally, as a general remark, Enterprise Manager hides a lot of what it's
> really doing from you, so many people prefer to use Query Analyzer as much
> as possible, since then you have complete control over what you're doing.
> Simon|||"Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406041501.37b74d00@.posting.google.c om...
> Thanks for the response.
> Having done a bit more research on Google i managed to find this:-
> run this in your publication database.
> Here I am setting the identity column for the jobs table to NFR
> sp configure 'allow updates', 1
> GO
> reconfigure with override
> GO
> update syscolumns set colstat = colstat | 0x0008 where colstat &
> 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
> GO
> sp configure 'allow updates', 0
>
> Anyone know the value to set colstat too, so as to disable the NFR,
> and just make it a normal IDENTITY value?
> I also found this web site which was a good reference.
> http://www.winnetmag.com/SQLServer/...2080/22080.html
> Having clicked on the 'Save Change Script' button of Enterprise
> Manager when attempting to do this, I see what you mean about the
> amount of work that EM actually does.
> Thanks again
> Dan.

<snip
Based on the query above, you need an XOR operation to remove NOT FOR
REPLICATION:

update syscolumns
set colstat = colstat ^ 8
where colstat & 1 <> 0
and colstat & 8 <> 0
and id =object_id('jobs')

But be very careful with this - Microsoft does not support modifications to
system tables (see "System Tables" in Books Online), and the colstat column
is not documented (see "syscolumns"). So if you have problems, then you're
on your own - dropping and recreating the table is the supported, reliable
method.

Simon

Altering Identity Columns for Bidirectional TransasctionalReplication

Having 300 tables (all of them with identity columns) with
prepopulated data, I've come to a point where I have to alter all the
tables to have an identity conflict resolution system. In my case I
only have a publisher and a subscriber, hence I've opted to have the
publisher generate only even identities, and the subscriber will be
generating odd values.
I can't seem to find, besides using Enterprise Management (Design
Table Form), any other way to alter the existing identity step already
defined to the table.
Any ideas on how about to either alter the table (preferibly using
SQL), or perhaps a different approach to get the existing tables using
the new identity step (I would rather not have to export the data,
drop the tables, recreate them again, and reload the data once more)
Thank you,
James.
Here is how I ended up doing:
- Backup the original Database containing all the data:
- Create a new database - name it backup_db
- Destroy the orginal database and recreate the database only.
- Take the scripts that created the original database (if none is
available then export the scripts from the backup_db)
- Edit the script and alter all the IDENTITY(1,1) to the desired
seed and step.
- In the original db (now empty) create only the tables - don't
worry about the views, procedures and any other objects.
- Create a snapshot replication using the backup_db as the
publishing database: ensure that the published articles are marked to
only "Delete all the data in the existing table" - this will ensure
that the subscriber's tables are not destroyed (hence keeping your new
identity definition"
- Subscribe the original db to the publication and sync it.
- Once the replication has completed we now need to update all the
tables with an identity to reseed with the latest highest value - you
can do this by defining the following script (in my case, I need the
seed to be even):
BEGIN
DECLARE @.new_ident as int
SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
IF (@.new_ident % 2) <> 0
SET @.new_ident = @.new_ident + 1
DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
END
|||Small errata:

> BEGIN
DBCC CHECKIDENT('your_table', RESEED)
> DECLARE @.new_ident as int
> SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
> IF (@.new_ident % 2) <> 0
> SET @.new_ident = @.new_ident + 1
> DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
> END
Also, in order to generate all the scripts for each table you could
use a mail merge program with a list of your table names - this should
save time and user errors.

Altering (or recreating) a Stored Procedure "header"

We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.Could you please explain how you are recreating the SP? If you are doing it from the UI or something then you may want to post this question in the Tools forum. Otherwise, please post the DDL statement(s) and the reprot steps.|||I believe I see what we were (or in this case weren't) doing... The USE statement is necessary to point the scripts to the correct database. We were seeing the outcome of confusing the master database with our application database. Thanks much for anyone stopping to consider our "dilemma".|||In essence, we are checking for existence of the stored procedure in the system table first, I believe sys.objects. If we find it there first, we drop it. Then we follow up by recreating it. But, as I mentioned in a follow up to our original post, the issue turned out to be a case of not using the USE statement. So what I thought was showing up in our application database was actually showing up in the master database. Not quite what we were shooting for. So hence the confusion.

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

ALTER TABLE Question

I have a temp table I'm filling from two different data sources. I then do
an ALTER TABLE statement to add an identity column. So far so good. But when
try to select the first x number of records using the identity column to put
into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
TIA...just curious: if you need the identity column, why not just include it
in the create table for the temp table?
if you want a good answer, you'll have to post DDL, code, sample data,
desired results, etc. otherwise you'll just get guesses
http://www.aspfaq.com/5006
glen wrote:
> I have a temp table I'm filling from two different data sources. I then do
> an ALTER TABLE statement to add an identity column. So far so good. But wh
en
> try to select the first x number of records using the identity column to p
ut
> into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
> TIA...
>|||glen wrote:

> I have a temp table I'm filling from two different data sources. I then do
> an ALTER TABLE statement to add an identity column. So far so good. But wh
en
> try to select the first x number of records using the identity column to p
ut
> into a cursor I get a 'Invalid Column Name' error. Anyone give me a clue?
> TIA...
ALTER TABLE is unwise in a proc. You'll likely get errors because the
server is unable to resolve all the column names at compile-time.
The "obvious" solution is to include the IDENTITY column when you
create the table instead of adding it later. Is that a problem?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ordinarily I would, but because of the different data sources I need to
number the rows after the data is in and sorted.
Here's my code:
ALTER PROCEDURE mediaq.usp_VideoKeywordCombo2
(
@.Page int,
@.RecsPerPage int,
@.keyword VARCHAR(200),
@.termlist VARCHAR(200)
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
sdate DATETIME,
id INT,
offset INT,
description VARCHAR(50),
thumbnail_name VARCHAR(250),
video_proxy VARCHAR(250),
ip_address VARCHAR(20),
tn_url VARCHAR(50),
mms_url VARCHAR(50),
start_time DATETIME,
sample VARCHAR(250),
time_in INT,
cc_time_in INT,
char_offset INT,
JPG_IMG VARCHAR(100),
VID_ASF VARCHAR(250),
vid_path VARCHAR(250),
cc_text VARCHAR(250),
logo_path VARCHAR(100),
content_date DATETIME,
content_source VARCHAR(100),
content_title VARCHAR(250),
content_id BIGINT,
journalist VARCHAR(200),
moname VARCHAR(100),
content_summary VARCHAR(2000),
article_url VARCHAR(250),
date_inserted DATETIME
)
-- vars for cursor
DECLARE @.bid INT
DECLARE @.id INT
DECLARE @.offset INT
DECLARE @.description VARCHAR(50)
DECLARE @.thumbnail_name VARCHAR(250)
DECLARE @.video_proxy VARCHAR(250)
DECLARE @.ip_address VARCHAR(20)
DECLARE @.tn_url VARCHAR(50)
DECLARE @.mms_url VARCHAR(50)
DECLARE @.start_time DATETIME
DECLARE @.sample VARCHAR(250)
DECLARE @.time_in INT
DECLARE @.cc_time_in INT
DECLARE @.char_offset INT
DECLARE @.JPG_IMG VARCHAR(100)
DECLARE @.VID_ASF VARCHAR(250)
DECLARE @.vid_path VARCHAR(250)
DECLARE @.P1 INT
DECLARE @.P2 INT
DECLARE @.cc_text VARCHAR(250)
DECLARE @.logo_path VARCHAR(100)
DECLARE @.counter INT
DECLARE @.edate DATETIME
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems
(content_date,content_source,content_tit
le,content_id,
journalist,moname,content_summary,articl
e_url,date_inserted)
EXEC mq..usp_KeywordSearch_ft_KV @.keyword
-- Insert records from keyword search proc into temp table
INSERT INTO #TempItems (id,offset,description,thumbnail_name,
video_proxy,ip_address,tn_url,mms_url,st
art_time,sample)
EXEC usp_SearchVideo_IX @.keyword
UPDATE #TempItems SET sdate = start_time where not start_time IS NULL
UPDATE #TempItems SET sdate = date_inserted where not content_date IS NULL
CREATE index glen on #TempItems (sdate desc)
ALTER TABLE #TempItems ADD bid int identity
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Use Cursor to generate new field data based on returned data
DECLARE tmpCursor CURSOR FOR
SELECT id, offset, description, thumbnail_name, video_proxy, ip_address,
tn_url, mms_url,
start_time, sample, bid FROM #TempItems WHERE bid > @.FirstRec AND bid <
@.LastRec
AND start_time IS NOT NULL
OPEN tmpCursor
Fetch next from tmpCursor
INTO @.bid, @.id,
@.offset,@.description,@.thumbnail_name,@.vi
deo_proxy,@.ip_address,@.tn_url,
@.mms_url,@.start_time,@.sample
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.char_offset = mediaq.uf_get_search_position(@.id,@.termlist)
SET @.cc_time_in = dbo.GetThumbnailTimeCC(@.id,@.char_offset)
SET @.time_in = dbo.GetThumbnailTimeTN(@.id,@.char_offset)
IF @.cc_time_in > 10000
SET @.cc_time_in = @.cc_time_in - 10000
ELSE
SET @.cc_time_in = 0
SET @.video_proxy = right(@.video_proxy,(len(@.video_proxy) -
(patindex(@.video_proxy,'/asf/') - 4)))
IF RIGHT(LTRIM(RTRIM(@.thumbnail_name)),1) = '/'
SET @.JPG_IMG = 'http://' + @.tn_url + '/JPGS/' + @.thumbnail_name +
LEFT(@.thumbnail_name,LEN(@.thumbnail_name
) - 1) + '_' +
CONVERT(VARCHAR(20),@.time_in) + '.JPG'
ELSE
SET @.JPG_IMG = 'http://' + @.tn_url + '/JPGS/' + @.thumbnail_name + '_' +
CONVERT(VARCHAR(20),@.time_in) + '.JPG'
SET @.P1 = PATINDEX('%\ASF\%',@.video_proxy)
SET @.VID_ASF = RIGHT(@.video_proxy,LEN(@.video_proxy) - @.P1 - 4)
SET @.vid_path = 'http://mediaq.enr-corp.com/dis_vid_fee.asp?tin=' +
CAST(@.cc_time_in AS VARCHAR(30)) + '&ip=' + @.mms_url + '&bn=' + @.VID_ASF
IF @.char_offset < 40
set @.P2 = 40
ELSE
set @.P2 = @.char_offset - 40
SET @.cc_text = (SELECT SUBSTRING(cc,@.P2,100) as cc_text FROM videos WHERE
id = @.id)
SET @.cc_text = REPLACE(@.cc_text,CHAR(13),' ')
SET @.logo_path = 'http://mediaq.enr-corp.com/channels/' + LEFT(@.VID_ASF,4)
+ '.jpg'
Update #tempitems set cc_time_in = @.cc_time_in, time_in = @.time_in,
char_offset = @.char_offset,
JPG_IMG = @.JPG_IMG, vid_asf = @.VID_ASF, vid_path = @.vid_path,
cc_text = @.cc_text, logo_path = @.logo_path where bid = @.bid
Fetch next from tmpCursor
INTO @.bid, @.id,
@.offset,@.description,@.thumbnail_name,@.vi
deo_proxy,@.ip_address,@.tn_url,
@.mms_url,@.start_time,@.sample
END
CLOSE tmpCursor
DEALLOCATE tmpCursor
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.bid >= @.LastRec
)
FROM #TempItems
WHERE bid > @.FirstRec AND bid < @.LastRec
SET NOCOUNT OFF
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%23epBGh4GGHA.3856@.TK2MSFTNGP12.phx.gbl...
> just curious: if you need the identity column, why not just include it in
> the create table for the temp table?
> if you want a good answer, you'll have to post DDL, code, sample data,
> desired results, etc. otherwise you'll just get guesses
> http://www.aspfaq.com/5006
>
> glen wrote:|||> Ordinarily I would, but because of the different data sources I need to
> number the rows after the data is in and sorted.
Why do you think adding an IDENTITY column will guarantee that the identity
values are applied exactly as the #temp table is allegedly "sorted"?
A|||I had assumed that the adding the Identity column after adding and indexing
the data would give me what I wanted. I'm using the identity column for
paging.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOcStq4GGHA.3176@.TK2MSFTNGP12.phx.gbl...
> Why do you think adding an IDENTITY column will guarantee that the
> identity values are applied exactly as the #temp table is allegedly
> "sorted"?
> A
>|||basically, I want to have an int column numbered after the the data is in
and indexed, for paging purposes.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137518046.399755.210350@.g47g2000cwa.googlegroups.com...
> glen wrote:
>
> ALTER TABLE is unwise in a proc. You'll likely get errors because the
> server is unable to resolve all the column names at compile-time.
> The "obvious" solution is to include the IDENTITY column when you
> create the table instead of adding it later. Is that a problem?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||>I had assumed that the adding the Identity column after adding and indexing
>the data would give me what I wanted.
Well, this is a bad assumption. IDENTITY is not guaranteed to work this
way.

> I'm using the identity column for paging.
Stop. Read:
http://www.aspfaq.com/2120|||....and therefore I should use?...
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eAN4iW5GGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Well, this is a bad assumption. IDENTITY is not guaranteed to work this
> way.
>
> Stop. Read:
> http://www.aspfaq.com/2120
>|||Did you read the article?
"glen" <gsault@.enr-corp.com> wrote in message
news:emDuza5GGHA.2320@.TK2MSFTNGP11.phx.gbl...
> ...and therefore I should use?...sql

Alter Table Question

I'm setting up a DTS to modify a table, and I need to "reset" the identity
field. I had originally wanted to do this with an ALTER TABLE command, but
apparently it isn't as straightforward as that. So at this point I believe
I need to drop the table and recreate it. Is this the case? Assuming it
is, I've run into the following problem. I need to add a description to the
fields when I re-create the table, but I can't seem to find the syntax. For
my CREATE function, it's as simple as:
CREATE TABLE TstTable
(
ACC_ID int identity primary key,
Code varchar(50),
CodeType varchar(15),
ActionType varchar(10),
ActionBy varchar(50),
ChangeDate datetime,
ChangeTime varchar(20)
)
...but I can't figure out how to put a description for each field in.
Anyone know the syntax, or if it's impossible?
Thanks,
JamesNevermind...seems like sp_addextendedproperty will do what I'm looking for.
Thanks anyway!
"James" <cppjames@.aol.com> wrote in message
news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
> I'm setting up a DTS to modify a table, and I need to "reset" the identity
> field. I had originally wanted to do this with an ALTER TABLE command,
but
> apparently it isn't as straightforward as that. So at this point I
believe
> I need to drop the table and recreate it. Is this the case? Assuming it
> is, I've run into the following problem. I need to add a description to
the
> fields when I re-create the table, but I can't seem to find the syntax.
For
> my CREATE function, it's as simple as:
> CREATE TABLE TstTable
> (
> ACC_ID int identity primary key,
> Code varchar(50),
> CodeType varchar(15),
> ActionType varchar(10),
> ActionBy varchar(50),
> ChangeDate datetime,
> ChangeTime varchar(20)
> )
> ...but I can't figure out how to put a description for each field in.
> Anyone know the syntax, or if it's impossible?
> Thanks,
> James
>|||James -
Have a look at dbcc checkident for the other issue of resetting the
identity.
Mike John
"James" <cppjames@.aol.com> wrote in message
news:OE4FGA72EHA.1192@.tk2msftngp13.phx.gbl...
> Nevermind...seems like sp_addextendedproperty will do what I'm looking
> for.
> Thanks anyway!
>
> "James" <cppjames@.aol.com> wrote in message
> news:eYEmV862EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> I'm setting up a DTS to modify a table, and I need to "reset" the
>> identity
>> field. I had originally wanted to do this with an ALTER TABLE command,
> but
>> apparently it isn't as straightforward as that. So at this point I
> believe
>> I need to drop the table and recreate it. Is this the case? Assuming it
>> is, I've run into the following problem. I need to add a description to
> the
>> fields when I re-create the table, but I can't seem to find the syntax.
> For
>> my CREATE function, it's as simple as:
>> CREATE TABLE TstTable
>> (
>> ACC_ID int identity primary key,
>> Code varchar(50),
>> CodeType varchar(15),
>> ActionType varchar(10),
>> ActionBy varchar(50),
>> ChangeDate datetime,
>> ChangeTime varchar(20)
>> )
>> ...but I can't figure out how to put a description for each field in.
>> Anyone know the syntax, or if it's impossible?
>> Thanks,
>> James
>>
>

ALTER TABLE MyT ALTER COLUMN IdtyCol t_idty NOT NULL Identity

Hello World,
Is there a way not to drop and create the table with temp table to alter a
column as in the subject?
Thanks,
C TO
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
Ehm, as far as I know, the "subject" should work just fine.
Is there an error that you're getting? If so, what is it?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||YOu have to recreate the column on order to create a identity column:
http://www.windowsitpro.com/Article...2080/22080.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"C TO" <CTO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2193C5FD-CC98-417A-90EC-751F71128B19@.microsoft.com...
> Hello World,
> Is there a way not to drop and create the table with temp table to alter a
> column as in the subject?
> Thanks,
> C TO|||
a
> Ehm, as far as I know, the "subject" should work just fine.
> Is there an error that you're getting? If so, what is it?
Woops, mixed this up with "not null".
Bugger.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

Alter Table Alter Column

I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity
after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know
the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL

CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT
(0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

You can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.

But there are several ways to INSERT identity values into a table which
already has the IDENTITY property - I suspect that's what you're really
looking for. For loading a text file with BULK INSERT or bcp.exe, there are
options to keep identity values when you import (KEEPIDENTITY and the -E
switch, respectively). For INSERTs from another table, you can use SET
IDENTITY_INSERT ON.

Finally, DBCC CHECKIDENT is used after you've INSERTed, to make sure that
the identity seed is consistent with the table data. See Books Online for
more details on all these commands.

Simon|||Thanks for the help!

CBL

"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

Alter table : identity col

I would like to add Indetntiy property to exitinng column
and would like to start it from 100. Could anybody please
give me the syntaxHi James
You have to drop the table & re-create it afaik.
Here's an example:
set nocount on
go
-- do it in a tran for safety
begin transaction
go
-- set up demo table
create table t1 (
c1 int not null primary key
, c2 char(1) not null
)
go
-- insert a demo row
insert into t1 (c1, c2) values (1, 'a')
go
-- set up a temp table with identity on the column
create table t1_temp (
c1 int not null identity (99, 1) primary key
, c2 char(1) not null
)
go
-- populate the temp table
set identity_insert t1_temp on
insert into t1_temp (c1, c2) select c1, c2 from t1
set identity_insert t1_temp off
go
-- destroy the original table
drop table t1
go
-- rename the temp table to t1
exec sp_rename 't1_temp', 't1'
go
-- insert another row to test
insert into t1 (c2) values ('b')
go
-- check results
select * from t1
go
-- clean up
rollback
go
Things might be a little more complicated if you're using schema binding for
stored procs / views & you might want to flush your proc cache too if you've
got stored procs using the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"james" <anonymous@.discussions.microsoft.com> wrote in message
news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> I would like to add Indetntiy property to exitinng column
> and would like to start it from 100. Could anybody please
> give me the syntax|||Greg
I think we can use the same table to add identity property
create table t
(
col int not null primary key,
col2 char(1) not null
)
go
insert into t values (1,'a')
insert into t values (2,'b')
go
alter table t add col1 int identity(1,1)
go
alter table t drop constraint PK__t__41D98783
go
alter table t drop column col
go
EXEC sp_rename 't.col1', 'col', 'COLUMN'
go
select * from t
go
drop table t
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> > I would like to add Indetntiy property to exitinng column
> > and would like to start it from 100. Could anybody please
> > give me the syntax
>|||Greg
Sorry, did not read OP to the end. My example doesnot resolve his problem
because he wants to start from 100.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx.gbl...
> > I would like to add Indetntiy property to exitinng column
> > and would like to start it from 100. Could anybody please
> > give me the syntax
>

Alter table : identity col

I would like to add Indetntiy property to exitinng column
and would like to start it from 100. Could anybody please
give me the syntaxHi James
You have to drop the table & re-create it afaik.
Here's an example:
set nocount on
go
-- do it in a tran for safety
begin transaction
go
-- set up demo table
create table t1 (
c1 int not null primary key
, c2 char(1) not null
)
go
-- insert a demo row
insert into t1 (c1, c2) values (1, 'a')
go
-- set up a temp table with identity on the column
create table t1_temp (
c1 int not null identity (99, 1) primary key
, c2 char(1) not null
)
go
-- populate the temp table
set identity_insert t1_temp on
insert into t1_temp (c1, c2) select c1, c2 from t1
set identity_insert t1_temp off
go
-- destroy the original table
drop table t1
go
-- rename the temp table to t1
exec sp_rename 't1_temp', 't1'
go
-- insert another row to test
insert into t1 (c2) values ('b')
go
-- check results
select * from t1
go
-- clean up
rollback
go
Things might be a little more complicated if you're using schema binding for
stored procs / views & you might want to flush your proc cache too if you've
got stored procs using the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"james" <anonymous@.discussions.microsoft.com> wrote in message
news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
> I would like to add Indetntiy property to exitinng column
> and would like to start it from 100. Could anybody please
> give me the syntax|||Greg
I think we can use the same table to add identity property
create table t
(
col int not null primary key,
col2 char(1) not null
)
go
insert into t values (1,'a')
insert into t values (2,'b')
go
alter table t add col1 int identity(1,1)
go
alter table t drop constraint PK__t__41D98783
go
alter table t drop column col
go
EXEC sp_rename 't.col1', 'col', 'COLUMN'
go
select * from t
go
drop table t
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>|||Greg
Sorry, did not read OP to the end. My example doesnot resolve his problem
because he wants to start from 100.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eGs#FA$9DHA.3488@.tk2msftngp13.phx.gbl...
> Hi James
> You have to drop the table & re-create it afaik.
> Here's an example:
> set nocount on
> go
> -- do it in a tran for safety
> begin transaction
> go
> -- set up demo table
> create table t1 (
> c1 int not null primary key
> , c2 char(1) not null
> )
> go
> -- insert a demo row
> insert into t1 (c1, c2) values (1, 'a')
> go
> -- set up a temp table with identity on the column
> create table t1_temp (
> c1 int not null identity (99, 1) primary key
> , c2 char(1) not null
> )
> go
> -- populate the temp table
> set identity_insert t1_temp on
> insert into t1_temp (c1, c2) select c1, c2 from t1
> set identity_insert t1_temp off
> go
> -- destroy the original table
> drop table t1
> go
> -- rename the temp table to t1
> exec sp_rename 't1_temp', 't1'
> go
> -- insert another row to test
> insert into t1 (c2) values ('b')
> go
> -- check results
> select * from t1
> go
> -- clean up
> rollback
> go
> Things might be a little more complicated if you're using schema binding
for
> stored procs / views & you might want to flush your proc cache too if
you've
> got stored procs using the table.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "james" <anonymous@.discussions.microsoft.com> wrote in message
> news:1413101c3f7e7$c92ea810$a601280a@.phx
.gbl...
>

ALTER TABLE ... IDENTITY question....

Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
StuCheck out DBCC CHECKIDENT in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stu" <s.lock@.cergis.com> wrote in message
news:uGBp9R1RGHA.5500@.TK2MSFTNGP12.phx.gbl...
Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
Stu

Alter table - add default value

Helo Group,
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:

> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

Alter Seed value of Identity column

Hi,
I want to create temporary table, say "a" which has a column say "col1"
which i wnt to be an identity for which I need to provide a seed value.
I tried the following
1. Create a Table with Identity seed,value as (1,1)
2. Tried to alter the table using "alter table a alter column col1
IDENTITY (500,1)" but this fails saying that "Server: Msg 156, Level 15,
State 1, Line 1 Incorrect syntax near the keyword 'IDENTITY'."
Any idea how to do this
Note: Since it is a temporary table I can't create a dynamic query bcoz the
table will be in tht context and later on will be destroyed (this is wht I
observed, correct me if I am wrong :) )
TIA
Thnx
PSee DBCC CHECKINDENT command in SQL Server Books Online.
--
Anith|||Thnx it worked !!!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%231WOwn90GHA.720@.TK2MSFTNGP02.phx.gbl...
> See DBCC CHECKINDENT command in SQL Server Books Online.
> --
> Anith
>

alter indentity field

Hi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
ShaffiqHi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
Shaffiq

alter table table_name
alter column column_Name int not null


that should solve your problem.|||Hi Enigma,
I'd tried it before but it not work. Even query analyzer return success message "The command(s) completed successfully." but when I open the table it still the same. And the identity field still ON

Regards,
Shaffiq|||do alteration in Enterprise Manager( dont save it) and click on 'save change script'(3 rd button from second row).copy and run that script in query analyser|||or

Alter Table MyTable ADD NewColumn int
GO
UPDATE MyTAble SET NewColumn = OldColumn
GO
ALTER TABLE MyTable DROP COLUMN MyCOlumn
GO
sp_rename 'MyTable.NewColumn','OldColumn',COLUMN

alter identity property of a column to NOT FOR REPLICATION

i need to alter all foreign keys in my database and uncheck the
"Enforce relationship for replication" check box. Using the EM, I
extracted the code snippet below. unfortunately, when i run this test
from query analyzer, then go back into the EM, the box is still
checked.

can anyone tell me what i am missing? any advice on unsetting this
attribute globally would be appreciated!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo
DROP CONSTRAINT FK_CustomerCustomerDemo_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.CustomerCustomerDemo WITH NOCHECK ADD CONSTRAINT
FK_CustomerCustomerDemo_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
) NOT FOR REPLICATION

GO
COMMIT

thanks!!dayong (reedmb89@.yahoo.com) writes:
> i need to alter all foreign keys in my database and uncheck the
> "Enforce relationship for replication" check box. Using the EM, I
> extracted the code snippet below. unfortunately, when i run this test
> from query analyzer, then go back into the EM, the box is still
> checked.

It's not simply a refresh issue? I was not able to reproduce this, of
the simple reason that I was not able find where you poke with FKs in
Enterprise Manager. I prefer to work exclusively with SQL statements
for DDL statements.

You can use "sp_helpconstraint" in Query Analyzer to verify the status
of the constraint.

> can anyone tell me what i am missing? any advice on unsetting this
> attribute globally would be appreciated!

As long as you know which the foreign keys are, going like the code you
included should not be a problem.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
first, obviously, i'm new to sql server. thanks for your advice so far.
you are correct, it was a refresh issue. unfortunately, i cannot find a
simple way to find the foreign keys that are set for replication. i
looked at the stored procedure you advised (sp_helpconstraint). it
appears to create a temp table and then query and join info and
eventually has a boolean value where if true is_for_replication and
false not_for_replication.

this code is greek to me in my early stages of sql server
administration. is there a simpler way to locate the keys and columns
that are set is_for_replication?

thanks in advance for any advice!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Michael Reed (anonymous@.anonymous.com) writes:
> first, obviously, i'm new to sql server.

If you find out how get the commands that EM runs, and run them
in Query Analyzer, you have come a long way compared to many other
SQL Server newbies!

> this code is greek to me in my early stages of sql server
> administration. is there a simpler way to locate the keys and columns
> that are set is_for_replication?

This SELECT lists all foreign key constraints that are set for replication,
and the parent table:

select tbl = object_name(parent_obj), fk_name = name
from sysobjects
where xtype = 'F' and objectproperty(id, 'CnstIsNotRepl') = 0
order by tbl, fk_name

I don't know how many constraints you have. If you have only a handful,
you might be able to the rest manually. If you have hundreds of table,
you probably want a list of the columns in each FK. Since I'm lazy, and
I don't have a query ready for that right now, I don't include one. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland

please re-post your last response. i can only see the summary. when i
click on the link, your post is nowhere to be found.

please re-post.

thanks!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>

Alter Identity Column question

What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||I thought so...
I ran profiler and couldn't pick up any Alter statement, so I kinda expected this answer.
Thanx anyway!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ONWdeetMEHA.2244@.tk2msftngp13.phx.gbl...
JLS,
this is not possible in TSQL. You can do it in EM, but if you run profiler you'll see that a huge amount of work goes on behind the scenes, including the creation, population and renaming of a temporary table.
HTH,
Paul Ibison
|||I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!
|||AWESOME! That's the answer, THANK YOU!
Now I will pay you back by buying your book once it hits the market. :-)
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:%23f7rE0BNEHA.4036@.TK2MSFTNGP12.phx.gbl...
run this in your publication database. Here I am setting the identity column for the jobs table to NFR
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0 and colstat & 0x0008 = 0 and id=object_id('jobs')
GO
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:O3iV6P2MEHA.1608@.TK2MSFTNGP12.phx.gbl...
I'm sorry, but I don't understand what this will do. Where do I place the table/column name of the identity column that I want to change to "Not for Replication" in your script?
What will this change to syscolumns provide? A default of setting all my identity columns to "Yes (Not for Replication)"?
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:OL9Z4FxMEHA.740@.TK2MSFTNGP12.phx.gbl...
try this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23gElYLsMEHA.620@.TK2MSFTNGP10.phx.gbl...
What is the syntax for changing an identity column in a table to "Not For Replication"?
Alter table x
?? identity column, Not For Replication
Thanx!