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?
>

Changing Colors of Set of Rows based on Group / Matrices

Norman Yuan helpe me very much with changing colors on alternate rows, but
now I need help with a couple of other reports that use matrices.
Is there an ""on group change" event that I can test so that I can determine
when the group changes so that I can change the background color for all the
rows in that group?
Please let me know.
Thanks in advacne.I guess that you used ROWNUMBER(Nothing) now, simply use the
ROWNUMBER(Groupname) for putting the same logic in place for groups.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Changing Collations...

I have looked thru the forum but have a couple of questions:
1) data base was created with case insensitive collation
2) all the tables were then create (72 tables) and by default got the CI collation on all columns
3) lots of data was added 2GB
4) discovered mistake and altered the database to have case sensitive collation
5).... how to change all the collations for all the columns without doing them all manually
can i backup the database and change some settings and restore it?
export all the data, drop and recreate tables and import data?
??Why would you want case sensitive?

99.999% of every database I work with use the default out of the box collation.

It's only when I deal with 3rd party vendors that I have to mess with a different one.

This makes life painful at times.|||and the database has to be case sensitive because the customer wants to be able to use lower and upper case codes in some of the files.|||Case insensetive doesn't mean you can't have mixed case, it just means that 'a' = 'A'. That would be false in Case Insesitive.

I would leave it alone.|||fwiw i've used binary sort order (case-sensitive) for vldb implementations that use integer keys for joins and gotten tested 30% gains in join performance.|||The apps being used for the database is an OLD cobol based programs. all thru the programs we have tests for lower and upper case values that must NOT equate 'a' as equal to 'A'. so that is why i MUST change the collation. it is not an option to change anything else about the database/tables.
anyways, i was hoping that someone here might know of a better way or have a tool that does it.|||I know you can not do a restore...

And you should probably create an instance on the box that is set up the way you want it.

Script out the database, create it in that instance, and bcp everything out and back in. Use Native format

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2|||The general sequence is this:

1) Research and find all columns that are defined as char, varchar, text or ntext.
2) Find all indexes that include any of these columns
3) Drop all indexes found in step 2
4) Issue the "alter table alter column" command to change the collation of each char column.
5) drop the index you missed in step 3 (always happens to me, anyway)
6) Finish altering tables
7) recreate all indexes that have been dropped.

Before you go through this, you may want to get a script of all of the indexes, in case you miss one here or there. A backup of the whole database is a handy thing, as well. Good luck.

Changing all VARCHAR to NVARCHAR database-wide?

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-BretYou should be able to write a script to change your tables - it will take you a while since you probably will need to:

For each table with Varchar columns
- Create a temp table
- Copy all the data to the temp table
- Remove all FK constraints to the table
- Drop the table
- Create the table with NVarchar columns
- Insert the data from the temp table
- Create the IDENTITY column if applicable
- Recreate all the FK constraints

If you have fewer than 100 tables or so it might be quicker to use EM to do this.

For the stored procs: The quickest way to do the change is to allow updates on system tables. You can change this in EM. Then you can find all the stored procedures in sysobjects, with the actual TSQL in syscomments (in the 'text' column) Of course it's not normally a good idea to update system tables directly, and I'm not 100% sure this will work, but it's worth a shot:

update C
 set C.Text = replace(C.[Text], 'varchar', 'nvarchar')
 from syscomments C
 join sysobjects O
 on C.[id] = O.[id]
 and O.Type = 'P'

Now obviously there's a lot of things that can go wrong here. You string 'varchar' may exist as a part of a variable name, or column name or some other place where it's not supposed to be replaced by 'nvarchar'. It's possible that a stored procedure is long enough to use more than one row in syscomments, and this will be a problem since 'nvarchar' is one character more than 'varchar' so the update will fail with 'string or binary data would be truncated'.

So be careful and be sure to test every SP. Again you can write a script to execute every stored proc in your DB and check for errors.

Have fun!|||You have to change to Unicode Collation, nothing serious in SQL Server 2000 you can change it down to column level but Unicode in SQL Server is Multi byte unlike .NET where you can choose UTF 8 which is equivalent to ASCII. Yes Stored Procs are stored in .SQL text files that you can open with notepad and can be seen under stored procs in the Enterprise Manager in your Database. Run a search in the BOL(books online) for Mixed collation and ALTER PROCEDURE. If you are now using UTF 8 in .NET you may not run into storage problems except disk space which will double. Stored procs in SQL Server 2000 can be up to 128 megs in size. Hope this helps.

Kind regards,
Gift Peddie