Sql Server Alignment problem: February 2012

Saturday, February 25, 2012

Alter Column Question

Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
--
David Portas

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.

Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...

CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)

The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.

Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO

and then...

Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.

Database restored from scratch and schema collection created, operations=

Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.

Restore Database as before and create schema collection as before

Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.

If you delete all the rows then the ALTER COLUMN completes without error.

A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.

All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.

Hi Mark,

It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.

Thanks

Jinghao Liu

SQL Server Relational Engine XML team

|||

FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro

Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection

I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup

I have not had a problem with update since I used the new filegroup

|||

Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.

Thanks

Jinghao Liu - SQL Server Engine

|||

Database information was sent to Jianghao Liu directly as requested. The following email was then received...

A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!

Have a nice weekend!

Jinghao

|||

This reply has just been received from MSFT:

This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.

If you are developing application base on continually ALTER COLUMN, then you need to redesign it.

Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?

So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?

And we wonder why people go anti-MSFT and turn to other platforms.

Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!

~swg

|||

I agree with swg that this response is unacceptable.

I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed

It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.

It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:

The minimum number of schemas needed are put into each collection - giving smaller collections|||

Hi!

Found a workaround. Good for solving other problems, too.

The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.

Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).

The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.

The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.

When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.

Hope, I could help.

Bye:

Barrez

|||

Thank you for your help

Regards

Mark Dooley

|||

Hey thanks for this,

Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?

I'll get Mark to mark your post as the Answer!

Cheers,

~swg

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...

CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)

The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.

and then...

Database restored from scratch and schema collection created, operations=

Restore Database as before and create schema collection as before

If you delete all the rows then the ALTER COLUMN completes without error.

A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.

All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.

Hi Mark,

It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.

Thanks

Jinghao Liu

SQL Server Relational Engine XML team

|||

FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro

Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection

I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup

I have not had a problem with update since I used the new filegroup

|||

Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.

Thanks

Jinghao Liu - SQL Server Engine

|||

Database information was sent to Jianghao Liu directly as requested. The following email was then received...

A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!

Have a nice weekend!

Jinghao

|||

This reply has just been received from MSFT:

If you are developing application base on continually ALTER COLUMN, then you need to redesign it.

And we wonder why people go anti-MSFT and turn to other platforms.

Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!

~swg

|||

I agree with swg that this response is unacceptable.

It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:

The minimum number of schemas needed are put into each collection - giving smaller collections|||

Hi!

Found a workaround. Good for solving other problems, too.

Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).

The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.

When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.

Hope, I could help.

Bye:

Barrez

|||

Thank you for your help

Regards

Mark Dooley

|||

Hey thanks for this,

I'll get Mark to mark your post as the Answer!

Cheers,

~swg

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...

CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)

The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.

and then...

Database restored from scratch and schema collection created, operations=

Restore Database as before and create schema collection as before

If you delete all the rows then the ALTER COLUMN completes without error.

A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.

All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.

Hi Mark,

It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.

Thanks

Jinghao Liu

SQL Server Relational Engine XML team

|||

FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro

Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection

I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup

I have not had a problem with update since I used the new filegroup

|||

Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.

Thanks

Jinghao Liu - SQL Server Engine

|||

Database information was sent to Jianghao Liu directly as requested. The following email was then received...

A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!

Have a nice weekend!

Jinghao

|||

This reply has just been received from MSFT:

If you are developing application base on continually ALTER COLUMN, then you need to redesign it.

And we wonder why people go anti-MSFT and turn to other platforms.

Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!

~swg

|||

I agree with swg that this response is unacceptable.

It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:

The minimum number of schemas needed are put into each collection - giving smaller collections|||

Hi!

Found a workaround. Good for solving other problems, too.

Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).

The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.

When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.

Hope, I could help.

Bye:

Barrez

|||

Thank you for your help

Regards

Mark Dooley

|||

Hey thanks for this,

I'll get Mark to mark your post as the Answer!

Cheers,

~swg

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...

CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)

The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.

and then...

Database restored from scratch and schema collection created, operations=

Restore Database as before and create schema collection as before

If you delete all the rows then the ALTER COLUMN completes without error.

A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.

All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.

Hi Mark,

It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.

Thanks

Jinghao Liu

SQL Server Relational Engine XML team

|||

FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro

Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection

I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup

I have not had a problem with update since I used the new filegroup

|||

Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.

Thanks

Jinghao Liu - SQL Server Engine

|||

Database information was sent to Jianghao Liu directly as requested. The following email was then received...

A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!

Have a nice weekend!

Jinghao

|||

This reply has just been received from MSFT:

If you are developing application base on continually ALTER COLUMN, then you need to redesign it.

And we wonder why people go anti-MSFT and turn to other platforms.

Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!

~swg

|||

I agree with swg that this response is unacceptable.

It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:

The minimum number of schemas needed are put into each collection - giving smaller collections|||

Hi!

Found a workaround. Good for solving other problems, too.

Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).

The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.

When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.

Hope, I could help.

Bye:

Barrez

|||

Thank you for your help

Regards

Mark Dooley

|||

Hey thanks for this,

I'll get Mark to mark your post as the Answer!

Cheers,

~swg

ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text'
I can change it through design view in Enterprise manager, after clicking ok
on the warning message, but I need to find a way to override this error in
Script.
Is there a way I can overide the fact that it is a text column and change it
to varchar?
Thanks in advance!
PaulEXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>|||When faced with situations like this, it might be helpful for you to
know that you can save the change script (third icon on standard
toolbar) from the Enterprise Manager which will show you how the change
is implemented by the EM. Granted, the method that is implemented is
usually not how I would do it, but it's helpful in a pinch.
In this case, I created and saved a table with a single text column,
and then changed it to a varchar column; this is the script EM used to
implement the change:
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
CREATE TABLE dbo.Tmp_test1
(
test varchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.test1)
EXEC('INSERT INTO dbo.Tmp_test1 (test)
SELECT CONVERT(varchar(50), test) FROM dbo.test1 TABLOCKX')
GO
DROP TABLE dbo.test1
GO
EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT'
GO
COMMIT
HTH,
Stu|||Thanks Guys!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>

ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
|||Cool, but this code will add the column to the end of the table, the column "purpose" will be the last one in "select * from cal_respurpose". this could be risky if the software or the store procedures performs an insert based on the columns indices.
so what i would suggest is to copy the table to a new table ( with new stucture ) after backing up the table and renaming the new one to the original table name.
I don't know if there is a way to preserve the columns order.
Faris

Quote:

Originally Posted by Paul B View Post

Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul

alter column of a replicated table?

How do I alter the column of the replicated table ?
If you have SQL Server 2005 then alter table will work for most changes. If
it's SQL 2000 then there are some workarounds. Please take a look at these 2
articles:
http://www.replicationanswers.com/AlterSchema2005.asp
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For SQL 2000 you have to pipe the values of the column you wish to change to
a temp table along with key information. Then use sp_repldropcolumn to drop
the column and sp_repladdcolumn to add it back with the new width/datatype.
Then push the content back into the base table from the temp table.
For SQL 2005 with replicate_ddl = true by default you can use alter table
statements.
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
"DallasBlue" <DallasBlue@.discussions.microsoft.com> wrote in message
news:D3070003-28E4-4568-93FC-7A2E4DF94B39@.microsoft.com...
> How do I alter the column of the replicated table ?
>

ALTER COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.
Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:

> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.
|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
http://www.aspfaq.com/
(Reverse address to reply.)
|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:

> with
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

ALTER COLUMN NAME?

I'm starting to think that there is no way to simply ALTER a column name with
T-SQL. Is that true?
That means I need to do it in EM, where it will do all that ugly
drop/re-create table stuff?
Any help would be appreciated.Hi
Yes, that is true.
Cheers
Mike
"Steve Z" wrote:
> I'm starting to think that there is no way to simply ALTER a column name with
> T-SQL. Is that true?
> That means I need to do it in EM, where it will do all that ugly
> drop/re-create table stuff?
> Any help would be appreciated.|||Thanks...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, that is true.
> Cheers
> Mike
> "Steve Z" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name with
> > T-SQL. Is that true?
> >
> > That means I need to do it in EM, where it will do all that ugly
> > drop/re-create table stuff?
> >
> > Any help would be appreciated.|||> I'm starting to think that there is no way to simply ALTER a column name
with
> T-SQL. Is that true?
NO!
EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Thank you very, very much - that is much neater...
"Aaron [SQL Server MVP]" wrote:
> > I'm starting to think that there is no way to simply ALTER a column name
> with
> > T-SQL. Is that true?
> NO!
> EXEC sp_rename 'tablename.old_column_name', 'new_column_name', 'COLUMN'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Alter column name of table?

Have any way to alter column name of table?
ThanksLook up sp_rename in the BOL. I could tell you how to do it, but you'd
probably get more information there.
Stu
Retf wrote:
> Have any way to alter column name of table?
> Thanks|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Alter column name from SQL?

How can you rename a column in a table (from c# code, preferrably from a SQL script command) without deleting the column and re-creating it with a new name?

Hello,

SQL Server contains a system stored procedure called SP_RENAME which can be used to rename user created object (tables, column, sp's, ...)

For more information, look at the BOL documentation on the subject http://msdn2.microsoft.com/en-us/library/ms188351.aspx (this is the 2005 version, but the system stored procedure also exist in previous version of SQL server)

Hope this helps,

|||Unfortunately this is not implemented in SQL Everywhere - you can only access the renaming APIs via OLE DB. You can always write a wrapper for .NET as I did.|||Guess I reacted to hasty. Thanks for the correction.|||

As of now we don't have direct method (e.g. SP support) for renaming column in SQL Server Everywhere Edition.

Thanks
Sachin

Alter column name

Hi,
Can I change the name of the column using alter command .
Is there any such option?
Provide me different ways of changing column name in a table.
Thanks & Regards,
Vani Jyothsna.From BOL
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
"Jyothsna" <jyothsnat@.dev.visualsoft-tech.com> wrote in message
news:uk8rWF9TFHA.752@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can I change the name of the column using alter command .
> Is there any such option?
> Provide me different ways of changing column name in a table.
> Thanks & Regards,
> Vani Jyothsna.
>|||Hi Jyothsna:
Here is the solution for your problem:
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
This example renames the contact title column in the customers table to titl
e.
Hope this answers the question
thanks and regards
Chandra
"Jyothsna" wrote:

> Hi,
> Can I change the name of the column using alter command .
> Is there any such option?
> Provide me different ways of changing column name in a table.
> Thanks & Regards,
> Vani Jyothsna.
>
>|||Notice that using sp_rename, will not change the code that references this
column from views, stored procedures or functions.
Example:
use northwind
go
create table t1 (
c1 int
)
go
create procedure proc1
as
select c1 from t1
go
create view v1
as
select c1 from t1
go
create function f1()
returns table
as
return(select c1 from t1)
go
select
[text]
from
syscomments
where
(
object_name([id]) = 'proc1'
and objectproperty([id], 'IsProcedure') = 1
)
or
(
object_name([id]) = 'v1'
and objectproperty([id], 'IsView') = 1
)
or
(
object_name([id]) = 'f1'
and objectproperty([id], 'IsInlineFunction') = 1
)
go
exec sp_rename 't1.c1', 'c2', 'column'
go
select
table_name,
column_name
from
information_schema.columns
where
table_schema = 'dbo'
and table_name = 't1'
go
select
[text]
from
syscomments
where
(
object_name([id]) = 'proc1'
and objectproperty([id], 'IsProcedure') = 1
)
or
(
object_name([id]) = 'v1'
and objectproperty([id], 'IsView') = 1
)
or
(
object_name([id]) = 'f1'
and objectproperty([id], 'IsInlineFunction') = 1
)
go
drop procedure proc1
go
drop view v1
go
drop function f1
go
drop table t1
go
AMB
"Jyothsna" wrote:

> Hi,
> Can I change the name of the column using alter command .
> Is there any such option?
> Provide me different ways of changing column name in a table.
> Thanks & Regards,
> Vani Jyothsna.
>
>|||Several items have been thrown out there but I woul add that it i not
suggested you rename a column in a production system as there are not only
internal references to worry about but external (consider any effects on
existing applications). If this is for reference you could use the column
description to put a note in or support of an application you might use a
view with the coulmn aliased.
"Jyothsna" wrote:

> Hi,
> Can I change the name of the column using alter command .
> Is there any such option?
> Provide me different ways of changing column name in a table.
> Thanks & Regards,
> Vani Jyothsna.
>
>

Alter column in a table that is being published for replication.

Ever get this wonderful error?
"Cannot alter the table 'XXXX' because it is being published for
replication."
Many of the posts I have seen regarding this subject suggest that you
"trick" the server into thinking that the table is not being replicated
by messing with the system tables. Then change the column, then update
the syswhatever table again.
The following solution does not touch the system tables. Probably best
implemented as a stored procedure. Tested on MSSQL 2000 to MSSQL 2000
transactional replication.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*ALTER COLUMN WITH TRANSACTIONAL REPLICATION*/
declare @.tablename varchar(55)
declare @.columnname varchar(55)
declare @.datatype varchar(15)
declare @.strSQL varchar(5000)
set @.tablename = 'YOUR TABLENAME HERE'
set @.columnname = 'YOUR COLUMN NAME HERE'
set @.datatype = 'NEW DATA TYPE'
/************************************************** **********************
in order to change a column without dropping and recreating
subscriptions
we have to:
1) create a dummy column
2) copy data from original column to dummy column
3) drop the original column
4) create the original column with the new parameters
5) copy data from dummy column into new original column
6) drop the dummy column
************************************************** ***********************/
--create dummy column
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'', @.typetext = ''' + @.datatype + ''''
exec(@.strSQL)
--put the data in the dummy column
set @.strSQL = 'update ' + @.tablename + ' set temp_column = ' +
@.columnname
exec(@.strSQL)
--drop the original column
set @.strSQL = 'sp_repldropcolumn @.source_object = ''' + @.tablename +
''', @.column = ''' + @.columnname + ''''
exec(@.strSQL)
--re-create the original column with new parameters
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''' + @.columnname + ''', @.typetext = ''' + @.datatype +
''''
exec(@.strSQL)
--copy data from dummy column to new original
set @.strSQL = 'update ' + @.tablename + ' set ' + @.columnname + ' =
temp_column'
exec(@.strSQL)
--drop the dummy column
set @.strSQL = 'sp_repldropcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'''
exec(@.strSQL)
/*END ALTER COLUMN WITH TRANSACTIONAL REPLICATION*/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enjoy!
Ryan,
this looks as though it could be useful, and thanks for sharing your
personal code.
From within a sp though, I'd change the following type of code
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'', @.typetext = ''' + @.datatype + ''''
exec(@.strSQL)
to
exec sp_repladdcolumn @.source_object = @.tablename, @.column = 'temp_column',
@.typetext = @.datatype
(I think the update is the only bit that needs to be dynamic sql).
Rgds,
Paul Ibison

Alter column in a database that is a transactional replication publisher

When I need to add columns to my publisher in a transactional replication I
use the sp_repladdcolumn. How do I Alter a column with no need to stop and
recreate the replication?
@.force_reinit_subscription has a default of zero, so I'm wondering if you
set this explicitly to 1? If not, are you really getting a reinitialization,
or just an indication that the existing snapshot is no longer applicable?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

alter column for every tables

I got one same tables in 10 database, and I need to amend one field from
'null' to 'not null'
Can I do it by script ' Please helpAgnes
Do the table have data?
If you don't
CREATE TABLE #Test (col1 INT NULL)
--No data
ALTER TABLE #Test ALTER COLUMN col1 INT NOT NULL
If you do have a data
CREATE TABLE t(c1 int null)
insert t values(1)
SELECT c1 INTO tA FROM t
SELECT ISNULL(c1, 0) AS c1 INTO tB FROM t
EXEC sp_help tA
EXEC sp_help tB
Note
USE database_name;
EXEC sp_rename ...
Unfortunately, sp_rename doesn't allow you to specify the database name,
rather it will run in the context of the "current" database.
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:eLUNghRMGHA.3896@.TK2MSFTNGP15.phx.gbl...
>I got one same tables in 10 database, and I need to amend one field from
>'null' to 'not null'
> Can I do it by script ' Please help
>

alter column fails due to statistics

Hello,

A script we run against the database as part of the upgrade of our product
is failing with the following message:

ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
accesses this column

The line that fails is:

alter table Badge alter column EncodedID nvarchar(16)

It's clear that there's some kind of automatically generated statistics
object referencing the column that prevents us from changing it from an
int to an nvarchar. However, I have no idea how that got there - my best
guess would be that it has something to do with the auto generate
statistics option being set on the database. However that seems odd to me
because we've done lots and lots of work like this and not encountered the
problem. It also seems like a quick fix would be to perform:

Drop Statistics Badge.encodedid

However I am afraid subsequent statements might fail on this db since I
wasn't really expecting this in the first place. Does anyone have any
insight?

DaveMetal Dave (metal@.spam.spam) writes:
> A script we run against the database as part of the upgrade of our product
> is failing with the following message:
> ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
> accesses this column
> The line that fails is:
> alter table Badge alter column EncodedID nvarchar(16)
> It's clear that there's some kind of automatically generated statistics
> object referencing the column that prevents us from changing it from an
> int to an nvarchar. However, I have no idea how that got there - my best
> guess would be that it has something to do with the auto generate
> statistics option being set on the database. However that seems odd to me
> because we've done lots and lots of work like this and not encountered the
> problem.

I played around a little, and not all table changes caused complaints
about statistics. But changing a column from varchar to nvarchar did.

The statistics in question is not a regular auto-statistics, their
names are different. My guess is that this could be something created
by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

>It also seems like a quick fix would be to perform:
> Drop Statistics Badge.encodedid

Well, "DROP STATISTICS Badge.hind_61_3" would be better.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 20 Dec 2004, Erland Sommarskog wrote:

> The statistics in question is not a regular auto-statistics, their
> names are different. My guess is that this could be something created
> by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

This makes a lot of sense. Thanks for the input. It also explains how this
might have gotten onto a customers database, if their dba started poking
around without us knowing about it.

Do you happen to know the naming scheme of the auto stats?

> >It also seems like a quick fix would be to perform:
> > Drop Statistics Badge.encodedid
> Well, "DROP STATISTICS Badge.hind_61_3" would be better.

Oops, that's what I intended to type. Thanks for the correction. I don't
even think mine would have run.

Dave|||Metal Dave (metal@.spam.spam) writes:
> Do you happen to know the naming scheme of the auto stats?

The ones I have seen are like _WA_Sys_columnname_1AD3FDA4.

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

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

ALTER COLUMN failing

ALTER TABLE Gen_Demo ADD email4 VARCHAR(50)
go
ALTER TABLE Gen_Demo ALTER COLUMN email4 VARCHAR(101)
go
when executed in Query Analyzer -
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'COLUMN'.
however this same code works on a friends SQL Server
Any ideas ?
Are you using SQL Server 7.0, or 2000 in compatibility = 70?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"glose" <anonymous@.discussions.microsoft.com> wrote in message
news:79729B16-964E-4EB3-AE62-B6BD6699B87F@.microsoft.com...
> ALTER TABLE Gen_Demo ADD email4 VARCHAR(50)
> go
> ALTER TABLE Gen_Demo ALTER COLUMN email4 VARCHAR(101)
> go
> when executed in Query Analyzer -
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'COLUMN'.
> however this same code works on a friends SQL Server
> Any ideas ?
|||oops - thanks -that was it the compatability was set to 65 for some reason.

ALTER COLUMN failing

ALTER TABLE Gen_Demo ADD email4 VARCHAR(50
g
ALTER TABLE Gen_Demo ALTER COLUMN email4 VARCHAR(101
g
when executed in Query Analyzer
Server: Msg 170, Level 15, State 1, Line
Line 1: Incorrect syntax near 'COLUMN'
however this same code works on a friends SQL Serve
Any ideas ?Are you using SQL Server 7.0, or 2000 in compatibility = 70?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"glose" <anonymous@.discussions.microsoft.com> wrote in message
news:79729B16-964E-4EB3-AE62-B6BD6699B87F@.microsoft.com...
> ALTER TABLE Gen_Demo ADD email4 VARCHAR(50)
> go
> ALTER TABLE Gen_Demo ALTER COLUMN email4 VARCHAR(101)
> go
> when executed in Query Analyzer -
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'COLUMN'.
> however this same code works on a friends SQL Server
> Any ideas ?|||oops - thanks -that was it the compatability was set to 65 for some reason.

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = newServer(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = new Server(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter column datatype and default value in a table

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance

Quote:

Originally Posted by vijaialphonse

Are you sure your columns meet all the criteria described in the ALTER COLUMN notes at http://msdn2.microsoft.com/en-us/library/ms190273.aspx ?
Also not that if you modify the type and/or constraints of a column the data already in the column must meet the new settings.

Alter column causing log to fill

I'm trying to simply change a column definition from Null to Not Null. It's
a multi million row table. I've already checked to make sure there are no
nulls for any rows and a default has been created for the column. My log is
set to autogrow and as the alter column colname char(6) Not Null runs the
log begins to grow. If I use no check BOL say the optimizer won't consider
the change. How can I change the nullability of a column that currently
contains no nulls without using up extreme amounts of log space?

DannyDanny (istdrs@.flash.net) writes:
> I'm trying to simply change a column definition from Null to Not Null.
> It's a multi million row table. I've already checked to make sure there
> are no nulls for any rows and a default has been created for the column.
> My log is set to autogrow and as the alter column colname char(6) Not
> Null runs the log begins to grow. If I use no check BOL say the
> optimizer won't consider the change. How can I change the nullability
> of a column that currently contains no nulls without using up extreme
> amounts of log space?

I guess the reason that the log grows, is that SQL Server needs to update
internal data structures in each page on the table. For each row there
is a bitmap that specifies which columns in the row that have the NULL
value. If you take make one column NOT NULL, then the bitmap is affected,
at least if it is not the last column in the map.

CHECK/NOCHECK has nothing to do with it, verifying the constraint does not
take any log space. (And SQL Server won't let you to say that a column is
NOT NULL without checking it, to save its own sanity.)

So the only other option to ALTER TABLE, is to take the long way: Rename
the table, create a new table, insert over, restore indexes, triggers and
constraints, move referencing foreign keys and drop the old table. When
you insert data over, you can do it in batches, and with the database
in simple recovery, the log growth will not be equally excessive. A
variation of this with even less log usage may be to bulk out the data,
and load the new table with BCP.

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

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

alter column b/w desired column

if i have structure of table like a,b,c,d,e column then

i want to alter one more column x b/w b and c column, so that

a,b,x,c,d,e

Do you need to add the column to the table or do you want to do something with the records?

ALTER TABLE testTabelle ADD x NVARCHAR(20)

I don't know if the position of the column could be defined but is it really needed?
You can always define the position on quering:

SELECT a, b, x, c, d, e
FROM testTabelle|||Hi,

if you want to put a column in between, you can`t do it without recreating the whole table. Ordinal position are not relevant in the database, though they can be retrieved in a custom order by defining a view for getting the "ordered" columns.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Alter Column as Identity

Hi All,
Is there a way to Alter an existing column and set it as an identity where t
he table contains data? TIA
A small sample code would be nice.No, you cannot alter an existing column to have the identity property. Your
options are limited to recreating the table or create another column as an
identity column.
Anith|||It's funny that you can manually set the column as an identity, but you can'
t programmatically change it.|||Actually, when you do it using EM (manually), a series of steps happen
behind the scenes : a new table is created with the identity column, the
data is copied, old one is dropped & the new table is renamed. You can see
the series of operations, by clicking on the save change script button on
the design table interface.
Anith

Saturday, February 25, 2012

Alter Column Question

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts

ALTER COLUMN on a text or ntext field

ALTER COLUMN on a text or ntext field

alter column of a replicated table?

ALTER COLUMN NAME?

ALTER COLUMN NAME?

Alter column name of table?

Alter column name from SQL?

Alter column name

Alter column in a table that is being published for replication.

Alter column in a database that is a transactional replication publisher

alter column for every tables

alter column fails due to statistics

ALTER COLUMN failing

ALTER COLUMN failing

Alter Column datatype with Default constraint

Alter Column datatype in SMO

Alter Column datatype in SMO

Alter column datatype and default value in a table

Alter column causing log to fill

alter column b/w desired column

Alter Column as Identity

Sql Server Alignment problem

Blog Archive

About Me