When I started with Thinkwise a few years ago our team was not completely using the Creation steps the way I was taught during the training.
For completeness: we usually add a column, table or domain to TW and manually add it to the db using SSMS. The problem is that it is easy to forget to add the correct constraints etc… So periodically I would delete my DEV version and rebuild the db from scratch and then compare (using Redgate SQL compare) DEV to TEST, ACC and PROD.
I'm trying to correct that but I keep running into situations that I need to manually alter the existing db.
I've started out with a newly generated db for branche A and did some development work on this branch
In branch B I've added a new column to a table and did some development that uses that column
Now I’ve merged A and B to MAIN and made a new branch from MAIN which I'll call branch C
When I load the new combined branch C I can see that the new column from branch B is existing.
But when I run Creation > Generate definition > Validate definition > Generate source code > Execute source code the new column is not being added resulting in an error when it tries to create/alter the stored procedure that uses this particular column.
I expected TW to know that the most recent build/deployed db was branch A and the current branch is a combination of A and B so it needs to alter that table and add the column.
But apparently there is something wrong in how I expected it to work :)
Obviously I can fix this because on DEV I can delete the entire db and force a complete rebuild (and manually sync the master data from PROD back into the db) or manually do the ALTER TABLE ADD COLUMN query but this isn't the correct way I suspect :p
Next would be deploying to PROD and obviously it's not an option to drop the entire db there…
For now I was planning on only deploying to DEV and use RedGate SQL Compare to deploy differences in the data model manually to the rest of the OTAP/DTAP street.
Creation doesn't add newly added column
- Hero
- 86 replies
Best answer by Anne Buit
The best way to go about the database being out of sync with the model on datatypes is to change the datatype twice, subsequently.
For example, the domain last_name in the model is nvarchar(100) but on the database is is actually nvarchar(50).
Change the datatype from the incorrect domain from nvarchar(100) to nvarchar(50), perform an upgrade (this will have no actual effect on the upgraded data and schema). Change the datatype back to nvarchar(100) and upgrade again.
The model and database are now once again in sync.
The same can be done for a missing column.
+3Hi
First of all, adding columns to the database manually instead of using the Software Factory (SF) is considered bad practice. Doing so prevents the SF from tracking changes to the model and the database, which can lead to various issues that may be difficult to resolve.
That said, it's good to hear that you're working on correcting this.
If I understand correctly, the column you’re expecting is present in the model but hasn’t been deployed to the database because it is missing from the upgrade script.
This is likely due to the source version: the version used as a base when creating the upgrade scripts may already contain the column. As a result, the Software Factory sees no need to include it in the script.
Could you please verify the source version of your database, the version you are deploying to, and the version in which the column was added?
Hope this helps.
Kind regards
Remco
- Hero
- 86 replies
Hi
First of all, adding columns to the database manually instead of using the Software Factory (SF) is considered bad practice. Doing so prevents the SF from tracking changes to the model and the database, which can lead to various issues that may be difficult to resolve.
That said, it's good to hear that you're working on correcting this.
If I understand correctly, the column you’re expecting is present in the model but hasn’t been deployed to the database because it is missing from the upgrade script.
This is likely due to the source version: the version used as a base when creating the upgrade scripts may already contain the column. As a result, the Software Factory sees no need to include it in the script.
Could you please verify the source version of your database, the version you are deploying to, and the version in which the column was added?
Hope this helps.
Kind regards
Remco
I can’t really check it anymore as I was in a hurry I just deleted the entire db and have TW rebuild it again from scratch.
I'm now following the branching and merging exactly as described in the guidelines described in the TW academy training and I'll keep a lookout to see if it no longer occurs.
- Hero
- 86 replies
I think I have a similar situation.
I've changed a domain from a varchar(100) to a varchar(5)
After running thru all steps under creation I see in the create script:
create type "station_id" from varchar(5)
go
grant references on type::"station_id" to public
go But because the db already exists the create isn't executed.
When I look in SSMS I see that the datatype is still a varchar(100):
Not sure what I’m doing wrong.
- Hero
- 86 replies
Do you run creation based off a version, the database version or a moment in time?
+5The best way to go about the database being out of sync with the model on datatypes is to change the datatype twice, subsequently.
For example, the domain last_name in the model is nvarchar(100) but on the database is is actually nvarchar(50).
Change the datatype from the incorrect domain from nvarchar(100) to nvarchar(50), perform an upgrade (this will have no actual effect on the upgraded data and schema). Change the datatype back to nvarchar(100) and upgrade again.
The model and database are now once again in sync.
The same can be done for a missing column.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.