November 2, 2006 at 8:22 pm
My laptop has SQL Server 2000 with default collation config.
So
SQL_Latin1_General_CP1_CI_AS <--- my laptop SQL Server collation
SQL_Latin1_General_CP1_CI_AS <--- NSurvey database collation
My hosting server has a different config:
SQL_Latin1_General_CP1_CS_AS <--- SQL Server collation
SQL_Latin1_General_CP1_CI_AS <--- NSurvey database collation
NSurvey V1.6 is open source code but the owner did not release the code for data access layer. Nsurvey runs fine on my laptop where the collations are the same. When it runs on my hosting server, it errors with
System.Data.SqlClient.SqlException: Must declare the variable '@UserID'.
System.Data.SqlClient.SqlException is a .NET class. This error is a result of case sensitivity. I can't change the code since I don't have the source code.
I would like to understand how the SQL Server collation and the database collation are applied? It does not seem that the database collation supercedes the server collation in my hosting server scenario.
THanks!
November 2, 2006 at 10:18 pm
hi
database collation supercedes the sql server collation. The collation set for the datbase is the default collation for the columns in the tables. U can change the collation of each column in a table and then that collation (set for the column) would apply.
What i mean to say is collation set at the lowest level applies.
Server Level ---> Database Level ---> Column Level
"Keep Trying"
November 3, 2006 at 7:53 am
What does the database collation apply to?
(only to the "DATA" in the database?) and not the database objects? It seems to me that with a case insensitive collation at the database level, the stored procs are still screening for case sensitivity. So I am having to alter all the stored procs(hundreds of them!).
Thanks
November 3, 2006 at 8:55 am
The server collation will not affect your database collation if you do column level collation and use collation precedence in your DML(data manipulation language) in SQL Server 2000/2005 but I think in 2000 it is limited. So start with the links below and run a search in the BOL(books online). In SQL Server 2000 and 2005 the collation is on the column level, so you can define as needed.
(only to the "DATA" in the database?) and not the database objects?
In 2005 you can define it in both columns and stored procs, in 2000 it is just the DDL(data definition language). And you don't need source to deal with collation because your DDL and DML are just one right click away in the object browser under Tables and Stored Procs. If you still have question post again. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
http://msdn2.microsoft.com/en-us/library/ms144250.aspx
http://msdn2.microsoft.com/en-us/library/ms179886.aspx
Kind regards,
Gift Peddie
November 6, 2006 at 1:11 pm
here is a script i use to change collation for a db that is different than what i usually use.
note that the sample below has TOP 5 limitation to see an example, you'd remove that in order to get the statements for ALL the fields in the database:
after changing the collation, you'd want to recompile all objects(views,procs,functions) to address collation issues in them i believe.
SET NOCOUNT ON
SELECT 'alter database ' + db_name() + ' COLLATE SQL_Latin1_General_CP1_CI_AS'
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')
ORDER BY TBLNAME,COLNAME
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE)
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')
ORDER BY TBLNAME,COLNAME
typical results:
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| alter database Whatever COLLATE SQL_Latin1_General_CP1_CI_AS |
| ALTERSTMT TBLNAME COLNAME DATATYPE length |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------ |
| ALTER TABLE ACACTSCR ALTER COLUMN DESCRIP varchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ACACTSCR DESCRIP varchar 500 |
| ALTER TABLE ACACTSCR ALTER COLUMN TITLE varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ACACTSCR TITLE varchar 80 |
| ALTER TABLE ACACTTYP ALTER COLUMN DESCRIP varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ACACTTYP DESCRIP varchar 80 |
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply