changing column type

Hi I have transactional replication and I need a tables column data type
changed from char(30) to varchar(40). What would be the best way and least
dangerous to edit this column and get it to replicate the changes succesfully.
thanks for any advice
Sammy
Sammy,
please check out these articles:
http://www.replicationanswers.com/AddColumn.asp
and
http://www.replicationanswers.com/AlterSchema2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

changing column type

hi,
i just want the change the type of a column in a table within sql server
2000 enterprise manager. however it warns that,
'MyTable' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'.
i don't want to get those options changed when i change the type of the
column and this is not the only database on the server that we cannot stop
the server to change the configuration options(ANSI_PADDING, ANSI_NULLS) of
the sql server. it seems that we have to use sp_dboption stored procedure to
change those options for the current database. my main intention is not to
deal with query analyzer to execute sql statements, only get the job done
through enterprise manager. isn't it possible through enterprise manager to
change the database options only for the current database? i looked through
the menus may be i missed.
thanksEM goes through a much more complex CREATE/INSERT/DROP table routine =when changing data types. Have you tried changing the column via Query =Analyzer?
I have not tried it...but I am guessing that it might work better via =Query Analyzer
ALTER TABLE x ALTER COLUMN y DesiredDataTypeGoesHere
You can always try to make the change via Enterprise Manager, but make =sure that you script out the changes instead of having it apply them for =you. You should then be able to modify the code as you wish.
-- Keith
"Richard" <rich@.thetop.com> wrote in message =news:eBhBgH4nDHA.2304@.TK2MSFTNGP11.phx.gbl...
> hi,
> i just want the change the type of a column in a table within sql =server
> 2000 enterprise manager. however it warns that,
> > 'MyTable' table
> - Warning: One or more existing columns have ANSI_PADDING 'off' and =will be
> re-created with ANSI_PADDING 'on'.
> - Warning: The table was created with ANSI_NULLS 'off' and will be
> re-created with ANSI_NULLS 'on'.
> > i don't want to get those options changed when i change the type of =the
> column and this is not the only database on the server that we cannot =stop
> the server to change the configuration options(ANSI_PADDING, =ANSI_NULLS) of
> the sql server. it seems that we have to use sp_dboption stored =procedure to
> change those options for the current database. my main intention is =not to
> deal with query analyzer to execute sql statements, only get the job =done
> through enterprise manager. isn't it possible through enterprise =manager to
> change the database options only for the current database? i looked =through
> the menus may be i missed.
> thanks
> >

Changing Column size/type with Derived Column

I have a number of date columns that are parsed as DT_WSTR (6) and I have written a Derived Column converting them into DT_DATE via this (found on the forums) type expression:
(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))
But I really want to replace the current column, not create a new one. If I use "replace" the data is forced to be a DT_WSTR (6), and I get a truncation error at run-time.
Simeon
Simeon,
You're stuck with it I'm afraid. You can't change the type of a column in the derived column component. Its not a ahrdship to add it as a new column though, jsut don't use the existing one that's all!

-Jamie|||Could you change the source to return a larger column. This would solve your problem.|||Cheers Jamie,
That's what I thought, I was just hoping there was a way to keep it "clean".
On a related thought, I am finding that each time I alter any component near the top of a data flow, I end up needing to delete and re-add most the down stream components due to fields mismatching. Is this why people appear to be building there packages via code?
Simeon.
|||This does depend on the component, some just need double clicking on and the meta data should correct it self, others require you to select the mapped columns. The latter is generally when you change the names of components and inputs.

You shouldn't have to delete components though, I find that surprising.|||

SimonSa wrote:

Could you change the source to return a larger column. This would solve your problem.

That might work, but the derived column sets the type to DT_WSTR, so I'm not sure that putting a entry that is cast to DT_DATE would not upset it also.
|||

I was finding this while I was developing my source component. I had it wired to Raw Files (then later Trash Destinations) with Data Viewers to inspect the data. Running the package (after reloading BI) would give errors, so I found it easier to delete the source and it four outputs, and re-wire.
But going forward I'll try double clicking, and checking the mappings.

Simeon
|||Do you need to cast it to a date? If you do then you will have to have a new column, and the derived column is the best solution|||I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.|||

SimonSa wrote:

I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.

It was. I was slowly adding support for different data types, then adding support for foreign keys. The source component is like a flat file parser, but it handles files that have different rows (with different columns) that have relationships based on order. So really n tables with the foreign keys implied by what a row follows.
I was (for simplicity) developing support incrementally, with the relations setup by a function. The next step is to put that information into a configuration file.
|||A column is identified by it's lineage Id. Deleteing a column and adding it back, even with the same name and same data type properties will cause it to change lineage Id. This means downstream components that have referenced that column (by lineage Id) are now invalid. Opening the UI should bring up the mapping dialog, and one of the options is Map by Name. This normally solves most issues.

A well behaved component will not recreate the output buffer columns each time, but rather detect invalid columns, and remove, add new columns if required, and fix any columns in can detect on both sides, or leave alone matching columns. This can be a pain, as it is lots more code, but try the samples such as the ADO Source for some good template code.

Changing column data type to Unicode data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie

sql

Changing column data type increase transaction log

I am using SQL Server 2000. My database is about 8.5 gig in size.
I have a table with a column with datatype smallint, and I would like to
change it to datatype int.
When I did that, it is taking forever and the transaction log just keep
growing and growing until I almost ran out of disk space before I killed
Enterprise Manager.
How can I change a column datatype without making the transaction log keep
growing and growing ?
Thank you.On Mar 20, 12:56=A0pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep=
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||Thank you.
My database model is already simple. If I do the ALTER TABLE will the
transaction log keep growing and growing ?
I also posted another question on this forum regarding moving data from 1
column to another. I am thinking to do it that way.
Here is the real table:
CREATE TABLE [dbo].[Packet] (
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PACKET_DATA] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKET_VOL] [smallint] NULL ,
[PACKET_VOL2] [int] NULL ,
[PACKET_TRADE] [float] NULL
) ON [PRIMARY]
GO
I create PACKET_VOL2 as datatype INT, and I will move data from PACKET_VOL
to PACKET_VOL2 with the following query:
set ROWCOUNT 50000
declare @.LastCount smallint
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
UPDATE PACKET SET PACKET_VOL2 = PACKET_VOL WHERE PACKET_VOL2 is null
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
Then I will rename PACKET_VOL to PACKET_VOLX and PACKET_VOL2 to PACKET_VOL.
What do you think ?
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||If after renaming PACKET_VOL2 to PACKET_VOL, if I want to delete column
PACKET_VOL2 by doing the following:
ALTER TABLE packet DROP COLUMN PACKET_VOLX
Will it eat up the transaction log (will the log keep growing and growing
while I do the ALTER TABLE above) ?
Thank you
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||When I do
ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
it executes very fast, and the transaction log did not grow.
But when I do
ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
the transaction log keeps growing and growing, so I stopped it.
Why when converting to smallint it went fast and the log did not grow but
when converting to INT the log keep growing?
Thanks.
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.|||> Why when converting to smallint it went fast and the log did not grow but when converting to INT
> the log keep growing?
Some operations are meta-data only operations where for other operations SQL Server need to actually
modify each row. I would guess that there is some information in Books Online about this, but they
can't document every possible change (from type - to type) and whether such change is meta-data only
or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"fniles" <fniles@.pfmail.com> wrote in message news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but when converting to INT
> the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log keep
>> growing and growing ?
>> Thank you.
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>|||In addition to Tibor's reply, there is also a third type of change. For
some datatype changes SQL Server has to inspect every row to see if the
existing values 'fit' into the new datatype, and gives you an error if even
one row won't be convertible. But it doesn't actually make any changes. So
there can be lots of reads with no writes.
So ALTER TABLE operations can be one of the following:
1. Metadata only
2. Read the whole table
3. Read and WRITE the whole table
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>
> Some operations are meta-data only operations where for other operations
> SQL Server need to actually modify each row. I would guess that there is
> some information in Books Online about this, but they can't document every
> possible change (from type - to type) and whether such change is meta-data
> only or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>|||If your column is a small int already, and you issue a command to change it
to a small int, since nothing actually needs to change I would expect this
to be a very fast operation.
However, if the data type actually changes, I would expect it to take some
time, depending on the size of the table. I'm not sure how much work SQL
Server needs to do to change the smallint to an int, maybe it needs to
allocate more space in every row?
"fniles" <fniles@.pfmail.com> wrote in message
news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but
> when converting to INT the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>|||> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
Page density will also likely come into play... if the pages are full then,
even though it's only a 2 byte change, there may need to be a large
re-allocation of data...|||Thank you, all.
1. Metadata only -> is this the fast one ?
2. Read the whole table --> is this slow ?
3. Read and WRITE the whole table --> is this the slowest one ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23RhZg80iIHA.5088@.TK2MSFTNGP02.phx.gbl...
> In addition to Tibor's reply, there is also a third type of change. For
> some datatype changes SQL Server has to inspect every row to see if the
> existing values 'fit' into the new datatype, and gives you an error if
> even one row won't be convertible. But it doesn't actually make any
> changes. So there can be lots of reads with no writes.
> So ALTER TABLE operations can be one of the following:
> 1. Metadata only
> 2. Read the whole table
> 3. Read and WRITE the whole table
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>> Why when converting to smallint it went fast and the log did not grow
>> but when converting to INT the log keep growing?
>>
>> Some operations are meta-data only operations where for other operations
>> SQL Server need to actually modify each row. I would guess that there is
>> some information in Books Online about this, but they can't document
>> every possible change (from type - to type) and whether such change is
>> meta-data only or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow
>> but when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like
>> to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I
>> killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>>
>|||Thank you, all.
I was doing some testing.
When I change the column to smallint, it was of type int originally. This
went fast.
Then I changed back from smallint to int, that's when it took a long time.
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:%235NGxW1iIHA.5504@.TK2MSFTNGP05.phx.gbl...
> If your column is a small int already, and you issue a command to change
> it to a small int, since nothing actually needs to change I would expect
> this to be a very fast operation.
> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>> When I do
>> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
>> it executes very fast, and the transaction log did not grow.
>> But when I do
>> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
>> the transaction log keeps growing and growing, so I stopped it.
>> Why when converting to smallint it went fast and the log did not grow but
>> when converting to INT the log keep growing?
>> Thanks.
>>
>> "rpresser" <rpresser@.gmail.com> wrote in message
>> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
>> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
>> I am using SQL Server 2000. My database is about 8.5 gig in size.
>> I have a table with a column with datatype smallint, and I would like to
>> change it to datatype int.
>> When I did that, it is taking forever and the transaction log just keep
>> growing and growing until I almost ran out of disk space before I killed
>> Enterprise Manager.
>> How can I change a column datatype without making the transaction log
>> keep
>> growing and growing ?
>> Thank you.
>> Don't use enterprise manager. EMGR's approach is to duplicate the
>> entire table with the new schema and then rename it back.
>> Use an ALTER statement:
>> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
>> You will have to drop any indexes, references or constraints that use
>> the column, and recreate them afterwards.
>> It is still going to eat a lot of trans log space. You might need to
>> change the model from full to simple first.
>|||fniles (fniles@.pfmail.com) writes:
> 1. Metadata only -> is this the fast one ?
Yes, this is instant.
> 2. Read the whole table --> is this slow ?
Takes some more time yes. But your log will not be affected.
> 3. Read and WRITE the whole table --> is this the slowest one ?
Yes, and your transaction log takes a toll if the table is big.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Changing column data type increase transaction log

I am using SQL Server 2000. My database is about 8.5 gig in size.
I have a table with a column with datatype smallint, and I would like to
change it to datatype int.
When I did that, it is taking forever and the transaction log just keep
growing and growing until I almost ran out of disk space before I killed
Enterprise Manager.
How can I change a column datatype without making the transaction log keep
growing and growing ?
Thank you.
On Mar 20, 12:56Xpm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||Thank you.
My database model is already simple. If I do the ALTER TABLE will the
transaction log keep growing and growing ?
I also posted another question on this forum regarding moving data from 1
column to another. I am thinking to do it that way.
Here is the real table:
CREATE TABLE [dbo].[Packet] (
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PACKET_DATA] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKET_VOL] [smallint] NULL ,
[PACKET_VOL2] [int] NULL ,
[PACKET_TRADE] [float] NULL
) ON [PRIMARY]
GO
I create PACKET_VOL2 as datatype INT, and I will move data from PACKET_VOL
to PACKET_VOL2 with the following query:
set ROWCOUNT 50000
declare @.LastCount smallint
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
UPDATE PACKET SET PACKET_VOL2 = PACKET_VOL WHERE PACKET_VOL2 is null
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
Then I will rename PACKET_VOL to PACKET_VOLX and PACKET_VOL2 to PACKET_VOL.
What do you think ?
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||If after renaming PACKET_VOL2 to PACKET_VOL, if I want to delete column
PACKET_VOL2 by doing the following:
ALTER TABLE packet DROP COLUMN PACKET_VOLX
Will it eat up the transaction log (will the log keep growing and growing
while I do the ALTER TABLE above) ?
Thank you
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||When I do
ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
it executes very fast, and the transaction log did not grow.
But when I do
ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
the transaction log keeps growing and growing, so I stopped it.
Why when converting to smallint it went fast and the log did not grow but
when converting to INT the log keep growing?
Thanks.
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||In addition to Tibor's reply, there is also a third type of change. For
some datatype changes SQL Server has to inspect every row to see if the
existing values 'fit' into the new datatype, and gives you an error if even
one row won't be convertible. But it doesn't actually make any changes. So
there can be lots of reads with no writes.
So ALTER TABLE operations can be one of the following:
1. Metadata only
2. Read the whole table
3. Read and WRITE the whole table
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>
> Some operations are meta-data only operations where for other operations
> SQL Server need to actually modify each row. I would guess that there is
> some information in Books Online about this, but they can't document every
> possible change (from type - to type) and whether such change is meta-data
> only or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>
|||If your column is a small int already, and you issue a command to change it
to a small int, since nothing actually needs to change I would expect this
to be a very fast operation.
However, if the data type actually changes, I would expect it to take some
time, depending on the size of the table. I'm not sure how much work SQL
Server needs to do to change the smallint to an int, maybe it needs to
allocate more space in every row?
"fniles" <fniles@.pfmail.com> wrote in message
news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but
> when converting to INT the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>
|||> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
Page density will also likely come into play... if the pages are full then,
even though it's only a 2 byte change, there may need to be a large
re-allocation of data...
|||Thank you, all.
1. Metadata only -> is this the fast one ?
2. Read the whole table --> is this slow ?
3. Read and WRITE the whole table --> is this the slowest one ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23RhZg80iIHA.5088@.TK2MSFTNGP02.phx.gbl...
> In addition to Tibor's reply, there is also a third type of change. For
> some datatype changes SQL Server has to inspect every row to see if the
> existing values 'fit' into the new datatype, and gives you an error if
> even one row won't be convertible. But it doesn't actually make any
> changes. So there can be lots of reads with no writes.
> So ALTER TABLE operations can be one of the following:
> 1. Metadata only
> 2. Read the whole table
> 3. Read and WRITE the whole table
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>
|||Thank you, all.
I was doing some testing.
When I change the column to smallint, it was of type int originally. This
went fast.
Then I changed back from smallint to int, that's when it took a long time.
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:%235NGxW1iIHA.5504@.TK2MSFTNGP05.phx.gbl...
> If your column is a small int already, and you issue a command to change
> it to a small int, since nothing actually needs to change I would expect
> this to be a very fast operation.
> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>

Changing column data type constraint

I am trying to change the data type of two columns in a SQL database.The columns were created using the“smallint” data type.Since they are used to identify document and document sections rather than mathematical functions, I think they should have been constrained to a text data type such as nvarchar.When I try to concatenate with a query the result is a mathematical addition of the numbers, butI am trying to combine the two numbers as a string with a "-" between them.

I have not had any success in changing the data type of the two columns.Apparently, the original database was set up for full text search and won’t let me change the column data type.I keep getting this error message:

'Full Documents' table

- Unable to modify table.

Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.

The statement has been terminated.

I ran a query to increase the timeout period (which succeeded in increasing the timeout but still got the same error message when trying to change the column data type). My research suggests that this is really a matter of the column data constraints related to the full text search issue.

Any suggestions on how to change the data type of these columns?

Since this is a general SQL question, I'm moving it to a more general forum where you'll get a better answer.

Mike

Changing column data type

I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.

ALTER TABLE database_table
ALTER COLUMN my_bit_columnINT;

Thanks.

It should work. The data will change to 0 (if it was False) or 1 (if it was True) after you change your column data type.

Changing chart type at run-time

Hello,

I'm coding a web application using ASP.NET 2.0 and I'd like to show users some charts allowing them to chose the chart type at run-time.
I'm building a rdlc (local) report source at run-time starting from a template and working in memory.
Users can select wich rows and columns from a table to display in the chart. They can also chose che chart type (Bar, line, pie etc.)

Now the code works fine the first time it's run. Then the report viewer keeps showing the chart with the same chart type and columns data. Only rows change accordingly to the user selection.

I'm using the following code to initialize the reportviewer:

Dim str As System.IO.MemoryStream = New MemoryStream
rptDoc.Save(str) //->that's the xml doc being saved to a memory stream
str.Position = 0
repView.LocalReport.ReportPath = String.Empty 'Just in case...
repView.LocalReport.LoadReportDefinition(str)
repView.LocalReport.DataSources.Clear() 'Just in case again...
repView.LocalReport.DataSources.Add(New ReportDataSource("DataSet1_01E01000", dt2))
repView.LocalReport.Refresh()

Any suggestion?

Thank you all.Can you share with me how you change the Chart Type at Runtime? Thanks!|||

Sorry for the late reply, I haven't checked forums lately.

As I said I'm building the rdlc (local) report source file at run-time starting from some templates.

The rdlc files are nothing more that XML files and the chart type is defined inside a node. All I do is put the desired type in the right node.

You can also build a rdlc right from scratch and pass it to the ReportViewer component via its LoadReportDefinition method using the overloaded version that gets a IO.Stream as source.

Ask me if you need a sample.

Bye.

|||

You need to reset the report by doing a

ReportViewer1.Reset();//Then setting the datasource and all the properties.

|||

Hello Stojilcoviz,

Can you share with me the sample of your code which changes the chart types at run-time in the RDLC? Thanks.

changing Chart Type

Hi
Do any one have idea on how to change the chart type in Reporting
Services. I'm new to SQL server RS. So if you have any code snippet,
please send. It will very help full for me.
Thanks and regards
SanthamurthyHi,
Are you asking dynamic chart type changing?
I dont think you are asking from layout (while designing) "chart type" since
it is very simple.
Regards
Amarnath
"Santhamurthy" wrote:
> Hi
> Do any one have idea on how to change the chart type in Reporting
> Services. I'm new to SQL server RS. So if you have any code snippet,
> please send. It will very help full for me.
> Thanks and regards
> Santhamurthy
>

Changing char length in stored procedures

Hello all, I'm using SQL Server 2000 and have about 250 stored procedures that use an EMPLID parameter or variable of type varchar with a length of 4. I need to change the length to 10 instead and would like to do so without having to open every sp for editing. Is there a way to do this through SQL Server 2000? Does anyone have a script to do this? Any help would be appreciated.I'd use SQL Enterprise Mangler to script all of the stored procedures, then a text editor to make the changes. It should be a simple replace operation, possibly using a regexp if you aren't careful about how you format your declarations.

-PatP

Changing between Identity type and Int type

What is the TSQL command to chanage a column of int type to identity type
and vice versa?
Thanks in advance!
KMYou can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
> What is the TSQL command to chanage a column of int type to identity type
> and vice versa?
> Thanks in advance!
> KM
>|||To add on, if your table is having millions of data do go for Enterprise
Manager, you can mess up your life doing so. It will remain in a hanged
situation. So better do it manual ways like creating a new table and renamin
g
droping the original one.
Thanks,
Sree
"Ryan" wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>
>|||But when I insert the existing data into the new table with the identity
column, how can I make sure that the keys are not changed?
(I am thinking of existing data having integer keys and a lot of unused
numbers due to deletions.)
Ryan wrote:

> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data, yo
u
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
>|||> You can't add the IDENTITY property to an existing table -
No ,you CAN
create table #t
(
col1 char(1)
)
insert into #t values ('a')
insert into #t values ('b')
insert into #t values ('c')
alter table #t add col2 int identity(1,1)
select * from #t
Actually you cannot EDIT/ALTER an IDENTITY property
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
> you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||My understanding of the question was to add the identity property to an
existing INT column, admitidly my opening sentance should have read :-
You can't add the IDENTITY property to an existing column
rather than
You can't add the IDENTITY property to an existing table -
Apologies for any mis-understanding. Either way KM now has an answer to his
question.
HTH. Ryan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eiljmwAIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> No ,you CAN
> create table #t
> (
> col1 char(1)
> )
> insert into #t values ('a')
> insert into #t values ('b')
> insert into #t values ('c')
> alter table #t add col2 int identity(1,1)
> select * from #t
>
> Actually you cannot EDIT/ALTER an IDENTITY property
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>|||Ryan,
In the Enterprise Manager, I can create an int column with non-contiguous
(and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
records. (Please note that the values are not in ascending order and two of
them are duplicated.) Then later, I can change the column to an identity
column without any value change. Thus I am sure that rather than dropping
and recreating the column, the Enterprise Manager is doing it in another
way. Please correct me know if I am wrong.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You can't add the IDENTITY property to an existing table - you need to
> create a new table with the IDENTITY column. If you have existing data,
you
> can create it with a different name, INSERT the existing data, drop the
> existing table, then rename the new table. Enterprise Manager will do this
> for you if you add the property in the table designer.
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OccfMRAIGHA.2012@.TK2MSFTNGP14.phx.gbl...
type
>|||Hi Ferdinand,
To your question:
You should Set identity_insert on on the new table with the identity
before you insert the data.
The Set will last for the session or until you set identity_insert off.
Example:
Create table #source (cola int, colb int)
insert into #source values (1,2)
insert into #source values (3,4)
Create table #dest (cola int identity, colb int)
set identity_insert #dest on
Insert into #dest (cola,colb)
Select cola, colb
From #source
Select *
from #dest
-- drop table #dest
-- drop table #source
"Ferdinand Zaubzer" wrote:

> But when I insert the existing data into the new table with the identity
> column, how can I make sure that the keys are not changed?
> (I am thinking of existing data having integer keys and a lot of unused
> numbers due to deletions.)
>
> Ryan wrote:
>
>|||Run the SQL Profiler as the EM makes the change. You'll be amazed.
ML
http://milambda.blogspot.com/|||> Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
>
Enterprise Manager create a new table, loads data from the old table and
them drops the old table to implement this change. You can verify this with
a Profiler trace or by clicking the 'save change script' button. The end
result is as if the identity property was simply added to the column but the
actual technique used is more involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Krygim" <krygim@.hotmail.com> wrote in message
news:OrPh5wBIGHA.2912@.tk2msftngp13.phx.gbl...
> Ryan,
> In the Enterprise Manager, I can create an int column with non-contiguous
> (and even duplicated) values in different records, e.g., 1, 31, 1, 7 in 4
> records. (Please note that the values are not in ascending order and two
> of
> them are duplicated.) Then later, I can change the column to an identity
> column without any value change. Thus I am sure that rather than dropping
> and recreating the column, the Enterprise Manager is doing it in another
> way. Please correct me know if I am wrong.
> KM
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:es%23Y6YAIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> you
> type
>

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.
You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a column type with replication

I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.

Changing a Column datatype

Hello I am having a table
table1
col1 (bit)
and i want to changethe col1 type for smallint
col1 (smallint)
true will be = 1
and false = 0
how can i do it ??
thank youJust change it using Enterprise Manager. The existing values will be implicitly converted.|||i must change it from a script
but i found it

thank you|||Change it in Enterprise Manager, and then click the icon that scripts your change.

Changind a Primary Key value

Hi,
I have a table with a Primary Key field that is an integer (int) data type
with auto increment.
Is there a way to change the value of the ID field (e.g. currently it is 100
and I want to change it to 20, is it possible?)
Thanks,Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>|||On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@.shamir.co.il> wrote:
>Hi,
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>Thanks,
>
Hi Shai,
Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?
Unfortunately, that is not possible.
From Books Online:
Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.
Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.
Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:
To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.
To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table with a Primary Key field that is an integer (int) data type
> with auto increment.
> Is there a way to change the value of the ID field (e.g. currently it is
100
> and I want to change it to 20, is it possible?)
> Thanks,
>

Changed a parameter to multi-value and now it doesn't work

I have a report with multiple parameters that the user can specifiy before
running the report. One of them is a field that the user can type in a value
for. It's defined as a string and allows a blank value in the event the user
does not want to enter anything. It works fine when not multi-value. But
when I change it to multi-value, the report brings back nothing when the user
does not enter any values.
The related dataset code for this field (tracking_number) is
...
WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
...
Any help would be appreciated.
StephanieRemove the multi-value property. that is not really meant for a text field,
only for instances where one might choose from multiple values of a drop down
list.
"Stephanie" wrote:
> I have a report with multiple parameters that the user can specifiy before
> running the report. One of them is a field that the user can type in a value
> for. It's defined as a string and allows a blank value in the event the user
> does not want to enter anything. It works fine when not multi-value. But
> when I change it to multi-value, the report brings back nothing when the user
> does not enter any values.
> The related dataset code for this field (tracking_number) is
> ...
> WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> ...
> Any help would be appreciated.
> Stephanie|||When I do that, I cannot add multple values for the field.
I've tried:
x,y
x y
'x','y'
Suggestions?
"Carl Henthorn" wrote:
> Remove the multi-value property. that is not really meant for a text field,
> only for instances where one might choose from multiple values of a drop down
> list.
> "Stephanie" wrote:
> > I have a report with multiple parameters that the user can specifiy before
> > running the report. One of them is a field that the user can type in a value
> > for. It's defined as a string and allows a blank value in the event the user
> > does not want to enter anything. It works fine when not multi-value. But
> > when I change it to multi-value, the report brings back nothing when the user
> > does not enter any values.
> >
> > The related dataset code for this field (tracking_number) is
> > ...
> > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > ...
> >
> > Any help would be appreciated.
> >
> > Stephanie|||Everything that a person enters into that field will be considered one
string. you have to parse out the string in the sproc to break it up into its
component pieces.
Without seeing what you are doing, I dont understand why you "cant add
multiple values for the field." tha field just wraps a single quote around
whatever is there and passes it in as the parameter.
in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
not happening for you, you may want to make things easier on you and just
have a multi-valued dropdown list. At least that way you are not hostage to
your users spelling ability.
"Stephanie" wrote:
> When I do that, I cannot add multple values for the field.
> I've tried:
> x,y
> x y
> 'x','y'
> Suggestions?
> "Carl Henthorn" wrote:
> > Remove the multi-value property. that is not really meant for a text field,
> > only for instances where one might choose from multiple values of a drop down
> > list.
> >
> > "Stephanie" wrote:
> >
> > > I have a report with multiple parameters that the user can specifiy before
> > > running the report. One of them is a field that the user can type in a value
> > > for. It's defined as a string and allows a blank value in the event the user
> > > does not want to enter anything. It works fine when not multi-value. But
> > > when I change it to multi-value, the report brings back nothing when the user
> > > does not enter any values.
> > >
> > > The related dataset code for this field (tracking_number) is
> > > ...
> > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > ...
> > >
> > > Any help would be appreciated.
> > >
> > > Stephanie|||The problem is that this is a string, not an integer. So a single quote at
the beginning and the end is not helpful. The user does not what a drop-down
list because the number of values that would be there would be very large.
They want to type in something like: CIM070524001,CIM070522002. They want to
be able to type in one or multiple values.
Can you test with a string and let me know how that goes? I just can't get
it to work.
"Carl Henthorn" wrote:
> Everything that a person enters into that field will be considered one
> string. you have to parse out the string in the sproc to break it up into its
> component pieces.
> Without seeing what you are doing, I dont understand why you "cant add
> multiple values for the field." tha field just wraps a single quote around
> whatever is there and passes it in as the parameter.
> in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
> sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
> not happening for you, you may want to make things easier on you and just
> have a multi-valued dropdown list. At least that way you are not hostage to
> your users spelling ability.
> "Stephanie" wrote:
> > When I do that, I cannot add multple values for the field.
> >
> > I've tried:
> >
> > x,y
> > x y
> > 'x','y'
> >
> > Suggestions?
> >
> > "Carl Henthorn" wrote:
> >
> > > Remove the multi-value property. that is not really meant for a text field,
> > > only for instances where one might choose from multiple values of a drop down
> > > list.
> > >
> > > "Stephanie" wrote:
> > >
> > > > I have a report with multiple parameters that the user can specifiy before
> > > > running the report. One of them is a field that the user can type in a value
> > > > for. It's defined as a string and allows a blank value in the event the user
> > > > does not want to enter anything. It works fine when not multi-value. But
> > > > when I change it to multi-value, the report brings back nothing when the user
> > > > does not enter any values.
> > > >
> > > > The related dataset code for this field (tracking_number) is
> > > > ...
> > > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > > ...
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Stephanie

change value type from Int64 to Int32 ?

I have a query which calculates a number... but by default the number is represented as a 64 bit integer. I cannot remember the function name but it is only using SQL9.0 built in functions. Is there a way to cast the number?

This is not a trivial issue since I have found an easier way to do this which did not involve the number, although any input would be greatly appreciated.

Thank you :)

