altering dynamically a set data type

Hi

suppose that I have a set named 'my_set' wich contain
'aaa','bbb','ccc'
and I want to alter with an sql command the structure to get 'ddd' in
addition. How can I do that?Does "My_Set" refer to a table? If so, you add a new column to the table
using the ALTER TABLE statement. For example:

ALTER TABLE my_set ADD ddd INTEGER NULL

--
David Portas
SQL Server MVP
--|||jfbeaulieu2003@.yahoo.com (J.Beaulieu) wrote in message news:<eb1b8a74.0408101906.49a3bf1b@.posting.google.com>...
> Hi
> suppose that I have a set named 'my_set' wich contain
> 'aaa','bbb','ccc'
> and I want to alter with an sql command the structure to get 'ddd' in
> addition. How can I do that?

MSSQL doesn't have a 'set data type' - are you using MySQL, perhaps?
If so, you will get a better answer in a MySQL forum.

Simon|||No, my_set is a field, a SET data type

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<78GdnU9yacB-aoTcRVn-iA@.giganews.com>...
> Does "My_Set" refer to a table? If so, you add a new column to the table
> using the ALTER TABLE statement. For example:
> ALTER TABLE my_set ADD ddd INTEGER NULL

>> Hi
>>
>> suppose that I have a set named 'my_set' wich contain
>> 'aaa','bbb','ccc'
>> and I want to alter with an sql command the structure to get 'ddd' in
>> addition. How can I do that?|||J.Beaulieu (jfbeaulieu2003@.yahoo.com) writes:
> No, my_set is a field, a SET data type

There is no SET datatype in SQL Server (unless you it's a user-defined
type you added yourself.) Are you in the wrong newsgroup?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no base datatype called "SET" in SQL Server.

You may have a user-defined datatype of that name. There is no way to alter
the definition of an existing datatype - you have to drop it and then
recreate it - but I'm not even sure if your question relates to altering the
datatype or the data within it.

Simon suggested that maybe this is a MySQL question. If that's the case then
please try a MySQL forum. This is a SQL Server group.

--
David Portas
SQL Server MVP
--

Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

ALTER'ing an XML Column

I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.

I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...

There has to be an easier way...

Thanks!!

You can first alter the column to untyped xml:

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;

Then modify or create the new schema

Lastly, change the column to the new schema (the data in the table must comply with the new schema)

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;

ALTER'ing an XML Column

I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.

I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...

There has to be an easier way...

Thanks!!

You can first alter the column to untyped xml:

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;

Then modify or create the new schema

Lastly, change the column to the new schema (the data in the table must comply with the new schema)

ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?
The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>sql

alter text column

Hi

I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column.

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

ALTER TABLE TableName ADD ColumnName text NULL
UPDATE TableName SET ColumnName = ColumnName_old
ALTER TABLE TableName DROP COLUMN ColumnName_old

However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist.

However then I tried to execute these queries one by one I was able to do that.

Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.

any help would be appreciated.

Himani

Your logic should work with a little change. Add a GO between each batch.

Like:

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

GO

ALTER TABLE TableName ADD ColumnName text NULL

GO
UPDATE TableName SET ColumnName = ColumnName_old

GO
ALTER TABLE TableName DROP COLUMN ColumnName_old

GO

After each DML statement executed, you should get what you want.

By the way, it seems you can change the colum with text datatype from not null to allow null directly from the table definition in SQL 2005 Management Studio.

Also, you can directly do the update like this:

UPDATE yourTable

Set yourNewcolumTextAllowNull=youroldcolumnnotAllowNull

HTH

|||

Thanks a lot limno,it worked !!!

:-)

sql

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:

>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP

alter table without data type

I would like to set a field to not null which I can do like this: ALTER TABLE table1 ALTER COLUMN field1 BIGINT NOT NULL

My question is there anyway to set this field to NOT NULL without having to specify the data type BIGINT? I'm using dynamic sql and if there was a way then I wouldn't need to go get the data type of the field dynamically and it would save me a step. Thanks.Why are using dynamic SQL in the first place?|||I'm creating tables from a DB2 database and importing it's data. I have to dynamically do this because the tables on the DB2 side are constantly changing.|||So when the tables change (and yes you need to supply the datatype) in DB2, how do you apply the schema changes? Are you using ERWin?

