By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,574 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,574 IT Pros & Developers. It's quick & easy.

stored procedure update HUGE PROBLEM

P: n/a
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.

Nov 8 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

Nov 8 '05 #2

P: n/a
Don't ever create or alter procs in Enterprise Manager. Use Query
Analyzer and specify SET QUOTED_IDENTIFIER 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 = '''+@utilisateur+''''

Don't use double quotes for string delimiters.

--
David Portas
SQL Server MVP
--

Nov 8 '05 #3

P: n/a
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_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, es****@sommarskog.se

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

Nov 8 '05 #4

P: n/a
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 (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_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, es****@sommarskog.se

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


Nov 9 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.