KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q255664: Installation Problem Due to SQL 7.0 Quoted_Identifier Attribute

Article: Q255664
Product(s): Microsoft Systems Management Server
Version(s): 2.0,2.0 SP1,2.0 SP2
Operating System(s): 
Keyword(s): kbsms200
Last Modified: 23-OCT-2000

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft Systems Management Server versions 2.0, 2.0 SP1, 2.0 SP2 
-------------------------------------------------------------------------------

SYMPTOMS
========

Your attempt to install Microsoft Systems Management Server (SMS) did not
succeed. The SMS Installation Wizard may cause the following error message to be
displayed on your computer screen:

  Systems Management Server Setup cannot continue because of the following
  error.

  Setup cannot create the required database tables. Contact your SQL
  Administrator.

Also, the Smssetup.log file may contain the following error message:

  2-21-2000 11:54:46 SqlExecute IF NOT EXISTS (select * from NextIds where
  IdName = "NextCollectionID") BEGIN IF NOT EXISTS (select CollectionID from
  Collections) BEGIN insert NextIds (IdName, IdValue) values
  ('NextCollectionID', 13) END ELSE BEGIN insert NextIds (IdName, IdValue)
  select 'NextCollectionID', max(CollectionID) + 1 from Collections END END
  02-21-2000 11:54:47

  Cannot execute sql command IF NOT EXISTS (select * from NextIds where IdName =
  "NextCollectionID")

CAUSE
=====

This behavior can occur because of the double quotation of "NextCollectionID"
located in the preceding select statement. If the Quoted_Identifier attribute is
selected, SQL misinterprets "NextCollectionID" in the SMS installation script as
a SQL column name.

WORKAROUND
==========

To work around this behavior, you must change the setting of the
Quoted_Identifier attribute. By default, this attribute is not enabled. To
change the setting of this attribute, perform the following steps:

1. Run SQL Enterprise Manager.

2. Right-click SQL Server, and then click Properties.

3. On the Connection tab, locate the Quoted_Identifier attribute and set it to
  Off.

4. After SMS is installed successfully, the Quoted_Identifier attribute can be
  set to On.

MORE INFORMATION
================

The setting of the Quoted_Identifier attribute determines what meaning Microsoft
SQL Server gives to double quotation marks ("). When the Quoted_Identifier
attribute is set to Off, double quotation marks delimit a character string, just
as single quotation marks do. When the Quoted_Identifier attribute is set to On,
double quotation marks delimit an identifier, such as a column name.

An identifier must be enclosed in double quotation marks (for example, if its
name contains characters that are restricted in an identifier, including spaces
and punctuation, or if the name conflicts with a reserved word in Transact-SQL).
Regardless of the setting of the Quoted_Identifier attribute, an identifier can
also be delimited by square brackets.

The meaning of the following statement, for example, depends on whether the
Quoted_Identifier attribute is set to On or Off:

  SELECT "x" FROM T

If the Quoted_Identifier attribute is set to On, "x" is interpreted to mean the
column named x. If it is set to Off, "x" is the constant string x and is
equivalent to the letter x.

If the preceding Select statement example had been part of a stored procedure
created when the Quoted_Identifier attribute had been set to On, "x" would
always mean the column named x. Even if the setting of the Quoted_Identifier
attribute was later switched to Off, the stored procedure would behave as if it
had been set to On and treat "x" as the column named x.

When the SQL Server Upgrade Wizard re-creates database objects in SQL Server
version 7.0, the setting of the Quoted_Identifier attribute determines how all
of these objects behave. If all database objects had been created in SQL Server
6.x with the same setting of the Quoted_Identifier attribute, click that
setting, either On or Off. If objects had been created in SQL Server 6.x with a
mix of the two settings, or if you are unsure of the settings used, click
Mixed.

With the Mixed option, the SQL Server Upgrade Wizard first converts all objects
containing double quotation marks with the Quoted_Identifier attribute set to
On. The SQL Server Upgrade Wizard then converts any objects that could not be
created with the Quoted_Identifier attribute set to Off.

Additional query words: prodsms

======================================================================
Keywords          : kbsms200 
Technology        : kbSMSSearch kbSMS200 kbSMS200SP1 kbSMS200SP2
Version           : :2.0,2.0 SP1,2.0 SP2
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.