Go to Main page..

DACPAK and adding in the table a column UNIQUE NOT NULL , Microsoft SQL Server





DACPAK and adding a UNIQUE NOT NULL column to a table [new]
In the Visual Studio created the project "Database" in which he described the structure of the database, in particular the table
 ( table (
 id ] INT NOT NULL , [ id ] INT NOT NULL , 
 value ] NVARCHAR ( 255 ) NOT NULL , [ value ] NVARCHAR ( 255 ) NOT NULL , 
 KEY ([ id ] ASC ), PRIMARY KEY ([ id ] ASC ), 
 ([ name ] ASC ) UNIQUE ([ name ] ASC )
 );
Collected the DACPAC file and deployed it to the clients. Everything was good, until the decision came “from above” to add to this table another unique non-empty field value2 with values ​​[value] + '' + CAST ([id] as nvarchar)
And here I had a stupor: Make it a script, of course, does not cost anything, but how to do it kosher using MSVS?
I tried to use predeploy or postdeploy scripts, get either an error, such as field [value2] is defined twice, or in the description script I see the field value2 nvarchar (max) NULL, which is already filled in the post-deployment script and NOT NULL UNIQUE, which in my opinion - not very well.
People, tell me, please, how to solve the problem correctly?
And the bugs were crawling and brazenly moving their whiskers ... (c) Not mine.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan
I tried to use predeploy or postdeploy scripts, get either an error, such as field [value2] is defined twice, or in the description script I see the field value2 nvarchar (max) NULL, which is already filled in the post-deployment script and NOT NULL UNIQUE, which in my opinion - not very well.
People, tell me, please, how to solve the problem correctly?
Why is "not entirely successful"?
Manually, by the script, you would do the same.
It is only strange that nvarchar ( max ), this type cannot be made unique. You must have been sealed?
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
alexeyvg
It is only strange that nvarchar ( max ), this type cannot be made unique. You must have been sealed?
Where is NVARCHAR (MAX)? I see NVARCHAR (255).
And I do not see the field [name], which made UNIQUE
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
iap
Where is NVARCHAR (MAX)?
ABOUT! In the text I found value2 nvarchar (max).
Join alexeyvg
December 20, 18, 09:39 [ 21769171 ] Reply | Quote Report to moderator
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
alexeyvg, Yes. A typo. NVARCHAR (255) field [value], instead of [name] While writing the question, I was distracted to another base, so the errors went. I apologize.
 TABLE [ table ] ( CREATE TABLE [ table ] (
 id ] INT NOT NULL , [ id ] INT NOT NULL , 
 value ] NVARCHAR ( 255 ) NOT NULL , [ value ] NVARCHAR ( 255 ) NOT NULL , 
 KEY ([ id ] ASC ), PRIMARY KEY ([ id ] ASC ), 
 ([ value ] ASC ) UNIQUE ([ value ] ASC )
 );
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
alexeyvg
Why is "not entirely successful"?

Because in the original table creation script, the [value2] field will be NULL and NOT UNIQUE. What makes documenting difficult.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Another example of why "not entirely successful" refers to the subsequent updating of the structure. The updated structure will contain the [value2] NOT NULL UNIQUE field, the structure update will initially return it to NULL and NOT UNIQUE, and then the post-installation script will return it to NOT NULL and UNIQUE again. And can this masturbation be somehow banned? For example, to check that if there is a field, then not to change it?
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan
Another example of why "not entirely successful" refers to the subsequent updating of the structure. The updated structure will contain the [value2] NOT NULL UNIQUE field, the structure update will initially return it to NULL and NOT UNIQUE, and then the post-installation script will return it to NOT NULL and UNIQUE again. And can this masturbation be somehow banned? For example, to check that if there is a field, then not to change it?
Is that what this is all about? What returns where and why?
Do not you write the next update script yourself?
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan,

so set the default value to [value] + '' + CAST ([id] as nvarchar) for the new column.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
iap, the use of DACPAC implies that it contains a description of the database structure, and the update process itself builds the scripts that will update the client database to the current one. As far as I could understand this mechanism, I can describe two scripts that will be executed either before deployment or after deployment. However, my update scripts are built before the execution of the PREDEPLOY script, so if I add a field in this script, then the deployment will fail with the error "There can not be two fields with the same name", and if I add a field change to the POSTDEPLOY script, then the main script first " will turn the "field to NULL NOT UNIQUE, and then the POSTDEPLOY script will return the attributes NOT NULL UNIQUE to this field.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Vladislav Kolosov, tried, does not roll. Message: "SQL70536: Name" [value] "is forbidden in this context. Valid expressions include constants, constant expressions, and (in some contexts) variables. Column names are not allowed"
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan
And here I had a stupor: Make it a script, of course, does not cost anything, but how to do it kosher using MSVS?
For convenience you have to pay. Juggling crutches is normal.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan,

on 2 "deploy" break, all business ...
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan
alexeyvg
Why is "not entirely successful"?

Because in the original table creation script, the [value2] field will be NULL and NOT UNIQUE. What makes documenting difficult.
In the next version, do NOT NULL and UNIQUE. I hope this is what he will digest?
Although inconvenient, of course, crutches and all changes from other developers will ruin production. So in general, this option disappears.

I honestly thought that DACPAC will not add a field if it already exists.

And I did not expect that the operation to add a field seems to be the developers of Visual Studio with such an incredibly unique operation that they did not. Or maybe they did, but we do not know here what tick in the properties to click?

I did not use this technology when it appeared, I thought that if I finished it (which is unlikely for the epoch of the “after Bill” epoch, but it would be possible in a siqueline team), then I will.
But alas, not finished.

Tek that in a student lab - Wellcome, do not use in professional work.
Use just the base project, make all changes with scripts saved in the same project, from time to time use Compare, to compare the project and the base.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Critic, great idea.
But I can’t figure out what to write in the first section, but what about the second? Can you tell me?
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
alexeyvg, I have several clients who update their software whenever they want. Many of these customers from the "Who's there? Sit down, I'll open it myself." Therefore, I would not like them to do "complex" instructions with sequential rolling packages. But apparently, there is no other option.
DACPAC does not add a second field if it existed prior to the deployment. But if the deployment began, it first builds the update script based on what it is now, then it executes the predeploy script. And if in this script there is a code that added this field to the table, then the main dakpack code will crash with an error. From here an output: the predeploy script to be taken out of limits of dacpac. Those. first execute it, and then deploy dacpac.
Re: DACPAK and adding in the table a column UNIQUE NOT NULL [new]
Harlan
DACPAC does not add a second field if it existed prior to the deployment. But if the deployment began, it first builds the update script based on what it is now, then it executes the predeploy script. And if in this script there is a code that added this field to the table, then the main dakpack code will crash with an error. From here an output: the predeploy script to be taken out of limits of dacpac. Those. first execute it, and then deploy dacpac.
That's how, thank you.

Harlan
alexeyvg, I have several clients who update their software whenever they want. Many of these customers from the "Who's there? Sit down, I'll open it myself." Therefore, I would not like them to do "complex" instructions with sequential rolling packages. But apparently, there is no other option.
Another option is not to use DACPAC :-)



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