And what do you mean by constantly? Is this a production database?

And what schema changes are you applying...I mean it could get kind of hairy doing everything...indexes, constraints...it could get ugly...

Are you taking those things in to account?|||I'm doing a full refresh (drop/create) of the tables. Not using ERWin, just stored procedures.

"Constantly" may be a bad term. It is a production database (DB2 side) so the changes are minimal, but I do not want to maintain changes on the MSSQL side.

I created a table to relate keys of different tables I'm pulling in. So the schema I'm developing is controlled.

It's looking like the best way to do this is to see if the column needs to be NOT NULL on creation.

Alter table with merge replication

Hello,
We are using sql2000.
1.Is there a better way to change field data type with
merge replication than add column, copy data and then drop
column?
2.How can i create index with merge replication?
3.If the only way is stop and restart the replication,
what is the easiest way to do so?
Many Many Thanks For Reply.
>
> We are using sql2000.
> 1.Is there a better way to change field data type with
> merge replication than add column, copy data and then drop
> column?
No, there isn't. You use sp_repladdcolumn to add the new column and then
sp_repldropcolumn to remove the old column. Note that ALTER TABLE and ALTER
COLUMN does this transparently.

> 2.How can i create index with merge replication?
>
You use the @.schema_option argument of the sp_repladdcolumn to generate a
corresponding index. A value of 0x010 will generate a clustered index and
0x40 will generate a nonclustered index.

> 3.If the only way is stop and restart the replication,
> what is the easiest way to do so?
You don't need to break merge replication to perform make schema changes in
SQL Server 2000.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Alter table with merge replication

Hello,
We are using sql2000.
1.Is there a better way to change field data type with
merge replication than add column, copy data and then drop
column?
2.How can i create index with merge replication?
3.If the only way is stop and restart the replication,
what is the easiest way to do so?
Many Many Thanks For Reply.>
> We are using sql2000.
> 1.Is there a better way to change field data type with
> merge replication than add column, copy data and then drop
> column?
--
No, there isn't. You use sp_repladdcolumn to add the new column and then
sp_repldropcolumn to remove the old column. Note that ALTER TABLE and ALTER
COLUMN does this transparently.
> 2.How can i create index with merge replication?
>
--
You use the @.schema_option argument of the sp_repladdcolumn to generate a
corresponding index. A value of 0x010 will generate a clustered index and
0x40 will generate a nonclustered index.
> 3.If the only way is stop and restart the replication,
> what is the easiest way to do so?
--
You don't need to break merge replication to perform make schema changes in
SQL Server 2000.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

alter table set default value for money type column

I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:

>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql

Alter Table

How to change field type using ALTER TABLE?Originally posted by Mike Borozdin
How to change field type using ALTER TABLE?

alter table table_name alter column column_name new datatype

Alter several columns using ALTER COLUMN

Hi!

I have a table with 6 columns of type int. I would like to alter this table and set these columns to float. Is it possible to do it using only one statement? I don't want to use the following query six times!

ALTER TABLE T1

ALTER COLUMN C1 FLOAT NOT NULL

Thank you!

What happens when you try to alter multiple columns in one statement?

Alter of text type field

Can I change the datatype for a particular field which was previously set to text?If possible then how?Secondly can I change the datatype of a filed of int type to identity after inserting data?Can I remove identity property from a field?USE Northwind
GO

CREATE TABLE myTable98 (Col1 int, Col2 text)
GO

INSERT INTO myTable98 (Col1, Col2)
SELECT 1, REPLICATE('X',8001) UNION ALL
SELECT 2, 'Hi! How the hell are you' UNION ALL
SELECT 3, 'X'
GO

ALTER TABLE myTable98 ALTER Column Col2 varchar(8000)
GO
-- No Good
ALTER TABLE myTable98 ADD Col3 varchar(8000)
GO

UPDATE myTable98 SET Col3 = Col2

SELECT Col1, LEN(Col3), Col3 FROM myTable98

ALTER TABLE myTable98 DROP Column Col2
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable98
GO

Look up ALTER in Books Online for more....

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