September 18, 2015 at 3:33 am
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).
September 18, 2015 at 3:42 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 18, 2015 at 4:01 am
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?
September 18, 2015 at 4:05 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 18, 2015 at 4:28 am
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.
September 20, 2015 at 1:47 am
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.
September 20, 2015 at 1:49 am
September 20, 2015 at 2:46 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 21, 2015 at 1:37 am
There is no trigger that would prevent this change. Another constraint change to the same table worked fine.
September 21, 2015 at 9:28 am
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.
September 21, 2015 at 10:29 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 22, 2015 at 1:25 am
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().
September 22, 2015 at 3:44 am
We have a similar problem. There is a connect ticket for it:
September 23, 2015 at 1:04 am
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.
September 23, 2015 at 1:57 am
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 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply