none
SQLPackage.exe - Comparing two DacPacs produces unexpected output when using /Action:Script RRS feed

  • Question

  • I have two DacPacs created from different versions of my database project (SSDT). When I try to generate a script between the two versions via SQLPackage.exe, I'm getting a couple of stored procedures which were not changed between versions being added to the script as an ALTER.  Is there anyway to determine why this is happening or a more detailed log?  I compared the model.xml files and don't see any reason for the stored procedures to be added to the script.

    I tried with SQLPackage.exe 14.0 and 15.0 with same results

    --Source

    <Element Type="SqlProcedure" Name="[dbo].[populate_Users]">
       <Property Name="BodyScript">
        <Value><![CDATA[

    SET NOCOUNT ON

    --Reference Data
    MERGE INTO [Users] AS Target
    USING (VALUES
       ('x        ', 'xx', '0', 'True', NULL, 'True', GETDATE(), NULL, NULL)
      ,('y        ', 'yy', '0', 'True', NULL, 'True', GETDATE(), NULL, LTRIM(RTRIM('$(PSServiceAcct)')))
      ,('VPM-AUTOMATION', 'VPM Automation', '0', 'True', NULL, 'True', GETDATE(), NULL, LTRIM(RTRIM('$(AutomationServiceAcct)')))

    ) AS Source ([Login], [PseudoLogin], [LastUserId], [Active], [EnttId], [System], [LastDateTime], [ExtRefCode], [WindowsLogin])
    ON (Target.[Login] = Source.[Login])
    --Update matched rows
    WHEN MATCHED AND SOURCE.[PseudoLogin] <> '' AND (Target.[PseudoLogin] <> Source.[PseudoLogin] OR (ISNULL(Target.[WindowsLogin], '') <> LTRIM(RTRIM(ISNULL(Source.[WindowsLogin], ''))) AND LTRIM(RTRIM(SOURCE.[WindowsLogin])) <> '') AND LTRIM(RTRIM(SOURCE.[WindowsLogin])) <> 'XX') THEN
           UPDATE SET [PseudoLogin] = Source.[PseudoLogin]
                     ,[WindowsLogin] = Source.[WindowsLogin]
    --Inserts new rows
    WHEN NOT MATCHED BY TARGET THEN
                  INSERT([Login], [PseudoLogin], [LastUserId], [Active], [EnttId], [System], [LastDateTime], [ExtRefCode], [WindowsLogin])
                  VALUES(Source.[Login], Source.[PseudoLogin], Source.[LastUserId], Source.[Active], Source.[EnttId], Source.[System], Source.[LastDateTime], Source.[ExtRefCode], Source.[WindowsLogin])
    ;
    --Verify rows affected
    DECLARE @mergeError int, @mergeCount int
    SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
    IF @mergeError != 0
                  BEGIN
                  PRINT 'ERROR OCCURRED IN MERGE FOR [Users]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
                  END
    ELSE
                  BEGIN
                  PRINT '[Users] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
                  END

    SET NOCOUNT OFF

    RETURN 0
    ;]]></Value>
       </Property>
       <Property Name="IsAnsiNullsOn" Value="True" />
       <Relationship Name="BodyDependencies">
        <Entry>
         <References ExternalSource="BuiltIns" Name="[int]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[int]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[Login]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[PseudoLogin]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[WindowsLogin]" />
        </Entry>
        <Entry />
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[PseudoLogin]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[WindowsLogin]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[Login]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[LastUserId]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[Active]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[EnttId]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[System]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[LastDateTime]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[ExtRefCode]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[varchar]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[varchar]" />
        </Entry>
       </Relationship>
       <Relationship Name="Schema">
        <Entry>
         <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
       </Relationship>
       <Annotation Type="SysCommentsObjectAnnotation">
        <Property Name="Length" Value="3291" />
        <Property Name="StartLine" Value="1" />
        <Property Name="StartColumn" Value="1" />
        <Property Name="HeaderContents" Value="CREATE PROCEDURE [dbo].[populate_Users]&#xD;&#xA;&#xD;&#xA;/**************************************************************************************&#xD;&#xA;VPM STORED PROCEDURE&#xD;&#xA;***************************************************************************************/&#xD;&#xA;/*&#xD;&#xA;############################################################################################&#xD;&#xA;&#xD;&#xA;      The use of this Software is subject to the terms of an agreement with Company Inc., &#xD;&#xA;      and is subject to confidentiality, use and license restrictions thereunder.  &#xD;&#xA;      You may not disclose, copy, modify or create derivative works of the software except &#xD;&#xA;      as may be set forth in the agreement.&#xD;&#xA;      &#xD;&#xA;############################################################################################&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;**************************************************************************************************&#xD;&#xA;Stored Procedure Description: &#xD;&#xA;This will populate the table as part of the normal database schema build and the execution of the procedure &#xD;&#xA;will occur from the project post-deployment script. This is to take advantage of providing additional build &#xD;&#xA;validation for the data script – for example, if a column populated by the script is deleted from the table &#xD;&#xA;an error will be reported.&#xD;&#xA;&#xD;&#xA;***************************************************************************************************&#xD;&#xA;*/&#xD;&#xA;&#xD;&#xA;AS" />
       </Annotation>
      </Element>

    --Destination

    <Element Type="SqlProcedure" Name="[dbo].[populate_Users]">
       <Property Name="BodyScript">
        <Value><![CDATA[

    SET NOCOUNT ON

    --Reference Data
    MERGE INTO [Users] AS Target
    USING (VALUES
       ('x        ', 'xx', '0', 'True', NULL, 'True', GETDATE(), NULL, NULL)
      ,('y        ', 'yy', '0', 'True', NULL, 'True', GETDATE(), NULL, LTRIM(RTRIM('$(PSServiceAcct)')))
      ,('z', 'zz', '0', 'True', NULL, 'True', GETDATE(), NULL, LTRIM(RTRIM('$(AutomationServiceAcct)')))

    ) AS Source ([Login], [PseudoLogin], [LastUserId], [Active], [EnttId], [System], [LastDateTime], [ExtRefCode], [WindowsLogin])
    ON (Target.[Login] = Source.[Login])
    --Update matched rows
    WHEN MATCHED AND SOURCE.[PseudoLogin] <> '' AND (Target.[PseudoLogin] <> Source.[PseudoLogin] OR (ISNULL(Target.[WindowsLogin], '') <> LTRIM(RTRIM(ISNULL(Source.[WindowsLogin], ''))) AND LTRIM(RTRIM(SOURCE.[WindowsLogin])) <> '') AND LTRIM(RTRIM(SOURCE.[WindowsLogin])) <> 'XX') THEN
           UPDATE SET [PseudoLogin] = Source.[PseudoLogin]
                     ,[WindowsLogin] = Source.[WindowsLogin]
    --Inserts new rows
    WHEN NOT MATCHED BY TARGET THEN
                  INSERT([Login], [PseudoLogin], [LastUserId], [Active], [EnttId], [System], [LastDateTime], [ExtRefCode], [WindowsLogin])
                  VALUES(Source.[Login], Source.[PseudoLogin], Source.[LastUserId], Source.[Active], Source.[EnttId], Source.[System], Source.[LastDateTime], Source.[ExtRefCode], Source.[WindowsLogin])
    ;
    --Verify rows affected
    DECLARE @mergeError int, @mergeCount int
    SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
    IF @mergeError != 0
                  BEGIN
                  PRINT 'ERROR OCCURRED IN MERGE FOR [Users]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
                  END
    ELSE
                  BEGIN
                  PRINT '[Users] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
                  END

    SET NOCOUNT OFF

    RETURN 0
    ;]]></Value>
       </Property>
       <Property Name="IsAnsiNullsOn" Value="True" />
       <Relationship Name="BodyDependencies">
        <Entry>
         <References ExternalSource="BuiltIns" Name="[int]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[int]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[Login]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[PseudoLogin]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[WindowsLogin]" />
        </Entry>
        <Entry />
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[PseudoLogin]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[WindowsLogin]" />
        </Entry>
        <Entry />
        <Entry>
         <References Name="[dbo].[Users].[Login]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[LastUserId]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[Active]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[EnttId]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[System]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[LastDateTime]" />
        </Entry>
        <Entry>
         <References Name="[dbo].[Users].[ExtRefCode]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[varchar]" />
        </Entry>
        <Entry>
         <References ExternalSource="BuiltIns" Name="[varchar]" />
        </Entry>
       </Relationship>
       <Relationship Name="Schema">
        <Entry>
         <References ExternalSource="BuiltIns" Name="[dbo]" />
        </Entry>
       </Relationship>
       <Annotation Type="SysCommentsObjectAnnotation">
        <Property Name="Length" Value="3291" />
        <Property Name="StartLine" Value="1" />
        <Property Name="StartColumn" Value="1" />
        <Property Name="HeaderContents" Value="CREATE PROCEDURE [dbo].[populate_Users]&#xD;&#xA;&#xD;&#xA;/**************************************************************************************&#xD;&#xA;VPM STORED PROCEDURE&#xD;&#xA;***************************************************************************************/&#xD;&#xA;/*&#xD;&#xA;############################################################################################&#xD;&#xA;&#xD;&#xA;      The use of this Software is subject to the terms of an agreement with Company Inc., &#xD;&#xA;      and is subject to confidentiality, use and license restrictions thereunder.  &#xD;&#xA;      You may not disclose, copy, modify or create derivative works of the software except &#xD;&#xA;      as may be set forth in the agreement.&#xD;&#xA;      &#xD;&#xA;############################################################################################&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;**************************************************************************************************&#xD;&#xA;Stored Procedure Description: &#xD;&#xA;This will populate the table as part of the normal database schema build and the execution of the procedure &#xD;&#xA;will occur from the project post-deployment script. This is to take advantage of providing additional build &#xD;&#xA;validation for the data script – for example, if a column populated by the script is deleted from the table &#xD;&#xA;an error will be reported.&#xD;&#xA;&#xD;&#xA;***************************************************************************************************&#xD;&#xA;*/&#xD;&#xA;&#xD;&#xA;AS" />
       </Annotation>
      </Element>



    • Edited by RKD203 Tuesday, July 9, 2019 7:29 PM
    Tuesday, July 9, 2019 7:27 PM