Issue at refresh / verification step

skuhn

I'm attempting to create a new SQL Automation project using an existing database that has hundreds of objects in it.  The initial step of pulling in the objects and setting a baseline works without issue.  However, when I attempt to refresh and SQL Change Automation creates the Shadow database and begins to apply stored procedure scripts, it fails on a stored procedure with an error of Incorrect syntax near 'beginset'.  Taking a look at the SQL that was produced for the stored procedure everything looks okay with the words begin and set on separate lines, not concatenated as they are in the error message. I can take the stored procedure SQL that was generated and run it against the Shadow database and it works fine.  If I add an additional line to the script between begin and set then it runs fine, but then another stored procedure will end up with the same error.  It's not every stored procedure that this occurs with but since there are hundreds of them in this database, it would be a significant task to add lines to the stored procedures to resolve this.  I suspect some type of line ending issue is at play here but I'm not sure what it is or how to address it holistically rather than having to edit most stored procedures.  Has anyone seen this issue before?  Here's the script below:

IF OBJECT_ID('[dbo].[pCatalogStatusRead]') IS NOT NULL

DROP PROCEDURE [dbo].[pCatalogStatusRead];

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

create procedure [dbo].[pCatalogStatusRead]

(

@CatalogStatusID int

)

as

begin

set nocount on

select

CatalogStatusID,

CatalogStatusCode,

CatalogStatusName

from

dbo.tCatalogStatus X

where

X.CatalogStatusID = @CatalogStatusID

end

GO

GRANT EXECUTE ON  [dbo].[pCatalogStatusRead] TO [db_executeall]

GO

skuhn

I resolved this by writing a powershell script to fix the line endings on the generated stored procedure files and then applied those changes back to my development database.  After that the Refresh / Verification step completed successfully. I'm still not certain how the stored procedures were originally deployed without a linefeed on some lines.