Strange !!!

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  February 24, 2011 at 12:20 pm

  #247264

  I noticed that sql is case sensitive..

  I had a table dbo.alrt

  when i queried select * from alrt

  i got error: invalid object name

  then i queried select * from dbo.alrt

  then also same error

  Then i query : select * from dbo.ALRT

  and then I could see the errors..

  Isn't sql case insensitive?

  Or am I wrong?

  Regards,

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 24, 2011 at 12:35 pm

  #1291220

  it depends on the collation of the database you are connecting to.

  if you create a database with a case sensitive collation, all the tables and objects are going to require that extra level of care, since [Invoice] is not the same as [invoice]

  CREATE DATABASE [SandBoxCS] ON PRIMARY

  ( NAME = N'SandBoxCS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SandBox CS.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = N'SandBoxCS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SandBox CS_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

  COLLATE SQL_Latin1_General_CP1_CS_AS

  GO

  USE SandBoxCS

  GO

  SELECT * FROM SYS.COLUMNS --fails

  select * from sys.columns --works

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  February 24, 2011 at 12:54 pm

  #1291239

  Lowell (2/24/2011)

  ---

  it depends on the collation of the database you are connecting to.

  if you create a database with a case sensitive collation, all the tables and objects are going to require that extra level of care, since [Invoice] is not the same as [invoice]

  CREATE DATABASE [SandBoxCS] ON PRIMARY

  ( NAME = N'SandBoxCS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SandBox CS.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = N'SandBoxCS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\SandBox CS_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

  COLLATE SQL_Latin1_General_CP1_CS_AS

  GO

  USE SandBoxCS

  GO

  SELECT * FROM SYS.COLUMNS --fails

  select * from sys.columns --works

  The collation of my database is sql_latin1_general_cp850_bin

  Regards,

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 24, 2011 at 1:11 pm

  #1291250

  The collation of my database is sql_latin1_general_cp850_bin

  is a binary collation, so yes, that is case sensitive;

  unless you know it's a mistake, the collation must have been put in place for a reason...it's not a sharepoint database or anything, is it? i thought sharepoint db's were usually Latin1_General_CI_AS_KS_WS,

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  February 24, 2011 at 1:17 pm

  #1291255

  No, its not a sharepoint database.

  Regards,

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP