Single Quote and Double Quote

• isd83

  Ten Centuries

  Points: 1066

  More actions

  June 19, 2006 at 5:18 pm

  #118421

  I recently migrate a SQL 7.0 box from NT to Windows 2000. Many scripts with double quotes no longer work unless I change them to single quote like the shown here:

  select "Count of HFCD_access_rec:", count(*) from HFCD.dbo.tv_accss_rec

  It will take a lot of time to search and change the scripts. Is there any way to tell sql 7.0 on a window 2000 box to use single quote or this is at the OS level? Thanks. 

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  June 19, 2006 at 5:55 pm

  #644720

  Does it think the stuff in the double quotes is a column name?  If so, the database options may be set to QUOTED_IDENTIFER ON, and that can be turned off to put you back to the old behavior.  I don't know why upgrading would have changed it, though...

  ---

  And then again, I might be wrong ...
  David Webb

• Prakash Sawant

  SSCrazy

  Points: 2173

  More actions

  June 20, 2006 at 12:28 am

  #644730

  I am not very sure why OS will change the way query is writen in SQL server.

  I think you just need to set the "SET QUOTED_IDENTIFIER ON"

  for more info on this see BOL..

   

   

  ------------
  Prakash Sawant

  http://psawant.blogspot.com

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  June 20, 2006 at 3:39 am

  #644750

  The 'correct' way to denote literal strings in T-SQL is by a single quote, not double quotes.

  If you always use single quotes, then you'll never see this problem.

  The reason being that double quotes serve different purposes depending on if QUOTED_IDENTIFIER is set to ON or OFF. Single quotes are not affected by this setting, so the issue never arises regardless of how QUOTED_IDENTIFER is set.

  Anyway, if your intenion with "Count of HFCD_access_rec:" is to quote as a literal string, then depending on how QUOTED_IDENTIFIER is set, it will be that, or SQL Server will think that it's an object's name. When the latter happens, you'll get an error. This is the reason that it's more robust to code literals with single quotes, since you have no programmatic control over which SET settings that any given connection may use that uses the code.

  The 'short fix' is to set QUOTED_IDENTIFIER appropriately, the 'better fix' is to replace the double quotes with single quotes where necessary.

  /Kenneth

   

• stax68

  SSChampion

  Points: 11761

  More actions

  June 20, 2006 at 6:32 am

  #644783

  I remember in v6.5 in which quoted_identifier was off by default, I used to use single quotes for the outermost layer, then doubles for embedded quotes. So you might need to check for code like that, too, and replace embedded double-quote characters with pairs of single-quote characters.

  set

  quoted_identifier off

  exec

  ('exec("select 99999")') --embedded double quotes

  go

  set

  quoted_identifier on

  exec

  ('exec(''select 99999'')') --embedded single quotes are doubled-up

   

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant