Change Increment Value for existing Identity Column

Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?

I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)

but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .

Thanks,
NataliaYou have to drop and recreate the table.sql

Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

change default value of column

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

try doing an ALTER TABLE|||I tried but it was not working|||

Quote:

Originally Posted by sourabhmca

I tried but it was not working

if this is a one time thing, try creating a new field then drop the existing one.|||oh yeah, you have to rename the field to the old field name after dropping the old one.

IF this is a one time thing...|||

Quote:

Originally Posted by sourabhmca

Hi friends,

I have a table :
create table abc (a int,b smalldatetime default getdate())
then insert value like
insert into abc(a) values(1)
now I want to change default value of column b be getdate()+(0.435)
so how can I do ?
please help. Its urgeny.

Hey try like this...

create table abc (a int,b smalldatetime constraint df_abc_b default getdate())
insert into abc(a) values(1)
select * from abc

alter table abc drop constraint df_abc_b
alter table abc add constraint df_abc_b default(getdate()+0.435) for b
insert into abc(a) values(1)
select * from abc

change default value

How do you change a default value for a column in tsql?
cheers
Chalie.drop existing default constraint using statement.
ALTER TABLE <table>
DROP CONSTRAINT <default constraint name>
Recreate the new default constraint using :
ALTER TABLE <table> ADD CONSTRAINT
<constraint name> DEFAULT 'x' FOR <column name>
-Vishal
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> How do you change a default value for a column in tsql?
> cheers
> Chalie.
>|||What about something like this
CREATE TABLE DefVals(col1 int CONSTRAINT def_col1 DEFAULT 1, col2 int)
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
GO
ALTER TABLE DefVals DROP CONSTRAINT def_col1
GO
ALTER TABLE DefVals ADD CONSTRAINT def_col1 DEFAULT 4 FOR col1
GO
INSERT DefVals(col2) VALUES(1)
GO
SELECT * FROM DefVals
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Hello Charlie !
The Default Value is a constraint that you can change:
Sop you have to DROP and ADD the new constraint.
HTH, Jens Süßmeyer.|||Hi,
Thanks for this, but how do I find out what my constraint name is?
Charlie
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> drop existing default constraint using statement.
> ALTER TABLE <table>
> DROP CONSTRAINT <default constraint name>
> Recreate the new default constraint using :
> ALTER TABLE <table> ADD CONSTRAINT
> <constraint name> DEFAULT 'x' FOR <column name>
>
> --
> -Vishal
> "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > How do you change a default value for a column in tsql?
> >
> > cheers
> > Chalie.
> >
> >
>|||Try
Exec sp_helpconstraint 'TABLE NAME'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Charlie,
EXEC sp_helpconstraint <tablename>
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:qRI3b.453$b82.171440@.newsfep1-win.server.ntli.net...
> Hi,
> Thanks for this, but how do I find out what my constraint name is?
> Charlie
> "Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
> news:uk9GvVibDHA.1580@.tk2msftngp13.phx.gbl...
> > drop existing default constraint using statement.
> >
> > ALTER TABLE <table>
> > DROP CONSTRAINT <default constraint name>
> >
> > Recreate the new default constraint using :
> > ALTER TABLE <table> ADD CONSTRAINT
> > <constraint name> DEFAULT 'x' FOR <column name>
> >
> >
> > --
> > -Vishal
> >
> > "charlie B" <Charlie.remove@.freeuk.com> wrote in message
> > news:B6H3b.338$b82.140399@.newsfep1-win.server.ntli.net...
> > > How do you change a default value for a column in tsql?
> > >
> > > cheers
> > > Chalie.
> > >
> > >
> >
> >
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql

change datatype of a column

what is the best (and fast) why to change a column's datatype from varchar to decimal ?
the table has 3 million records and the column is filled with data (no problem with converting the data to numeric).alter table tablename
alter column columnname float null|||tnx

Change datatype from varchar to bigint not working

Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi

This should help you:
http://www.sqlteam.com/article/default-constraint-names

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!
It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
Paul
Paul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>
|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT@.String = 'Hello'
SELECTDATALENGTH(@.String)
SELECTDATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

Change data type

Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
Wait till "off hours"/ drop Subscription/ drop article/ change data type on both tables/ add article/ add subscription. If theres no good time to do it, you can re-snapshot the data as well.
"David" <davbarquero@.hotmail.com> wrote in message news:OvRppwFnEHA.3196@.TK2MSFTNGP10.phx.gbl...
Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
|||David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje news:eOJxZYJnEHA.3428@.TK2MSFTNGP11.phx.gbl...
David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage
e.g
DECLARE @.string VARCHAR(10
SELECT @.String = 'Hello
SELECT DATALENGTH(@.String
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10))
Regard
Juli
http://www.sqlporn.co.uk :o)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requireme
nts. If you have long string lengths you need to remember that nvarchar will
double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT @.String = 'Hello'
SELECT DATALENGTH(@.String)
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

change column width

Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, CalinTester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar
80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NUL
L
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com

change column width

Hi there,
How would I go about changing column width from varchar 40 to varchar 80
in SQL server 2000? Is it possible without losing data?
Thanks, Calin
Tester wrote:
> Hi there,
> How would I go about changing column width from varchar 40 to varchar 80
> in SQL server 2000? Is it possible without losing data?
> Thanks, Calin
>
>
See ALTER TABLE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
or
ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
|||pl wrote:
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NOT NULL
> or
> ALTER TABLE tblToChange ALTER COLUMN [colToChange] VARCHAR(80) NULL
Look at alter table in books online.
Since you are increasing length dont worry about data loss.
Regards
Amish Shah
http://shahamishm.tripod.com