locked
sqlpackage.exe / script action / sqlcmd vars mix up comparrisson RRS feed

  • Question

  • I am trying to create a sql script to update a database in production. As I don't have access to the production environment and sqlpackage.exe is not available in production, I use the original dacpac of the version deployed to production as a reference when creating the update script.

    The database uses some views that query linked servers. Those views haven't changed so I don't expect them to be mentioned in the update script. But they are.

    I reproduced the behavior by creating a ssdt project with a view accessing a linked server and applied the following script to the build output:

    :: make sure we find sqlpackage.exe

    path C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120;%path%

    :: create the target dacpac as a copy of the original dacpac copy -y demoDB.dacpac demoDB_prev_version.dacpac /y

    :: generate the update script sqlpackage.exe /action:script ^ /outputpath:Update_demoDB.sql ^ /sourceFile:demoDB.dacpac ^ /targetFile:demoDB_prev_version.dacpac ^ /targetDatabaseName:i_dont_know_why_i_must_set_this ^ /v:linkedDB=linkedDB ^ /v:OtherServer=localhost ^ /p:BackupDatabaseBeforeChanges=False ^ /p:BlockOnPossibleDataLoss=True ^ /p:CommentOutSetVarDeclarations=True ^ /p:IgnoreFileAndLogFilePath=True ^ /p:IgnoreFilegroupPlacement=True

    This results in a file Update_demoDB.sql that contains the following lines (removed the other lines for prevety):

    GO
    PRINT N'Altering [dbo].[SomeViewQueryingTheLinkedServer]...';
    
    
    GO
    ALTER VIEW [dbo].[SomeViewQueryingTheLinkedServer]
    	AS SELECT * FROM [$(OtherServer)].[$(linkedDB)].dbo.SomeTable
    

    It seems obvious that this is not what one would expect.

    Looks to me as if the variable values supplied to sqlpackage are applied only to one of the dacpacs (probably the source) before comparing.

    Curiously when I do a schema compare in VS of those two dacpacs I see no differences. Of course I can't provide any variable values for the schema compare. Of course I must set these values when using sqlpackage.exe.

    Seems like a bug to me. 


    ralf


    • Edited by ralfkret Tuesday, May 12, 2015 1:55 PM formatting error
    Tuesday, May 12, 2015 1:54 PM

All replies

  • I'm getting the same results in the schema compare for objects that are using 3 or 4 part names with sqlcmd variables.

    For example:  [$(LinkServerName)].[$(DatabaseName)].[SchemaName].[ObjectName]

    or                  [$(DatabaseName)].[SchemaName].[ObjectName]

    This occurs even while setting both the Source/Target files to the same .dacpac file for an object that is referencing an external database.  I don't see any SqlPackage.exe Script property that would control this behavior.

    Also like Ralfkret, when I manually perform the schema compare within Visual Studio, it shows no differences

    To reproduce the issue I performed the following:

    • In Visual Studio 2013 with Update 5, create a new SQL Server Project "Database1".
    • Add a new table "Table1" to the project and accept the default script.
    • Add another project "Database2" to the solution.
    • In project "Database2", add a database reference to "Database1".  Make sure to select "Different Database, same server and accept the defaults as shown here:
    • In project "Database2", add a new view "View1" and add the following SELECT statement.
    • Compile the application.
    • Open the Visual Studio 2013 Developer command prompt
    • Make sure that you add the SqlPackage.exe to the path variable.  As I have SQL Server 2014 installed, I've added this path:  "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin"
    • Execute the following SqlPackage.exe command to compare the Database2.dacpac with itself.
    • SqlPackage.exe /a:Script /sf:Database2.dacpac /tf:Database2.dacpac /tdn:Database2 /op:AlterScript.sql /v:Database1="Database1"
    • Review the output script "AlterScript.sql" and verify that the results of the compare are not identical and displays the following:

    There is definitely an issue with the sqlcmd variables for external database references.  As I was comparing the same .dacpac file with itself, there should have been no changes in the generated script.


    • Edited by Matthew M. Merrill Wednesday, August 5, 2015 1:49 PM Added steps to reproduce issue.
    Tuesday, August 4, 2015 10:16 PM
  • Although this is by no means a permanently solution, I do have a workaround available for this issue.

    Create a synonym for the external database objects that are being referenced in the project.  Then update each stored procedure, function, view, etc., that is referencing the sqlcmd database variable with the synonym.

    Finally in the SqlPackage.exe command add the following property: /p:ExcludeObjectsTypes=Synonyms.

    Usage of synonyms is a good practice to abstract the 3 part or 4 part naming convention and remove the hard-coded values from stored procedures and other objects.  So if the database is moved to a different server, then only the synonym needs to be updated and not all the objects that reference the external database.

    So for my example above, make the following changes.

    • Create a synonym for each object referenced in "Database1" using the sqlcmd variable.

    • Update the view in the "Database2" project to reference the synonym.

    • Recompile the application.
    • Add the "ExcludeObjectTypes" property to the SqlPackage.exe command line.

    SqlPackage.exe /a:Script /sf:Database2.dacpac /tf:Database2.dacpac /tdn:Database2 /op:AlterScript.sql /p:ExcludeObjectTypes=Synonyms /v:Database1="Database1"

    This moves all the externally referenced sqlcmd variables to the synonyms and then exclude them from the generated script.  However, this is not an ideal solution, but it works as long as you don't need to generate script changes for the synonyms.

    Microsoft needs to look into this issue further.

    Wednesday, August 5, 2015 5:08 PM
  • I'm chiming in just to confirm that I've been bitten by the same exact issue. 

    Will let you know if I find an answer.

    Cheers,
    Gianluca

    Wednesday, January 27, 2016 4:05 PM