Changing Collations...

Mar 2, 2006

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

View 7 Replies


ADVERTISEMENT

Changing Collations

Dec 19, 2005

Hi,We have around 150 databases as case sensitive, and we are planning tochange it to case insensitive. Each database has around 180 tables, Ihave changed the collation on DB, but changing collation manually oneach column is a daunting process. Is there any script or tool whichcan assist in doing this.Appreciate your help.ThanksSAI

View 5 Replies View Related

Changing Collations For Master Database.

May 3, 2004

Hi there


I am using SQL server 2000 and
I want to Change Server Collations from SQL_Latin1_General_CP850_BIN to
SQL_Latin1_General_CP1_CS_AS.

Can anybody help me in this regard.

Rgds

Wilson

View 5 Replies View Related

Do You Know Anything About Collations

Nov 5, 2003

I want to change the collation on a column to be case-sensitive without affecting anything else. What do I do?

View 1 Replies View Related

Collations

Jun 26, 2003

Can anyone point me to collation chart for Latin1_General and CP850?

I'm having conversion difficulties dealing with diacritics and want to look into the specifics.

Thanks,

Al

View 1 Replies View Related

Collations

Nov 2, 2007

Hi there. I'm trying to create a copy of a remote database for development purposes. I am useing MySQL, the remote database is in MSSQL. The create table commands contain a collation titled

quote:SQL_Latin1_General_CP1_CI_AS

which MySQL doesn't recognise. The three most similar collations I can find in MySQL are:
quote:
'latin1_general_ci',
'latin2_general_ci'
'latin7_general_ci'


Does anyone know the difference between these collations? And whether any of them is equivalent to the MSSQL code I have to implement?

thanks! Dave

View 3 Replies View Related

Need Help With Collations

Jul 20, 2005

I want to create a database where the table names / column names / SP namesare NOTcase sensitive but where the data in the tables is, so that I canbuild a unique index where 'test' and TEST' is accepted as different.I have tried Installing SQL with a Collation designator with the CaseSensitive option checked- this caused all sp names / column names / table names to be casesensitive - not what I want.I have also tried installing SQL and selecting a SQL Collation and pickingan option from the drop down list. - again this cause everything to be casesensitive - not desiredDo I have to install SQL with a non case sensitive collation, then set eachcolumn in the table to be case sensitive? What if any are the problems I amlikely to come across?ThanksSteve

View 2 Replies View Related

SQL2000 Collations

Feb 4, 2002

Hi,
I noticed that when I installed SQL2000,with "typical install",
the default, the following will be used:
SQL collation (dictionary order, case-insensitive, assent sensitive)
sp_helpsort will give
SQL_Latin1_General_CP1_CI_AI

Is there any difference if I choose the "custom install", then and choose
the windows locale which gives the result of sp_helpsort:
Latin1_General_CI_AI

SQL_Latin1_General_CP1_CI_AI is supposed for backward compatibilty, so are
they actually equivalent ??
Any impact or difference we have to be aware of ??
Anyone knows..Thx

View 1 Replies View Related

Collations And Nvarchar

Aug 22, 2007

Should a programmer bother using nvarchar for a person's name (special chracters allowed) if I still use SQL_Latin1_General_CP1_CI_AS?

View 1 Replies View Related

English And German Collations

Apr 10, 2007

I have developed a tool to allow project developers to easily re-create the entire schema for our base product. The current issue involves setting the correct collation for the customers' region. Our brother company in Germany uses the same db creation tool and scripts, and we here in the US also have customers in South America. My ultimate question is "what subset of collation names would be necessary to provide the project developer?" I could query the database to get all the collation names, but I think it was around 1000 names. Can I query to get a smaller subset of the most relevant collation names?

Ed

View 3 Replies View Related

Collations And Simple Import From A Csv-file

Apr 23, 2007

Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database
This csv-file is defined as a variable length file
Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe?
I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?



I appreciate all help.

Regards

Thomas Ivarsson

View 3 Replies View Related

