stored procedure update HUGE PROBLEM

[Patrik]

Hi,

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.

[Jens]

Don´t know which "as" you mean but I would assume to write the
character rather than doublequoting them, that more readable.

SELECT @SQL1 = 'SELECT @Total1 = Count(*) FROM dbo.Tbl_Report WHERE
Utilisateur =' +CHAR(39) +@utilisateur +CHAR(39)

HTH, Jens Suessmeyer.

[David Portas]

Don't ever create or alter procs in Enterprise Manager. Use Query
Analyzer and specify SET QUOTED_IDENTIFI ER and ANS_NULLS ON or OFF as
required. Prefereably always ON.

Your code fragment looks wrong anyway. Presumably it should be:

SELECT @SQL1 = 'SELECT @Total1 = Count(*) FROM dbo.Tbl_Report WHERE
Utilisateur = '''+@utilisateu r+''''

Don't use double quotes for string delimiters.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Patrik (pa***********@ 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_IDENTIFI ER. 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_IDENTIFI ER 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Patrik]

Thank you both for these clarifications. I will indeed stop using EM
and will read furthermor on quoted_identifi er as I leave it on.

Erland Sommarskog wrote:

Patrik (pa***********@ 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_IDENTIFI ER. 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_IDENTIFI ER 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp