What are the challenges you face when working across database platforms? Take the survey
Options

Object already exists error when objects have been renamed

jasonrcoxjasonrcox Posts: 4
edited March 31, 2006 9:48AM in SQL Compare Previous Versions
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&gt;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&gt;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

Sign In or Register to comment.