I want to create a database where the table names / column names / SP names
are NOTcase sensitive but where the data in the tables is, so that I can
build a unique index where 'test' and TEST' is accepted as different.
I have tried Installing SQL with a Collation designator with the Case
Sensitive option checked
- this caused all sp names / column names / table names to be case
sensitive - not what I want.
I have also tried installing SQL and selecting a SQL Collation and picking
an option from the drop down list. - again this cause everything to be case
sensitive - not desired
Do I have to install SQL with a non case sensitive collation, then set each
column in the table to be case sensitive? What if any are the problems I am
likely to come across?
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?
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
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? ????
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
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?
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?
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.
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.
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?
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.
