Microsoft KB Archive/269558

= PRB: SQL Server Upgrade Wizard Generates a Warning for Stored Procedures Created with '.object_name' or '..object_name' =

Article ID: 269558

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q269558



SYMPTOMS
The complete name of an object in Microsoft SQL Server consists of four identifiers in the following format:

'server.database.owner_name.object_name'

Most object references use a three part name such as:

'database.owner_name.object_name'

You can create an object by using either 'owner_name.object_name' or just the object_name. SQL Server also allows you to create stored procedures by using just '.object_name' or '..object_name'.

When you create a stored procedure procedure name by using '.object_name' or '..object_name', the name is stored in the Syscomments system table and is preceded by the '.' or '..'. However, the procedure name is stored in the Sysobjects system table with just 'object_name'. During the upgrade process from Microsoft SQL Server 6.5 to Microsoft SQL Server 7.0 or Microsoft SQL Server 2000, the Upgrade Wizard (specifically Check65.exe, which the wizard calls), may generate a warning for these objects even though the object has not been renamed. The warning might look like the following: ============================================= The following syscomments entries are invalid on your 6.x SQL Server. It is recommended that you fix these problems before you continue.

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

mydb

Inconsistency Report For Database: mydb.

dbo.testproc Procedure

Object Renamed: Text does not match sysobjects. If you run 'SP_HELPTEXT testproc' or if a script of this object is generated, the CREATE PROCEDURE statement contains the object name preceded by a '.' without the object owner. This occurs because that exact syntax was used for the original creation of the object. For example: CREATE PROCEDURE .testproc AS select au_id from titleauthor go



WORKAROUND
To work around this behavior, use one of the following methods:  If a stored procedure was already created in the form '.object_name' or '..object_name' and because this does not affect the execution or proper upgrading of the procedure, you can safely ignore the warning from Check65.exe.

-or-

  If you want to remove the '.' or '..' from the CREATE PROCEDURE statement stored in the Syscomments table so that the warning is not received from the Upgrade Wizard, you can drop and re-create the stored procedure. To do this, on the SQL Server 6.x database generate a script through the SQL Server Enterprise Manager that includes statements to drop and re-create the object. In the script replace the 'CREATE PROCEDURE .object_name' or 'CREATE PROC ..object_name' statements with 'CREATE PROCEDURE object_owner.object_name' or 'CREATE PROC database_name.object_owner.object_name' or 'CREATE PROC object_name'.

For example, the following script: if exists (select * from sysobjects where id = object_id(N'[dbo].[testproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[testproc] GO

CREATE PROCEDURE .testproc AS select au_id from titleauthor GO Could be replaced with: if exists (select * from sysobjects where id = object_id(N'[dbo].[testproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[testproc] GO

CREATE PROCEDURE dbo.testproc AS select au_id from titleauthor GO 