|
|
Posted by Patrik on 09/28/43 11:31
Thank you both for these clarifications. I will indeed stop using EM
and will read furthermor on quoted_identifier as I leave it on.
Erland Sommarskog wrote:
> 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]
|