User profile for way0utwest

Sorry, this process sometimes is a bit of a chicken and egg, going around in circles when trying to explain the process. In case someone else is following, here's what I recommend. Let's say I have a procedure that I wanted created called dbo.SetDateforClass. This looks like this in my programmable object: CREATE OR ALTER dbo.SetDateforClass<br>  @ClassID int<br>, @dt date<br>as <br>begin<br>update Class<br> set StartDate = @DTs <br>where ClassId = @ClassID<br>end I add this to my database and then add a migration script that looks like this. <br>exec dbo.SetDateforClass 1, getdate()<br>exec dbo.SetDateforClass 2, getdate() Now, when I refresh the project, this compares to the shadow database. The migration scripts execute first, then the programmable objects. In this case, I'll get an error as the proc doesn't exist in Shadow yet, so the script fails. This worked in the main development database since the chronology was correct in how I built the objects. To fix this, I recommend this: change the migration script to: <br>if not exists( select name from sys.objects where name = 'SetDateforClass')<br>  begin<br>CREATE OR ALTER dbo.SetDateforClass<br>  @ClassID int<br>, @dt date<br>as <br>begin<br>update Class<br> set StartDate = @DTs <br>where ClassId = @ClassID<br>end<br>goexec dbo.SetDateforClass 1, getdate()<br>exec dbo.SetDateforClass 2, getdate() This will deploy the procedure as part of the migration script and then execute it. Later, the programmable object will overwrite this procedure with the same definition and the compare with the Shadow db for validity, as well as a build or downstream deploy, will work. If the procedure gets updated with a new parameter or different logic, that will be deployed, but this migration won't run again. However, if a new developer sets up from scratch, when they load the project and pick databases, this migration script will work (likely no data, so no effect) and then the updated procedure definition will be deployed as a part of the programmable object deployment. / comments

Sorry, this process sometimes is a bit of a chicken and egg, going around in circles when trying to explain the process. In case someone else is following, here's what I recommend.Let's say I have ...

0 votes

I'm assuming your migration script uses the procedure to do some work? I'd that right? What is say here is you can really only do one thing and thats include the proc crrate in the migration script . It will also be in the programmable object and that should just ovewrite with the version stored there. I'd do this in an idempotent way so that in future deployments if the proc exists, we don't try to recreate it .Not create it alter but an if exists(check sysobjects). Toss isn't great but it does solve the issue here. In the future, this also ensures builds work from scratch . Even if the proc changes, the valid version of the proc that works with this migration script works. I think if you deoy the proc first, to both shadow sha downstream environments, then add the script this may work, but if have to test. However, any deployments to a new system, like a new developer setup, would fail so I'd rather just inline the proc in the migration script, knowing that all future changes are handled as a part of the programmable object / comments

I'm assuming your migration script uses the procedure to do some work? I'd that right?What is say here is you can really only do one thing and thats include the proc crrate in the migration script ...

0 votes

the order of when you do things sometimes becomes an issue. The idea with SOC is that it's always linked between your branch and the db. If you unlink, relink, you'll get into strange places. The goal here is to get changes from your VCS into the db first, since those can be made by other users, and then try to get your changes in there. In this case, I think you confused this by linking after doing work. The idea with a live database is that you are pulling to a blank VCS system, not using this to get changes to/from the VCS. That's a model that is fraught with issues. / comments

the order of when you do things sometimes becomes an issue. The idea with SOC is that it's always linked between your branch and the db. If you unlink, relink, you'll get into strange places. The g...

0 votes

With my style, I get this: <div>IF @CalculateHeld = 'Yes'</div><div>BEGIN</div><div>    SELECT *</div><div>    FROM [#HeldFunds]</div><div>    WHERE [HeldAmount] > 0;</div><div>    DROP TABLE [#HeldFunds];</div><div>END;</div><div>ELSE</div><div>BEGIN</div><div>    SELECT *</div><div>    FROM [#HeldFunds2]</div><div>    WHERE [HeldAmount] > 0;</div><div>    DROP TABLE [#HeldFunds2];</div><div>END;<br></div> What I'd suggest is look at the whitespace and the control flow sections. Watch out for checking the "wrap if longer than" items. I've had some strange behavior here when these are applied with certain values. / comments

With my style, I get this:<div>IF @CalculateHeld = 'Yes'</div><div>BEGIN</div><div>    SELECT *</div><div>    FROM [#HeldFunds]</div><div>    WHERE [HeldAmount] > 0;</...

0 votes

Honestly, what to do here is actually fix this as a part of build and fail the build if the constraint isn't named with doso standard. This for a couple things. Developers actively consider the constraint more and you also ensure no duplicate constraints get generated by using a standard That being said, it would be nice for SCA to generate these according to some pattern and offer to fix your dev db / comments

Honestly, what to do here is actually fix this as a part of build and fail the build if the constraint isn't named with doso standard. This for a couple things. Developers actively consider the con...

0 votes

There is no workaround here. The solution is to name your constraints. They should be consistently named in all environments to ensure you can alter/drop them easily. / comments

There is no workaround here. The solution is to name your constraints. They should be consistently named in all environments to ensure you can alter/drop them easily.

0 votes

I don't mean to distract you or avoid the question, but there are a couple things in here that I want to point out. First, there's no magic on how to make changes. When we alter a table, there are really only a couple things that can be done in SQL Server to change a table. All of us are bound by the rules of the database engine for making changes, and we can't avoid those rules in terms of meta data only changes, or rewriting pages, or anything else. Second, the issues with large tables exist with small ones, but because of the scale or duration of changes (or resources), we don't notice them as much. We also can recover quicker, just because a rebuild/copy/etc, of 100 rows in inherently less disruptive that it is with 1,000,000,000 rows. Now, some of the issues with DACPAC changes are that you lack control in how you would prefer to make these changes. Similar issues can occur with SQL Compare, though I think Redgate does a better job of warning you and giving you a script to review. With SQL Change Automation (SCA), if you are tracking these changes in VS as migration scripts, you have complete control over how to make the change. We can't mitigate a bad decision, like locking a 1,000,000,000 row table to add and change data or rebuild an index. You might have valid reasons to make that change. We do suggest ways to make some changes, but at the time of migration script import, you need to ensure that you review the method of making the change. The advantage of SCA is that we give you complete control to decide how the change should be deployed. We don't necessarily give you the best method for your environment, since the requirements and restrictions vary by application, but we allow you to write the code that suits you. This means you need to understand how to make large table changes effectively in your world. There numerous ways to approach problems and you ought to research the best ways. A few ideas - https://sqlstudies.com/2016/07/14/altering-a-column-in-a-large-table-a-case-study/ - https://www.sqlservercentral.com/Forums/Topic851917-1550-1.aspx - https://www.red-gate.com/simple-talk/sql/database-administration/changing-data-types-large-tables-int-bigint-conundrum/ - https://serverfault.com/questions/36696/how-to-speed-up-adding-column-to-large-table-in-sql-server Note there are other methods, and you might need to experiment with how the different methods of making changes affect your system. Then incorporate those in your process and use them in your migration scripts. If you are using SQL Source Control, what I would recommend is that you review the scripts for ALTER TABLE changes and perhaps rewrite them. This won't flow through in your development environments necessarily, as the SCA cmdlets will still do what they do, but in smaller environments, some of these long or lengthy deployments usually don't matter if you rebuild the environment. However, for deployment to QA, Staging, Production, for the moment of the change, you'll want a real script to be used. In these cases, I'd use the new pre/post options for the table changes, and then comment out the alter in the regular script if you don't want it. / comments

I don't mean to distract you or avoid the question, but there are a couple things in here that I want to point out.First, there's no magic on how to make changes. When we alter a table, there are r...

0 votes

I would definitely investigate with whoever handles the networking and verify that things are setup correctly between the system, DNS, and AD. / comments

I would definitely investigate with whoever handles the networking and verify that things are setup correctly between the system, DNS, and AD.

0 votes

Are you adding the virtual server or the node itself? Can you disclose some of the names/setup? / comments

Are you adding the virtual server or the node itself? Can you disclose some of the names/setup?

0 votes

We don't have an option to do this, though that's interesting. If you post this on UserVoice, perhaps some others would vote for this. I'm not sure this is a high priority, but maybe if there is enough interest, it would get on the enhancement list. / comments

We don't have an option to do this, though that's interesting. If you post this on UserVoice, perhaps some others would vote for this. I'm not sure this is a high priority, but maybe if there is en...

0 votes

How can we help you today?

way0utwest

Comments