I don't quite understand your question. But you can cast a value to a different data type using CAST function. You may or may not get runtime errors depending on the data. See Books Online for more details. So in your query, you can do something like below:

select cast(<expr> as int)

from ...

Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4).
I was wondering whether there's a easier(quickest) method to do this as opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this type.
Thanks,
Manod
ALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas

"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.c om...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod
|||Thanks Anthony...I was hoping that there would be an easier option than going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas
|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas

"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas

Change user type from char to vchar

We have a requirement to change a user defined type from char(4) to vchar(4)
.
I was wondering whether there's a easier(quickest) method to do this as oppo
sed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this ty
pe.
Thanks,
ManodALTER TABLE ...
ALTER COLUMN
The stored procedure, view, and function dependencies should not matter;
however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
and Defaults that may be created on this field.
If you use the database designer tool, EM will usually script out a new
table with all of the dependency drops and recreates, move all of the data,
and drop and rename the tables for you.
This is usually not the best way to do this, especially for very large
tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
good way to learn at least the details if not a poorer solution.
Sincerely,
Anthony Thomas
"sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:69e9c64b.0503090211.3552c797@.posting.google.com...
We have a requirement to change a user defined type from char(4) to
vchar(4).
I was wondering whether there's a easier(quickest) method to do this as
opposed to:
- Drop dependent storedprocs and views
- rename type
- add type with new def
- reattach table columns
- drop old type
- recreate storedprocs,views
Yes, we have quite a few tables, storedproc and views that reference this
type.
Thanks,
Manod|||Thanks Anthony...I was hoping that there would be an easier option than goin
g
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the data
,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas|||Well, there is. As I stated before, if you use the Table Designer through
the EM, it will handle all of the scripting for you. It usually will just
do the creat, copy, drop, replace method, which can work, but it will take a
lot of resources to pull off on larger tables. The same could be done, just
do the same thing except save as script instead save. Keep the drop and
create dependencies, just replace the create, copy, and rename table pieces
with a single ALTER TABLE ... ALTER COLUMN statement. At leas this way, you
would have to parse the dependencies.
There is a caveat to this, however; the EM uses the sysdependencies to
script out all the drops and creates. If you have ever renamed an object
without dropping and recreating the dependencies, and have gotten that
little error message, this means the records in this system table have not
been updated to reflect the name change. So, the little wizard inside the
EM scripter will not catch everything...but your errors will.
Good luck.
Anthony Thomas
"Sandiyan" <sandiyan@.yahoo.co.uk> wrote in message
news:15EEA5C6-8281-41FB-8287-085E1193F84F@.microsoft.com...
Thanks Anthony...I was hoping that there would be an easier option than
going
through and sorting out dependencies and etc...
I hope this will be addressed in sql2005 - my bet is not!
regards,
Sandiyan.
"Anthony Thomas" wrote:

> ALTER TABLE ...
> ALTER COLUMN
> The stored procedure, view, and function dependencies should not matter;
> however, you probably will have to drop and recreate any PKC, UC, FKC, CC,
> and Defaults that may be created on this field.
> If you use the database designer tool, EM will usually script out a new
> table with all of the dependency drops and recreates, move all of the
data,
> and drop and rename the tables for you.
> This is usually not the best way to do this, especially for very large
> tables, but you can "SAVE AS SCRIPT" instead of executing it. This is a
> good way to learn at least the details if not a poorer solution.
> Sincerely,
>
> Anthony Thomas