I just installed SQL Server 2008 and imported AdventureWorksDatabase (for SQL 2005, as for 2008 that didn't worked).
It is now enforcing case sensitivity when I searched for any tables, views etcHence Person.contact table when written throws an error of Invalid column name, but when Person.Contact is written it shows all rows.
Plus the intellisense too doesn't work great.
Retrieved 13 December 2013. When Marie Was There official website. Archived from on 1 September 2014.
Romil NRomil N Ma, Kevin (12 December 2013). From the original on 10 January 2015.
6,3324343 gold badges110110 silver badges164164 bronze badges
2 Answers
Case sensitivity is controlled by the collation the database uses - check this by querying the system catalog views:
A collation name will be something like:
Latin1_General_CI_AS
The
_CI_
part is telling me here that it's a case insensitive collation. If you see a _CS_
then it's a case sensitive collation.You can change a database's default collation using: World of warcraft battle chest digital download.
and pick any valid collation here - use one with a
_CI_
to get a case-insensitive collation.Trouble is: even if you change the collation on the database level, certain tables might still have individual column that had a specific collation defined when the table was created. You could also change all of these, but that's going to be a bigger undertaking. See this article for more info and a script to check and possibly change individual columns in your tables.
The reason the intellisense might not be working properly is that the case sensitivity of database objects per se is controlled by the server collation - which can again be different from any database default.
To find out what the server's collation is, use:
Changing the server's system collation is quite a messy process and requires you to use the original
marc_smarc_ssetup.exe
as explained here.595k135135 gold badges11391139 silver badges12801280 bronze badges
The problem here is Case Sensitivity of the table name Contact. You should set
collation_name
of the Database AdventureWorks as Latin1_General_CI_AS
Check for collation_name:
If the
collation_name
is Latin1_General_BIN
or Latin1_General_CS_AS
change it to Latin1_General_CI_AS
If the Database has locked to do this action 'The database could not be exclusively locked to perform the operation.' . Alter the Database to Single User
and do
Revert back the Database to Multi User finally
Or
You can change the Collation in Database Properties.
Sathiya KumarSathiya Kumar
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008sql-server-2005collation or ask your own question.
I have a client login system that currently has a function once logged in for the client to change the password on their own. Unfortunately I need to have the old password NOT be case sensitive. How do I update this code to achieve this?<?
include('./scripts/sessionvalidate.php');
$iClientID = $_SESSION['clientid'];
$oldPass = $_REQUEST['txtOldPass'];
$newPass = $_REQUEST['txtNewPass'];
$ans=funCheckOldpass($iClientID, $oldPass);
if($anstrue)
{
if(strlen($newPass)>1)
{
$sql = ';
$sql = 'UPDATE tbl_clients SET passwords='.$newPass.'
WHERE clientid = '.$iClientID.'';
$res = mysql_query($sql);
if($res)$str_err_msg = '<li>Password successfully changed.</li>';
}else{
$str_err_msg = '<li>New password is too small.</li>';
}
}else{
$str_err_msg = '<li>Unable to change the password.</li>';
}
header('Location: profile.php?msg=$str_err_msg');
////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////
function funCheckOldpass($iClientID, $oldPass)
{
$myConn = dbConnect();
if($myConnfalse)
{
return false;
}
$sql = ';
$sql ='SELECT passwords FROM tbl_clients
WHERE clientid = '.$iClientID.'';
$rec = mysql_query($sql);
if(mysql_num_rows($rec)>0)
{
if(mysql_result($rec,0,'passwords')==$oldPass)
{
return true;
}else{
return false;
}
}else{
return false;
}
}
?>
MySQL documentation https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html says about system variable
lower_case_table_names
value 2:Mysql Case Sensitive Search
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.
And this is exactly what I see on my Mac when I run tests. First of all,
org.jetbrains.exposed.sql.tests.shared.DDLTests#testCreateMissingTablesAndColumns01
creates table Busy and MySQL shows it with capital B (SQL query is SELECT * FROM information_schema.tables where table_schema = 'testdb1';
). But immediately after that we create index CREATE UNIQUE INDEX Busy_busy_unique ON Busy (busy)
and our table renamed to busy in lowercase. So far so good, but org.jetbrains.exposed.sql.tests.shared.DDLTests#addAutoPrimaryKey
also has Busy-table logic and it checks if Busy table exists. When we load all table names in org.jetbrains.exposed.sql.vendors.VendorDialect#allTablesNames
we load table name as busy, no chance to convert to Busy. org.jetbrains.exposed.sql.vendors.VendorDialect#getInProperCase
checks that both TransactionManager.current().db.metadata.storesUpperCaseIdentifiers()
and TransactionManager.current().db.metadata.storesLowerCaseIdentifiers()
are false and org.jetbrains.exposed.sql.Table#nameInDatabaseCase
do not converts table name to lowercase. After that org.jetbrains.exposed.sql.vendors.VendorDialect#tableExists
can't find table Busy and org.jetbrains.exposed.sql.SchemaUtils#createStatements
generates statements to create table Busy.CREATE TABLE IF NOT EXISTS Busy (busy BOOLEAN NOT NULL)
works fine and does nothing, but index creation CREATE UNIQUE INDEX Busy_busy_unique ON Busy (busy)
fails with weird Duplicate key name 'Busy_busy_unique'
. And each isolated test works fine.I suggest that
org.jetbrains.exposed.sql.vendors.VendorDialect#tableExists
should ignorestoresUpperCaseIdentifiers()/storesUpperCaseIdentifiers()
and compare lowercases against lowercases. It's possible to support system property that forces to compare table names without converting it to lowercase. Probably, org.jetbrains.exposed.sql.vendors.VendorDialect#allTablesNames
shouldn't do any conversion.How to manually install skyrim mods special edition. Also I am not sure that we need to use
org.jetbrains.exposed.sql.SchemaUtils#withDataBaseLock
in org.jetbrains.exposed.sql.SchemaUtils#createMissingTablesAndColumns
.