SQL server exams

Author

cocolocopolo

2002-06-28, 12:15 pm

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.

kingsunl

2002-06-28, 5:30 pm

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.

kingsunl

2002-06-29, 6:42 pm

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.

cocolocopolo

2002-06-29, 10:56 pm

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

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

J-Ho

2002-06-30, 2:37 pm

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

Thru SQL. Use the following SQL Statements to turn it on and off:

set quoted_identifier on
set quoted_identifier off

Note, in a batch you need to separate the "set" statements with "go".

Here's a 'steps to repro':
1) Open Enterprise Manger, expand any database, and drill down 'til you see the list of stored procedures in the details pane
2) Select any (or all) stored procedure in the details pane, and press Ctrl+C for 'Copy'
3) Open Notepad or ISQLW
4) Paste in Notepad or ISQLW

You see how the query batch starts of with:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

kingsunl

2002-07-02, 4:59 pm

quote:

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

cocolocopolo

2002-07-02, 11:44 pm

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

limsam

2002-07-02, 11:45 pm

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'

cocolocopolo

2002-07-05, 2:12 am

Thanks limsam. I got one more idea now.

Sponsored Links