Synchronization Scripts Use ALTER For Temporary Tables

• 
  Hi Mike,
  
  What you have reported is rather worrying as SQL Compare should not be able to alter the contents of procs. Can you double check that the procs don't contain ALTERs already? If you are still getting this issue please can you post an example of this, ASAP?
  
  Many thanks,
  
  Jonathan

  JonathanWatts June 13, 2007 13:09

  - 0 +
• 
  Here is a snippet of the script SQLCompare wrote:
  
  PRINT N'Altering [dbo].[SellerItem_AutoArchive]'
  GO
  -- Procedure Name: SellerItem_AutoArchive
  --
  -- Description: Updates a Items to status 40 (archived).
  --
  -- Parameters: none
  --
  -- Returns: count of items archived
  --
  -- Created By: Wendy Colvin (wendyc@channeladvisor.com)
  -- Created On: 12/02/2003
  --
  -- Modified By: Wendy Colvin (wendyc@channeladvisor.com)
  -- Modified On: 03/04/2004 - do not archive Pro data to ZArchiveAuctionSetup
  -- Modified On: 03/23/2004 - to be able to archive blocked items, need to retain the fact that it was blocked so set blocked archived status = 60
  -- Modified On: 05/24/2004 - don't auto archive items that are scheduled to post
  --
  -- Modified By: Wendy Colvin (wendyc@channeladvisor.com)
  -- Modified On: 05/31/2006 - 6927 Matrix Inventory Requirements - add check for archiving of relationship items
  --
  -- Modified By: Wendy Colvin (wendyc@channeladvisor.com)
  -- Modified On: 12/07/2006 - PRO change in auto-archiving of ads/inventory
  --
  -- Modified By: Mike Wengler
  -- On: 05/30/2007
  -- Reason: Changed 3-part names to 2-part names where DB name is the current db
  ALTER PROCEDURE [dbo].[SellerItem_AutoArchive]
  --%%skip
  AS
  SET NOCOUNT ON
  SET NOCOUNT ON
  
  DECLARE
  @Err int,
  @ArchiveItemCount int,
  @ProArchiveItemCount int,
  @C2AArchiveItemCount int,
  @C2AArchiveAdCount int,
  @C2AArchiveItemCount2 int,
  @C2AArchiveAdCount2 int,
  @ArchiveTypeID int,
  @Dur money
  
  DECLARE @CurrentGMT datetime
  SET @CurrentGMT = GETUTCDATE()
  
  -- determine if this is a merchant or pro sql server instance
  SELECT TOP 1 @ArchiveTypeID = t.ArchiveTypeID, @Dur = d.Duration
  FROM C2AProfile.dbo.C2AProfile c WITH (NOLOCK)
  JOIN dbo.SellerAd ad WITH (NOLOCK) ON c.ProfileID = ad.ProfileID
  JOIN SellerStatic.dbo.ArchiveType t WITH (NOLOCK) ON t.ArchiveTypeDescription = 'Merchant Inventory'
  JOIN SellerStatic.dbo.ArchiveDefaultSetting d WITH (NOLOCK) ON d.ArchiveTypeID = t.ArchiveTypeID
  
  IF @ArchiveTypeID is null
  SELECT @ArchiveTypeID = t.ArchiveTypeID, @Dur = d.Duration
  FROM SellerStatic.dbo.ArchiveDefaultSetting d WITH (NOLOCK)
  JOIN SellerStatic.dbo.ArchiveType t WITH (NOLOCK) ON d.ArchiveTypeID = t.ArchiveTypeID
  WHERE t.ArchiveTypeDescription = 'Pro Inventory'
  
  IF ( @Dur is null )
  SELECT @Dur = max(Duration)
  FROM SellerStatic.dbo.ArchiveDefaultSetting d WITH (NOLOCK)
  JOIN SellerStatic.dbo.ArchiveType t WITH (NOLOCK) ON d.ArchiveTypeID = t.ArchiveTypeID
  WHERE t.ArchiveTypeDescription like '%Inventory'
  
  DECLARE @ProfileID int, @ClosingIntegration int, @IsPro bit, @ArchiveDateGMT datetime, @TZAdj float
  SELECT @ClosingIntegration = 0, @IsPro = 1, @ArchiveDateGMT = @CurrentGMT - @Dur, @ArchiveItemCount = 0
  
  DECLARE @ItemTbl TABLE (ItemID int, ProfileID int, ArchiveDateGMT datetime, OkToArchive bit DEFAULT (1), LastActivityGMT datetime, RelationshipInstanceID int, IsInRelationship bit)
  ALTER TABLE #AdTbl (SellerAdID int, ItemID int, ProfileID int)
  
  Note that #AdTbl is not created before this statement and it makes no sense to ALTER a temporary table when you are creating it.

  commcare June 13, 2007 13:31

  - 0 +
• 
  Mike,
  
  Does this text
  
  "ALTER TABLE #AdTbl (SellerAdID int, ItemID int, ProfileID int) "
  
  actually exist within the database objects or the object script that you are comparing when you view the object in SSMS or does it say CREATE there?
  
  If it says CREATE then can you post the creation SQL for the procs in both the databases?
  
  Sorry to be so fussy,
  
  Jonathan

  JonathanWatts June 13, 2007 13:36

  - 0 +
• 
  The proc object in the Dev database has CREATE. The proc object in the QA database has CREATE. The script made with SQLCompare from the QA database has CREATE. I ran the SQL Server object code on both Dev and QA, though I had to change CREATE PROC to ALTER PROC, and both ran successfully and still had CREATE TABLE #AdTbl

  commcare June 13, 2007 13:48

  - 0 +

Add comment

Please sign in to leave a comment.