Object already exists error when objects have been renamed
jasonrcox
Posts: 4
I am comparing two databases. The first has a stored procedure called sp2005GetNonDocumentIssues and sp2005GetNonDocumentIssues_old. The "_old" was created by renaming the original sp2005GetNonDocumentIssues stored procedure and then creating the new one with the same name. I mention this because I know the renaming objects via the enterprise manager does not always update the master references and object names properly.
Anyway, the second database has neither of the above mentioned stored procedures. The script generated by SQL Compare to make the second database the same as the first has the following pertinent lines:
PRINT N'Creating [dbo].[sp2005GetNonDocumentIssues]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp2005GetNonDocumentIssues]
<..... the code for the procedure goes in here ...>
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[sp2005GetNonDocumentIssues_old]'
GO
CREATE PROCEDURE [dbo].[sp2005GetNonDocumentIssues]
<..... the code for the procedure goes in here ...>
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
Notice that the comments correctly indicate that the sp2005GetNonDocumentIssues procedure is created first and then the sp2005GetNonDocumentIssues_old procedure is created next. The actual TSQL statements, however; attempt to create the sp2005GetNonDocumentIssues procedure twice which raises the error that it already exists.
This is not a typical situation and is not real problem for what I am trying to do but as a programmer, I always appreciate when users take the time to report behavior that does not seem proper. Sometimes, it turns out to be a bug.
Your products are top notch!
Anyway, the second database has neither of the above mentioned stored procedures. The script generated by SQL Compare to make the second database the same as the first has the following pertinent lines:
PRINT N'Creating [dbo].[sp2005GetNonDocumentIssues]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[sp2005GetNonDocumentIssues]
<..... the code for the procedure goes in here ...>
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[sp2005GetNonDocumentIssues_old]'
GO
CREATE PROCEDURE [dbo].[sp2005GetNonDocumentIssues]
<..... the code for the procedure goes in here ...>
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
Notice that the comments correctly indicate that the sp2005GetNonDocumentIssues procedure is created first and then the sp2005GetNonDocumentIssues_old procedure is created next. The actual TSQL statements, however; attempt to create the sp2005GetNonDocumentIssues procedure twice which raises the error that it already exists.
This is not a typical situation and is not real problem for what I am trying to do but as a programmer, I always appreciate when users take the time to report behavior that does not seem proper. Sometimes, it turns out to be a bug.
Your products are top notch!
Comments
Yes, this is a recurring issue caused by the fact that SQL Compare goes strictly by the object definition (in the syscomments table). When objects are renamed in Enterprise Manager, the associated DDL is not updated.