Change Collations In Many Tables Defined Using COLLATE

Jun 9, 2004

I have (inherited a) a db with lots of tables looking like the definition at the end of the message.

I need to change all these tables, so that all the textual fields follow the default DB collation.

Any help appreciated!
THANX...

CREATE TABLE [Club] (
[id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE Greek_CI_AS NOT NULL ,
[address] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[zoomAreaId] [int] NULL ,
[phone] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[contact] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[clubCategoryId] [int] NULL ,
[unused] [varchar] (2) COLLATE Greek_CI_AS NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL ,
[sunday] [int] NULL ,
[comments] [text] COLLATE Greek_CI_AS NULL ,
[URL] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[image] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[priceCategoryId] [int] NULL ,
[ratingId] [int] NULL ,
[entryDate] [datetime] NULL ,
[WAPText] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[SMSText] [varchar] (160) COLLATE Greek_CI_AS NULL ,
[SMSAddress] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[active] [bit] NOT NULL ,
[content_id] [bigint] NULL ,
[disp_as_propos] [bit] NULL ,
[date2disp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

View 2 Replies View Related

Unable To Resolve Column Level Collations

May 14, 2008

Hi,


When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.


Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".

Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.

On further trial and error I found that if I remove the fast load option, it works without glitch.

Its kind of wierd though....

Any body has faced this situation?

Thanks
Karunakaran

View 5 Replies View Related

Updating Systemtables Directly To Change Column-collations

Mar 3, 2005

Hi there.

Is there any way to update the system tables directly, to alter the collations of the columns in the user db's?

I've tried the script below:

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CS_AS' WHERE name = '<AddrCode>'
AND id = object_id('<Compliance>')

but, I get the following error message:

Server: Msg 271, Level 16, State 1, Line 1
Column 'collation' cannot be modified because it is a computed column.

Can you please help me! I need to do thousands of these, and most of them has constraints on, so my script I generated to do the ALTER TABLE.... ALTER COLUMN does not suffice.

Thanks.

View 2 Replies View Related

Why Do Some System Views Have Columns With Different Collations Then The Default Collation?

Oct 8, 2007

I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.


Run the following command.


Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type

You will receive the following error.


Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.


Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.


My question is why did Microsoft need to make this collation different for these columns?


Any ideas?


Thanks, Dave

View 3 Replies View Related

Integration Services :: How To Write ETL Package Addressing Different Collations Or Code Pages

Jul 13, 2015

Default code page in ETL package is 1252 which will not work if the collation is different e.g in Japanese_CI_AS, it is 932.

My question is how to write a generic ETL package so that it can cater any collation or any code page.

View 5 Replies View Related

Unable To Resolve Column Level Collations. Bulk-copy Cannot Continue

May 13, 2008

Hi,

When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.

Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.

Some body can shed light on this?

Thanks
Karun

View 6 Replies View Related

Changing The Sqldataroot

Dec 14, 2000

A college forgot to change the data root when installing a developer server. My question is can this be changed whitout reinstalling the server and restoring the developer databases.
I found sqldataroot under [HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServerSetu pSQLDataRoot]
If I stop the sql server and change this value and move the *.mdb and *.ldf files. Would I risk currupting the databases and do I need to change something else? For example would changeing this value change the default path for backupdevices?

View 2 Replies View Related

Changing Passwords

Dec 5, 2000

I have a problem where our developers would like to change the passwords of the SQL Logins without knowing the users old password.

The only way they can use the below syntax is that they must be a member of the SYSAdmin Server Role.

sp_password NULL, 'newpassword',Max

I need a way to allow them to change the passwords without making a user a member of the SYSAdmin Server Role.

Any thoughts would be greatly apprecitated. Thanks in advance.

Max

View 1 Replies View Related

Changing Security

Dec 20, 2000

I need to change my security settings from "Windows NT Only" to "SQL Server and Windows NT". Does anyone know of any issues with doing this? Does this subtract anything or does it only add access??

View 1 Replies View Related

Changing License Key

Jun 5, 2002

Is there a way to change the license key after installation of SQL7.0?

View 1 Replies View Related

Changing Database Name

Nov 19, 2002

hi, I have created a database in sql server 2000. I need to duplicate the database under different name, how can I do that?
Also, can I rename a database name?
thanks
Ali

View 3 Replies View Related

Changing Server Name

Dec 7, 1999

Hi all,

I need to change the NT Server computer name (and the SQL Server name). I know how to do it in NT, but how would I go about changing the SQL Server name. Can anyone help me with this? Is this an easy process?

Thanks,
LC

View 3 Replies View Related

Someone Changing Permissions..

Mar 1, 2005

Someone is changing permissions on our production sql databases frequently which is causing big problems..any idea how to know who is changing and when? Any code that can be written to capture such activity?

thanks,
dbfan.

View 6 Replies View Related

Changing The Sa Password

Aug 6, 1998

I`m running SQL 6.5 with standard security. We`re running NT 4.0. I am a member of the NT Admin group - which, of course, has sa privileges in SQL Server. I changed the sa password (via Enterpise Manager on my client machine); and it worked. I tested the password change by connecting to ISQL/w - both on my client machine and at the server machine.

However, when I attempted to connect to the server via Enterprise Manager (SEM) - on my client machine - my login attempt failed. But at the server machine, I connected just fine.

Somehow, even though we`re using standard security, I`m getting locked out of SEM because of the new password. Changing the sa password back to what it had been resolved the problem; but the old password was only meant to be temporary.

Any suggestions or explanations?

View 3 Replies View Related

Changing SA Passwword

Dec 15, 1998

Hi,

During SQL installation I left the SA password blank. Now when I try to change it, the event log fills up with the following errors:

Mesg 18456 : Login failed- User: sa.
I am using SQL database for SMS and I have a SMS service account.
The error type in event viewer is "Failure Audit".

I am stopping and starting SQL once changes are made. I am also registering server with "Use Trusted Connection". I have also tried with "Use Standard Security" and the SA account.

Can anyone tell me how to change my SA password without affecting my SMS service account?

Thanks,

Terry

View 1 Replies View Related

Changing Column Name

Nov 9, 2004

Hi all,

I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.

View 1 Replies View Related

Changing A Column Name

Nov 30, 2005

How can you change a Column name for MS SQL 2000 ?

something like :

ALTER TABLE [dbo].[MyTable]
OldName RENAME NewName
GO

thank you

View 6 Replies View Related

Tracking Who Is Changing What?

Aug 9, 2006

We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?

View 4 Replies View Related

Changing Formats

Apr 25, 2007

Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm

The file is being pulled into a varchar field..... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006


any suggestions

View 1 Replies View Related

Changing Compatability From 65 To 80

Apr 21, 2008

Finally, I may have a good enough excuse to justify changing the compatabilty level of one of the biggies I look after!

Before I go ahead with the change I just wanted to run a couple of questions passed the community.
I know this is very vague, but what sort of problems should I expect? I know there are a few syntax changes, but I don't imagine I will have a problem going up in compatability level!
What is the best method to perform this?
A very simple

EXEC sp_dbcmptlevel 'playdb', 80

Or

ALTER DATABASE SET SINGLE_USER
EXEC sp_dbcmptlevel 'playdb', 80
ALTER DATABASE SET MULTI_USER

How long does this process take; are we talking seconds, minutes, hours? I imagine it's seconds, but I pose this question because a colleague reckons it will take longer because of the database size. I argue that because it's a db configuration setting, it will make no difference...

Thanks a bunch,
George

View 14 Replies View Related

Changing A Date

Jan 25, 2012

SO, If I had a problem with server computer date & stored false dates for some operations, How can I alter those dates to correct dates.

View 1 Replies View Related

Changing Server Name

Jan 29, 2004

Hi. Sorry to be asking this. I know there is a KB article with the steps for changing a SQL2K server name, but I can't find it for the life of me.

Anyone have it tagged?

TIA

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved