Red Gate forums :: View topic - Migration script fails to set DB rev, modifies SP instead
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

Migration script fails to set DB rev, modifies SP instead

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Wed Nov 27, 2013 7:22 pm    Post subject: Migration script fails to set DB rev, modifies SP instead Reply with quote

Hey guys,

Today we created and tested a custom migration script for a complex schema change.

The migration script failed to set the revision number extended property, which caused an error in a later comparison. I've already raised a private bug report about that.

The ‘Commit Changes’ tab reveals that the code to set the extended property became part of a stored procedure instead of being executed against the target.

The code was added to the last stored procedure in the update script.

Looks like the SQL Source Control didn't add a batch separator between the stored procedure definition and the non-schema statements.

Is someone from Red Gate able to reproduce this?

Can you make sure that SSC always includes a batch separator between the schema definition and the non-schema statements?

Here’s what the ‘Commit Changes’ tab shows:

Code:

CREATE PROCEDURE [Customer].[uspGetPurchases]   
AS
BEGIN 
       SET NOCOUNT ON;

       SELECT
                ProductId = p.PK_ProductID,
                ProductName = p.ProductName,
                ProductType = p.ProductType
       FROM Customer.tbProduct p


       SELECT
            PurchaseId = cp.PurchaseId,    
                APIkey,
                ProductId = cp.FK_ProductID,
                ParameterName = p.ParameterName,
                ParameterValue = pv.ParameterValue
       FROM Customer.tbCustomerProductParameter cpp
       INNER JOIN Customer.tbCustomerProduct cp ON cpp.FK_CustomerProductID = cp.PK_CustomerProductID
       INNER JOIN Customer.tbParameter p ON cpp.FK_ParameterID = p.PK_ParameterID
       INNER JOIN Customer.tbParameterValue pv ON cpp.FK_ParameterValueID = pv.PK_ParameterValueID
       INNER JOIN Customer.tbCustomer c ON cp.FK_CustomerID = c.PK_CustomerID

END
/*
End of custom script APIServices 20124 to 20130 migrating from version 20124 to version 20130
*/
/*
Start of RedGate SQL Source Control versioning database-level extended properties.
*/
DECLARE @RG_SC_VERSION BIGINT
SET @RG_SC_VERSION = 20130
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


My SQL Source Control version is 3.5.3.29.
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Thu Nov 28, 2013 11:58 am    Post subject: Reply with quote

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

Code:

...
       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.
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Dec 03, 2013 4:55 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
isme



Joined: 12 Jun 2012
Posts: 78
Location: Edinburgh

PostPosted: Wed Dec 04, 2013 1:27 pm    Post subject: Re: Reply with quote

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.

Code:

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.

Code:

/*------------------------
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.

Code:

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.

Code:

/*------------------------
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.

Code:

/*------------------------
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!
_________________
Iain Elder, Skyscanner
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Wed Dec 04, 2013 1:32 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
freecell1



Joined: 13 Mar 2012
Posts: 21

PostPosted: Thu Aug 21, 2014 5:04 pm    Post subject: Reply with quote

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:


Code:

-- 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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group