We recently received a script for a modified stored proc from a vendor.  My colleague ran the script on the test database, but the application owner wasn't ready to test the new functionality.  My colleague knew she had to revert back to the old proc and scripted out the proc from production using SQL Enterprise Manager.  Strangely, the script she created produced this error when she tried to run it:

The identifier that starts with  ' <some code>' is too long. Maximum length is 128.

I hadn't seen this error before and took a look at the proc. Inside the proc, the developer had used double-quotes around literal strings, one of which was in the error message. I knew from the error message the SQL Server was interpreting the string as object identifyer.  I saw that the developer had done the same thing in his new proc.  The difference was that he started his script with this line:

SET QUOTED_IDENTIFIER OFF

My colleague's script from EM started with SET QUOTED IDENTIFIER ON.  We changed the line to match our vendor and the script worked.

Another interesting thing is that if I use SQL Server Management Studio to script the proc, I get the setting that was used when the script was created.  This setting is not something I have ever modified before, I like to stick with the standard.

SET QUOTED_IDENTIFIER ON allow you to use double-quotes around object names or aliases.  In that case, you can not use double-quotes around literal strings.  If you use the setting SET QUOTED_IDENTIFIER OFF, you can use double-quotes around the literals, but you can't use double-quotes around object names or aliases.  The advantage is that tick marks or single-quotes in the literal strings can just be used without escaping them. To escape the single-quote, use two single-quotes. 

With the setting off,you can do this:

Select LastName from names where LastName = "O'Neil"

With the seting on, you have to do this:

Select LastName from names where LastName = 'O''Neil'