When I followed the instruction from "MCSE SQL Server 2000 Database Design/Implementation Training Kit" page 314, it shows:

-- Use @r_Code to hold the result code.
Declare @y_YtdSales int, @t_TitleText varchar(80), @r_Code int

-- Run the procedure and set @r_Code equal to the procedure.
Exec @r_Code = SalesForTitle
@YtdSales = @y_YtdSales Output,
@TitleTExt = @t_TitleText Output,
@Title = "%Garlic%"

--Determine the value of @r_Code and execute the code.
If @r_Code = 0
Select "Title" = @t_TitleText,
"Number of Sales" = @y_YtdSales, "Return Code" = @r_Code

Else If @r_Code = 1
Print "No matching titles in the database. Return code=" +
Convert(varchar(1),@r_Code)
Go

Execute the code and will return the following result set:

Title: Onions, Leeks, and Garlic: Cooing Screts ...
No. of Sales 375
Return Code 0


But, actually when I executed this code, I only got error:

Server: Msg 128, Level 15, State 1, Line 16
The name 'No matching titles in the database. Return code=' is not
permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Please help find out where I made mistake.

Thanks.

__________________
cocolocopolo

Report this post to a moderator

Do you have QUOTED_IDENTIFIER set to OFF?

When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. That seems to be the cause of your problem.

If QUOTED_IDENTIFIER is set to OFF, "stirng here" is interpreted as a constant string, else it is iterpreted as a column name.

Report this post to a moderator

Unless you have good reasons to do so, use single quotes for string rather than change the default QUOTED IDENTIFIER OFF option. If within the string there is an apostrophe, use two single quotes within the string. SQL Server will interpret that as a single apostrophe.

Report this post to a moderator

Thanks kingsunl. It works when I change double quote to single quote.

By the way, where I can turn on/off
QUOTED_IDENTIFIER ???

__________________
cocolocopolo

Report this post to a moderator

quote:

---

Originally posted by cocolocopolo
By the way, where I can turn on/off
QUOTED_IDENTIFIER ???

---

Report this post to a moderator

quote:

---

By the way, where I can turn on/off
QUOTED_IDENTIFIER ???

---

Report this post to a moderator

Thanks J-Ho and kingsunl (in alphabetical order).

__________________
cocolocopolo

Report this post to a moderator

In Enterprise Manager, highlight the database you want to set the options, right click to bring up the menu and choose Properties. From the database properties dialog box, click on the Options tab. Check or uncheck the database settings

May I add that, if you are running SQL server and the Query analyser on the same PC, setting this option OFF using EM does not have any effect on "Query analyser" since ODBC or Microsoft® OLE DB Provider automatically use SET QUOTED_IDENTIFIER ON.

The following is from BOL.

Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON when they connect. DB-Library does not set QUOTED_IDENTIFIER ON by default

So, unless you are using the DB-Library to connect to the SQL server, the setting on Ent manager is not 'felt'

__________________
Sadly, it is not what you know. It is who you know!

Last edited by limsam on 07-03-02 at 04:54 AM

Report this post to a moderator

Thanks limsam. I got one more idea now.

__________________
cocolocopolo

Report this post to a moderator