Changing Column Length

I have a table which gets it feed from external source. I need to chnge the
length of the column on DBs which are in Prod. The table has couple of
indexes and FK relationship (NOT on the column that I need to change).
Number of records is around 36 million. I have never done something like
this before. Will there be any issues that I need to be aware off?If you are increasing the length of a varchar column using ALTER
TABLE...ALTER COLUMN, only meta-data changes are needed so the operation
should be very fast. This acquires a Sch-M lock though, so no other users
can use the table during the ALTER.
Hope this helps.
Dan Guzman
SQL Server MVP
"XXX" <sa@.nomail.com> wrote in message
news:uePmOBn2FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I have a table which gets it feed from external source. I need to chnge the
>length of the column on DBs which are in Prod. The table has couple of
>indexes and FK relationship (NOT on the column that I need to change).
>Number of records is around 36 million. I have never done something like
>this before. Will there be any issues that I need to be aware off?
>

changes across all dbs

I need to find the best way to make changes to all metadata my databases.

1. We can do it manually :(
2. We can use merge replication (needs investigation)
3. We can write our own scripts.
search all instances for a metadata database
if db exists check to see if the change exists (ie. new constraint, new column,cell value)
if it doesn't exist ALTER TABLE
send a mail back to DBA for each change made and where

What do you think is the best way?

-Kevinyou can use 3rd party software from www.red-gate.com

Change the collation on the user DB

Changed the master db collation by using the rebuildm utility from
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. F
or that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things
you need to do (like
dropping indexes), see Books Online. You can search the archives to see if a
nyone has posted a
script to semi-automate this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change th
e
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:

> ALTER DATABASE. But that doesn't change collation for the existing tables.
For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other thing
s you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if
anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the t
ransfer tools (DTS etc),
and depending on which method you chose, you script your objects first and t
hen re-create them
before the import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...[vbcol=seagreen]
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db
,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use th
e
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys
,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:

> There is no such direct feature in SQL Server. But you can use some of the
transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and
then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
>

Change recovery model using TSQL

Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks

Change recovery model using TSQL

Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks
Try;
alter database MyDB
set recovery full
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks

Change recovery model using TSQL

Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
ThanksTry;
alter database MyDB
set recovery full
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%2339YqjXDFHA.1392@.tk2msftngp13.phx.gbl...
Id like to change the recovery models of my dbs to Full using TSQL. How can
I do so ? It seems possible using EM. But I wanted to try using TSQL..
Thanks