Changing the size of a Varchar Field

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?

Thanks ahead of time
DMW

Edit:
We are running SQL Server 2000 and the db at the time was running in simple moodBulk copy it out, redefine the table without indexes (except clustered or primary key) or triggers, bulk copy back in with a batchsize set to keep the log from growing too large, reapply the indexes and repost the triggers.sql

changing fieldsize in a replicated database

I am looking for a way to increase the size of a varchar field in a
replicated database, without disturbing the data of course. I do not
want to remove the replication just for this one issue. What would the
process be in writing a SQL query to accomplish this? The table name is
tblBid, and the field is bidNotes. It is a varchar(500), and I would
like it to be 900. Is there a size limitation on varchars?
Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Daniel,
Directly this can't be done. Indirectly it can, but not nicely! You could
add a new column with the new datatype (sp_repladdcolumn), do an update on
the table to populate the column, then drop the column (sp_repldropcolumn).
Do this again to create the column having the same original name.
BTW this is available directly using Alter Table in SQL 2005.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Changing Datatype length

Hi all,
I need to change a varchar from 35 to 50. In the SQL Server books on
line it says that SQL Server actually creates a new table when you
change the length. I ran a test in a test database and it appears the
only thing that changes is the length. All the data remains in tact.

The table with the column I want to modify is very critical. Is there
any chance I would loose data if I change the length to a larger size? I
am making a back up of the table just in case. Thanks,
KellyKelly Prendergast (kelly.prendergast@.noaa.gov) writes:
> I need to change a varchar from 35 to 50. In the SQL Server books on
> line it says that SQL Server actually creates a new table when you
> change the length. I ran a test in a test database and it appears the
> only thing that changes is the length. All the data remains in tact.
> The table with the column I want to modify is very critical. Is there
> any chance I would loose data if I change the length to a larger size? I
> am making a back up of the table just in case. Thanks,

If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
all that will happens is that metadata will be updated, which will occur
in a snap. If you were to change a char(35) column to char(50), I
would expect it to be different, because in this case SQL Server would
move around data to leave room for the value.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9442489D4AB0Yazorman@.127.0.0.1...
> Kelly Prendergast (kelly.prendergast@.noaa.gov) writes:
> > I need to change a varchar from 35 to 50. In the SQL Server books on
> > line it says that SQL Server actually creates a new table when you
> > change the length. I ran a test in a test database and it appears the
> > only thing that changes is the length. All the data remains in tact.
> > The table with the column I want to modify is very critical. Is there
> > any chance I would loose data if I change the length to a larger size? I
> > am making a back up of the table just in case. Thanks,
> If you use "ALTER TABLE tbl ALTER COLUMN col varchar(50)"
> all that will happens is that metadata will be updated, which will occur
> in a snap. If you were to change a char(35) column to char(50), I
> would expect it to be different, because in this case SQL Server would
> move around data to leave room for the value.

I want to add to Erland's answer to address the final question. You will
NOT lose data.

SQL Server treats this as a transactional change so either the change will
complete in full, or nothing will change.

If it DOES create a new table the pseudo-SQL is:

Begin Tran
select into TEMP from FOO
drop table FOO
sp_renameobject TEMP to FOO
if error ROLLBACK Tran
else End tran

So the change is completely atomic. Nothing to worry about.

>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Changing Database Context in T-SQL

How can one change the database context within a stored procedure? In other
words, something like this ...
Declare @.DB varchar(20)
set @.DB = (Select databasename from databases where activedatabase = 1)
Use @.DB
Select * from Table1
This code doesn't work, but I'm sure there's a way to do this.
Thanks for the help.
Ross> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
Sure, you can use dynamic SQL, blecch.
EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
Don't use SELECT * in production code.|||Ok, is there another option?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sure, you can use dynamic SQL, blecch.
> EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
> Don't use SELECT * in production code.
>|||Why?
Can't you use four-part names for objects?
ML|||USE takes a literal string for the database name. In your case, you would
have to use dynamic query to for further processing.
So, the answer is 'no'.
-oj
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:Ok23XBQnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Ok, is there another option?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
>|||What are you trying to accomplish? Most likely, you need to do something
like:
databasename.owner.tablename
SQL code stored in SQL Server is not really made to do dynamic stuff like
this. T-SQL is a super duper language for querying the database, but lousy
for doing much string manipulation. If you want to do stuff like this, use
your middle tier objects where it is far easier to build robust string
building facilities.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:O$TAu4PnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> How can one change the database context within a stored procedure? In
> other words, something like this ...
> Declare @.DB varchar(20)
> set @.DB = (Select databasename from databases where activedatabase = 1)
> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
> Thanks for the help.
> Ross
>

changing data types

hi!
I'd like to change the datatype of one tablefield from varchar to text.
Is this possible when the table already is populated, or will there be trouble?
obscurrDepending the version you're using.

In any way

exec sp_rename MyTable, MyOldTable
select Col1, Col2, convert(TEXT, ColVarchar) ColVarchar
into MyTable
from MyOldTable
drop table MyOldTable|||I'm using SQL Server version 7.0