SSDT Not Updating Database

  • We're using Visual Studio 2013 SSDT to generate deployment scripts for database changes and have found some strange behaviour.

    The project has the following;

    [ModifiedBy] CHAR (8) CONSTRAINT [DF_BankHolidays_ModifiedBy] DEFAULT (replace(upper(system_user), 'SW\', '')) NOT NULL

    the database has (to save you time - we're swapping suser_sname for system_user);

    [ModifiedBy] CHAR (8) CONSTRAINT [DF_BankHolidays_ModifiedBy] DEFAULT (replace(upper(suser_sname()),'SW\','')) NOT NULL

    When we update the target database from the source, the update is successful, but the constraint definition in the database hasn't changed. We also tried generating a publish script and using that. The script contains a drop and a create for the constraint - the create having the syntax as in the project. Again, the script runs successfully, but the constraint definition in the database is unchanged. Has anyone else seen this? Any work-around?

    We're on the latest version of SSDT for Visual Studio 2013 (12.0.50730.0).

  • Chris Wooding (9/18/2015)


    We're using Visual Studio 2013 SSDT to generate deployment scripts for database changes and have found some strange behaviour.

    The project has the following;

    [ModifiedBy] CHAR (8) CONSTRAINT [DF_BankHolidays_ModifiedBy] DEFAULT (replace(upper(system_user), 'SW\', '')) NOT NULL

    the database has (to save you time - we're swapping suser_sname for system_user);

    [ModifiedBy] CHAR (8) CONSTRAINT [DF_BankHolidays_ModifiedBy] DEFAULT (replace(upper(suser_sname()),'SW\','')) NOT NULL

    When we update the target database from the source, the update is successful, but the constraint definition in the database hasn't changed. We also tried generating a publish script and using that. The script contains a drop and a create for the constraint - the create having the syntax as in the project. Again, the script runs successfully, but the constraint definition in the database is unchanged. Has anyone else seen this? Any work-around?

    We're on the latest version of SSDT for Visual Studio 2013 (12.0.50730.0).

    That all sounds good to me. Excuse the question, but are you sure that the database you are checking is the same one as the target of the deployment? 🙂

    If you run the DROP/CREATE generated in the deployment script manually in SSMS, is the result as intended?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • VS or SSDT uses a version of the database schema to perform the comparison and not the actual database target itself. Its more of an extension of Data Tier Application, You can try a DAC package to compare schemas to rule out SSDT as the issue.

    When you ran the publish script you didnt get any errors , was there a catch all?

    Have you refreshed the database schema to check if its no just SSMS that outdated?

    Jayanth Kurup[/url]

  • Jayanth_Kurup (9/18/2015)


    VS or SSDT uses a version of the database schema to perform the comparison and not the actual database target itself. Its more of an extension of Data Tier Application, You can try a DAC package to compare schemas to rule out SSDT as the issue.

    When you ran the publish script you didnt get any errors , was there a catch all?

    Have you refreshed the database schema to check if its no just SSMS that outdated?

    If you select a database as source or target, that is what gets used. There is no hidden 'version', AFAIK. If you think I am wrong, please go into more detail.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It's not SSDT after all. As suggested, I tried copying the DROP and CREATE statements from the deployment script into an SSMS window and ran them there. No errors, but the constraint definition didn't get changed. My login is a member of the db_owner and db_ddladmin roles in that database.

    We're on 2008 R2.

  • Phil Parkin (9/18/2015)


    Jayanth_Kurup (9/18/2015)


    VS or SSDT uses a version of the database schema to perform the comparison and not the actual database target itself. Its more of an extension of Data Tier Application, You can try a DAC package to compare schemas to rule out SSDT as the issue.

    When you ran the publish script you didnt get any errors , was there a catch all?

    Have you refreshed the database schema to check if its no just SSMS that outdated?

    If you select a database as source or target, that is what gets used. There is no hidden 'version', AFAIK. If you think I am wrong, please go into more detail.

    SSDT uses a version store of the target in memory when performing the comparison. This allows this allows the schema compare to not be affected when multiple people are deploying changes to the same DEV database. Infact its a freqneut problem that happens with SSDT running out of memory when comparing large number of database objects. Its not hidden exactly its just stored in memory.

    http://stackoverflow.com/questions/26511426/db-schema-compare-error-version-store-out-of-memory

    If the schema in memory is out of sync with that of the target might not not be able to tell the difference.

    Jayanth Kurup[/url]

  • You might have a policy or trigger preventing the changes the question is why you are not altered about the same.

    Jayanth Kurup[/url]

  • Jayanth_Kurup (9/20/2015)


    Phil Parkin (9/18/2015)


    Jayanth_Kurup (9/18/2015)


    VS or SSDT uses a version of the database schema to perform the comparison and not the actual database target itself. Its more of an extension of Data Tier Application, You can try a DAC package to compare schemas to rule out SSDT as the issue.

    When you ran the publish script you didnt get any errors , was there a catch all?

    Have you refreshed the database schema to check if its no just SSMS that outdated?

    If you select a database as source or target, that is what gets used. There is no hidden 'version', AFAIK. If you think I am wrong, please go into more detail.

    SSDT uses a version store of the target in memory when performing the comparison. This allows this allows the schema compare to not be affected when multiple people are deploying changes to the same DEV database. Infact its a freqneut problem that happens with SSDT running out of memory when comparing large number of database objects. Its not hidden exactly its just stored in memory.

    http://stackoverflow.com/questions/26511426/db-schema-compare-error-version-store-out-of-memory

    If the schema in memory is out of sync with that of the target might not not be able to tell the difference.

    I don't have any links to back this up, but I think you are misrepresenting what actually happens.

    At the point when the schema compare is run, SQL Server builds the 'version store' (whatever that really is) from the target database.

    As for your 'not affected' comment, it's simply not true. If there is any drift in the target database such that its schema is different from the SSDT version store, choosing the 'update target' option results in an error message being displayed: 'Drift detected in target database, please rerun comparison', or something similar to that effect. You cannot update the target db if drift is detected.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • There is no trigger that would prevent this change. Another constraint change to the same table worked fine.

  • Phil Parkin (9/20/2015)


    Jayanth_Kurup (9/20/2015)


    Phil Parkin (9/18/2015)


    Jayanth_Kurup (9/18/2015)


    VS or SSDT uses a version of the database schema to perform the comparison and not the actual database target itself. Its more of an extension of Data Tier Application, You can try a DAC package to compare schemas to rule out SSDT as the issue.

    When you ran the publish script you didnt get any errors , was there a catch all?

    Have you refreshed the database schema to check if its no just SSMS that outdated?

    If you select a database as source or target, that is what gets used. There is no hidden 'version', AFAIK. If you think I am wrong, please go into more detail.

    SSDT uses a version store of the target in memory when performing the comparison. This allows this allows the schema compare to not be affected when multiple people are deploying changes to the same DEV database. Infact its a freqneut problem that happens with SSDT running out of memory when comparing large number of database objects. Its not hidden exactly its just stored in memory.

    http://stackoverflow.com/questions/26511426/db-schema-compare-error-version-store-out-of-memory

    If the schema in memory is out of sync with that of the target might not not be able to tell the difference.

    I don't have any links to back this up, but I think you are misrepresenting what actually happens.

    At the point when the schema compare is run, SQL Server builds the 'version store' (whatever that really is) from the target database.

    As for your 'not affected' comment, it's simply not true. If there is any drift in the target database such that its schema is different from the SSDT version store, choosing the 'update target' option results in an error message being displayed: 'Drift detected in target database, please rerun comparison', or something similar to that effect. You cannot update the target db if drift is detected.

    Sorry i should have been more clear , with not affected I mean the schema is not affected at the time of comparison , not deployment. Essentially

    User 1 @ 0 sec fetchs a version of the target and places it in memory

    User 2 @ 1 sec Makes a change to target db

    User 1 @ 3 sec performs comparison with in memory schema version - not affected at this point

    User 1 @ 4 sec tries to deploy - change (target schema drift detected) -- not its affected and requries a comparison to done again.

    User 1 @ 5 sec performs comparison - and detects the change

    User 1 @ 6 sec deploys all changes.

    In this case it is still possible to see a mismatch between target and SSDt schema at 3 sec , which is rectified at 5 sec. I was wondering if the OP was seeing the behaviour at step 3.

    Jayanth Kurup[/url]

  • Sorry i should have been more clear , with not affected I mean the schema is not affected at the time of comparison , not deployment. Essentially

    User 1 @ 0 sec fetchs a version of the target and places it in memory

    User 2 @ 1 sec Makes a change to target db

    User 1 @ 3 sec performs comparison with in memory schema version - not affected at this point

    User 1 @ 4 sec tries to deploy - change (target schema drift detected) -- not its affected and requries a comparison to done again.

    User 1 @ 5 sec performs comparison - and detects the change

    User 1 @ 6 sec deploys all changes.

    In this case it is still possible to see a mismatch between target and SSDt schema at 3 sec , which is rectified at 5 sec. I was wondering if the OP was seeing the behaviour at step 3.

    OK, I see what you are getting at.

    Can you explain the action in SSDT that user 1 has to take to 'fetch a version of the target and place it in memory' please?

    Because all I know about is clicking on the 'compare' button, which does the fetch and the comparison in a single hit.

    Apologies to the OP for wandering slightly off topic.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • No apology required; it's always worth understanding what the tools are doing for us. As noted in a previous post, the problem I have is also occurring outside SSDT, so it's due to some other factor. I'm an a course most of this week and therefore there may be a delay before I am able to try out any other suggestions.

    To rule out any hidden rollbacks or silent failures, I tried the following in SSMS; 1) drop the existing constraint, 2) refresh the database - the constraint no-longer shows against the table, 3) add the constraint, 4) refresh the database. After step 4, the constraint is present once again, but with system_user replaced by suser_sname().

  • Yes, we've noticed that it frequently puts superfluous brackets around the default expression in the script so that the resulting constraint in the database doesn't match the project and gets detected as a difference each time.

  • Chris Wooding (9/23/2015)


    Yes, we've noticed that it frequently puts superfluous brackets around the default expression in the script so that the resulting constraint in the database doesn't match the project and gets detected as a difference each time.

    The schema compare tool certainly has its idiosyncrasies. It frequently used to identify differences where there were none. I once even used a hex editor to look for differences. Still nothing.

    Also, when updating the target (database --> project) it will often add in a few 'create schema' files, just for a laugh. Even though

    a) They did not appear in the graphical schema comparison window before hitting update, and

    b) There was no difference between source and target (apart from some trailing white space ('ignore whitespace' was selected, BTW))

    The icing on the cake? Create a deployment script and examine it: there is nothing to do with schemas on it. That's a whole load of superfluous fluff, for no apparent reason other than to waste people's time.

    But I still love SSDT 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply