Migration script fails to set DB rev, modifies SP instead

• 
  In version control the migration script ends with a batch terminator and a blank line.
  

  ...
         INNER JOIN Customer.tbCustomer c ON cp.FK_CustomerID = c.PK_CustomerID
      
  END
  GO
  
  

  
  It looks like SQL Source Control strips the batch terminator before appending the non-schema statements when it prepares the sync script.

  isme November 28, 2013 05:58

  - 0 +
• 
  Hi Iain,
  
  I've just had a look at this myself. I do see the same behaviour as you (I think!) - the migration script in the repo has a terminating 'GO' but when we execute the script on a Get Latest, the GO seems to be omitted. Interestingly, in my case, this didn't stop the extended properties from getting updated so I'm not sure that the cause of that is necessarily the same.
  
  In any case, I'll log a bug for that, although I'm not sure as to the likelihood of it getting resolved. This is because we're re-working the whole migrations function which will work completely differently (see here for details)

  James B December 03, 2013 10:55

  - 0 +
• 

  james.billings wrote:

  in my case, this didn't stop the extended properties from getting updated so I'm not sure that the cause of that is necessarily the same.

  
  What was the type of the last object in your sync script?
  
  I can reproduce your behavior if the last object is a table.
  

  CREATE TABLE x (y INT);
  
  DECLARE @RG_SC_VERSION BIGINT
  SET @RG_SC_VERSION = 123
  IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
    EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
  EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
  GO 
  

  
  The intention of this batch is to create table x and set the version property to 123.
  
  The batch separator is missing, but it behaves as you expect.
  
  It's not possible for the non-schema statements to be part of the table definition, so SQL Server creates the table then applies the extended property.
  
  You can see that it worked when you query the database metadata.
  

  /*------------------------
  SELECT name, create_date FROM sys.objects WHERE name = N'x';
  
  SELECT name, value FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL)
  ------------------------*/
  
  name                                create_date
  ----------------------------------- -----------------------
  x                                   2013-12-04 11:41:48.390
  
  
  name                                value
  ----------------------------------- ------------------------
  SQLSourceControl Database Revision  123
  

  
  One table was created, and the extended property was updated. Perfect!
  
  But there's a problem when the last object is a stored procedure.
  

  CREATE PROCEDURE y
  AS
  BEGIN
    SELECT 1;
  END;
  
  DECLARE @RG_SC_VERSION BIGINT
  SET @RG_SC_VERSION = 456
  IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
    EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
  EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
  GO 
  

  
  This batch looks like it creates stored procedure y and sets the version property to 456. Instead it creates a stored procedure that contains the code to set the property.
  
  The missing batch separator makes an important difference. A stored procedure definition begins immediately after the AS and continues to the end of the batch. The BEGIN...END is just syntactic sugar; SQL Server ignores it.
  
  You can see something is wrong when you query the metadata.
  

  /*------------------------
  SELECT name, create_date FROM sys.objects WHERE name = N'y';
  
  SELECT name, value FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL)
  ------------------------*/
  
  name                                create_date
  ----------------------------------- ------------------------
  y                                   2013-12-04 12:08:45.607
  
  
  name                                value
  ----------------------------------- ------------------------
  SQLSourceControl Database Revision  123
  

  
  One procedure was created, but the extended property was not updated. Uh-oh!
  
  You can see the code we meant to execute is inside the stored procedure when you print its definition.
  

  /*------------------------
  DECLARE @definition NVARCHAR(MAX) = (SELECT [definition] FROM sys.sql_modules WHERE OBJECT_NAME([object_id]) = N'y');
  PRINT @definition;
  ------------------------*/
  
  CREATE PROCEDURE y
  AS
  BEGIN
    SELECT 1;
  END;
  
  DECLARE @RG_SC_VERSION BIGINT
  SET @RG_SC_VERSION = 456
  IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
    EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
  EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
  

  
  If the last object is a table, then there is no problem with the sync script.
  
  If the last object is a procedure, then the object definition is mangled, and the version property is not updated.
  
  Is that enough information for you to reproduce the issue and avoid it in Migrations V2?
  
  Looking forward to the new version!

  isme December 04, 2013 07:27

  - 0 +
• 
  Ah, makes more sense, I think in my quick test the subsequent object was indeed a table.
  I did definitely observe the missing separator though. I'll let the team have a look over this thread. I think with the way V2 works (albeit my limited knowledge at this point!) it shouldn't be an issue either way, but they can verify that.

  James B December 04, 2013 07:32

  - 0 +
• 
  I had the same thing just happen to me except it was when the last (and only) object was a function. I was able to work around it by adding a bogus statement after the alter function part:
  
  

  -- this was the END from the alter function section
  END
  
  -- this was getting omitted.
  GO
  
  -- non-update to a random table 
  UPDATE dbo.SearchParameter
  SET DisplayName = DisplayName
  WHERE 1 = 0;
  
  -- this GO still got omitted but for some reason things worked.
  GO
  

  freecell1 August 21, 2014 12:04

  - 0 +
• 
  Hi,
  
  Since Migrations v2 has been deprecated in SQL Source Control 5 the bug report that was created as a result of this post is now closed.
  
  Thank you,

  Sergio R February 17, 2017 09:57

  - 0 +

Add comment

Please sign in to leave a comment.