Stored Procedure Compare

Has anyone seen this:

I have about 30 stored procedures that continually keep showing up as out of sync with my source database. When lookinf at the compare windows (source and target), the Schema Compare engine is putting in a bunch of white space in the target stored procedure script and deeeming that as not being the same as the target.

If I script the same stored procedures in SSMS - or any other SQL editor for that matter - the two scripts are identical...no extra white space, anything...

Whenever I choose to "Writre Updates", nothing happens - the stored procedure never "syncs" up. I'll include an example. Here is the source stored procedure:

Code Snippet

-- Stored procedure

-- =============================================

-- Author:Scott Clements

-- Create date: 07/16/2007

-- Description: Account SELECT

-- =============================================

CREATE PROCEDURE[dbo].[p_Account_Select]

@AccountIdInt=Null

BEGIN

-- Set System Options

SET NOCOUNT ON

SETANSI_NULLSOFF

SETANSI_WARNINGSOFF

DECLARE@AcctIdVarChar(10)

-- Populate Character values for possible error reporting

SELECT@AcctId =Convert(VarChar(10),@AccountId)

IF NOT EXISTS(SELECT*

FROMAccount (NOLOCK)

WHEREAccountId = @AccountId)AND@AccountIdIS NOT NULL

BEGIN

RAISERROR(50334, 11, 1, 50334, @AcctId)

Return-1

END

SELECT

AC.AccountId

, AC.AcctTypeEnumItemId

, AC.Description

, AC.AccountProviderTypeEnumItemId

, AC.ExternalAccountId

, AC.RoutingInfo

, AC.SeedPaymentId

, AC.CreateDate

, AC.CreateId

, AC.ModifiedDate

, AC.ModifiedId

,Count(DISTINCTD.DistributionId)ASNumberOfDistributions

,Count(P.PaymentId)ASNumberOfPayments

FROMAccount AC (NOLOCK)

LEFT OUTER JOINDistribution DOND.AccountId = AC.AccountId

LEFT OUTER JOINPayment POND.DistributionId = P.DistributionId

WHEREAC.AccountId =CASE WHEN@AccountIdIS NULL THEN

AC.AccountId

ELSE@AccountIdEND

GROUP BY

AC.AccountId

, AC.AcctTypeEnumItemId

, AC.Description

, AC.AccountProviderTypeEnumItemId

, AC.ExternalAccountId

, AC.RoutingInfo

, AC.SeedPaymentId

, AC.CreateDate

, AC.CreateId

, AC.ModifiedDate

, AC.ModifiedId

END

Here is the target procedure, as interpreted by the Schema Compare (right window):

Code Snippet

-- Stored procedure

-- =============================================

-- Author:Scott Clements

-- Create date: 07/16/2007

-- Description: Account SELECT

-- =============================================

CREATE PROCEDURE[dbo].[p_Account_Select]

@AccountIdInt=Null

BEGIN

-- Set System Options

SET NOCOUNT ON

SETANSI_NULLSOFF

SETANSI_WARNINGSOFF

DECLARE@AcctIdVarChar(10)

-- Populate Character values for possible error reporting

SELECT@AcctId =Convert(VarChar(10),@AccountId)

IF NOT EXISTS(SELECT*

FROMAccount (NOLOCK)

WHEREAccountId = @AccountId)AND@AccountIdIS NOT NULL

BEGIN

RAISERROR(50334, 11, 1, 50334, @AcctId)

Return-1

END

SELECT

AC.AccountId

, AC.AcctTypeEnumItemId

, AC.Description

, AC.AccountProviderTypeEnumItemId

, AC.ExternalAccountId

, AC.RoutingInfo

, AC.SeedPaymentId

, AC.CreateDate

, AC.CreateId

, AC.ModifiedDate

, AC.ModifiedId

,Count(DISTINCTD.DistributionId)ASNumberOfDistributions

,Count(P.PaymentId)ASNumberOfPayments

FROMAccount AC (NOLOCK)

LEFT OUTER JOINDistribution DOND.AccountId = AC.AccountId

LEFT OUTER JOINPayment POND.DistributionId = P.DistributionId

WHEREAC.AccountId =CASE WHEN@AccountIdIS NULL THEN

AC.AccountId

ELSE@AccountIdEND

GROUP BY

AC.AccountId

, AC.AcctTypeEnumItemId

, AC.Description

, AC.AccountProviderTypeEnumItemId

, AC.ExternalAccountId

, AC.RoutingInfo

, AC.SeedPaymentId

, AC.CreateDate

, AC.CreateId

, AC.ModifiedDate

, AC.ModifiedId

END

As you can see, there is a TON of white space VSTE DB is adding to the script...if you script these two out in SSMS, they match up precisely...

[7418 byte] By [ansonee] at [2008-1-10]

it's Archive about 'Stored Procedure Compare...'

Stored Procedure Compare

Visual Studio Team System Hot Topic

Visual Studio Team System New Topic

Visual Studio Team System

Site Classified