|
|
Posted by Erland Sommarskog on 11/26/44 11:31
Patrik (patrik.maheux@umontreal.ca) writes:
> I need help on this one. For the past two days, whenever I make a
> modification to a stored procedure using enterprise manager (Apply),
> the stored procedure stops working.
>
> If I copy it under a new name, it works but as soon as I make a
> modification, it stops working. I am going crazy on this one.
>
> The error : wrong column name. He doesn't recognize the column name on
> a very straighforward line : SELECT @SQL1 = 'SELECT @Total1 = Count(*)
> FROM dbo.Tbl_Report WHERE Utilisateur = "'+@utilisateur+'"
>
> For example 'sa' is not a column (it skips Utilisateur as the column
> name).
>
> Very strange. Never had this problem in the past. thank you very much.
The problem is that " sometimes is a string delimiter in T-SQL and
sometimes it is an identifier delimiter. This depends on the setting
QUOTED_IDENTIFIER. This setting is ON by default in most environment,
and this is also the recommended setting since some functionality
requires this setting to be in effect. It happens to be the case that
EM has this setting off by default, but EM is really a crappy tool for
maintaing stored procedures. Use Query Analyzer instead.
I appreciate that when working with dynamic SQL, it's very nice to
have access to both ' and " as string delimiters. You can put
SET QUOTED_IDENTIFIER OFF first in you dynamic SQL, and then you can
use " within the dynamic SQL. But that presumes that you are not using
indexed views, so best is probably to stick with ' only - even if
the list of ' can sometimes become unbelievably long.
By the way, there is no need for dynamic SQL in the snippet you posted.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|