Showing posts with label base. Show all posts
Showing posts with label base. Show all posts

Tuesday, March 27, 2012

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.

Tuesday, March 20, 2012

Changing 100 table object owner names at once

Is there any way I can change the object owner for over
100 tables in a single data base at once in SQL 7 without
using the sp on each table?
Well you can do an update on sysobjects but if you look at the sp it does a lot of checking and clearing up.
Better to code a loop to execute it on all the tables necessary.
"Smith" wrote:

> Is there any way I can change the object owner for over
> 100 tables in a single data base at once in SQL 7 without
> using the sp on each table?
>
sql

Wednesday, March 7, 2012

Change the location of the database files

Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)
Refer to following url
http://support.microsoft.com/default...en-us%3B224071
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"Gal" <W32.virus@.wanadoo.fr> wrote in message
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
>
|||hi Gal,
"Gal" <W32.virus@.wanadoo.fr> ha scritto nel messaggio
news:uM8J5PqMEHA.3596@.tk2msftngp13.phx.gbl...
> Hi,
> I would like to move my base from drive C: to drive D: .How can I do this
> with MSDE (I don't have the MMC installed on the computer where I want to
do
> this)
you can do it detaching the desired database, moving the database files to
the new desired position a,d reattacchin the database...
log into oSql.exe..
1>EXEC sp_detach_db db_name
2>GO
move the file(s) to new location
1>EXEC sp_attach_db @.dbname = 'db_name' ,
2>@.filename1 = 'd:\:fulldatapath_of_DataFile.Mdf' ,
3>@.filename2 = 'd:\:fulldatapath_of_LogFile.Ldf'
4>GO
for your convenienence, you can have a look at a free prj of mine, available
at the link following my sign., which provide a user interface similar to
Enterprise Manager for full MSDE administration...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Change the location of the base

Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)
Try this article:
http://support.microsoft.com/default...b;en-us;224071
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gal" <W32.virus@.wanadoo.fr> wrote in message
news:ecXhZPqMEHA.2704@.TK2MSFTNGP10.phx.gbl...
Hi,
I would like to move my base from drive C: to drive D: .How can I do this
with MSDE (I don't have the MMC installed on the computer where I want to do
this)