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
REFERENCES
For more information about Check65.exe and its use, please refer to the following article in the Microsoft Knowledge Base:
255254 INF: How to Use Check65.exe to Check for Problems Before Upgrade
For more information about the effects of renaming a stored procedure, a view or a trigger, please see the following article in the Microsoft Knowledge Base:
243198 PRB: Renaming Stored Procedure, View or Trigger Does Not Update SYSCOMMENTS Table