50 results found

1. Need ability to replace specific Data Table columns with functions, and then ignore in comparisons

   One of the things that we have in all of tables is a "DateCreated" and "ModifiedBy". DateCreated is supposed to be the UTC time of the creation of the record. For Metadata supported by SQL Source Compare -- this is becoming the date the script was written, rather than the date the production goes live. We would like to state that the actual column should be replaced by GETDateUTC(), rather than the time the record was first created in development, and then we want to tell SQL Source Control to ignore the underlying column for comparison.

   We want to be able to do something similar for "ModifiedBy", except we want to replace it with a static text value.

   One of the things that we have in all of tables is a "DateCreated" and "ModifiedBy". DateCreated is supposed to be the UTC time of the creation of the record. For Metadata supported by SQL Source Compare -- this is becoming the date the script was written, rather than the date the production goes live. We would like to state that the actual column should be replaced by GETDateUTC(), rather than the time the record was first created in development, and then we want to tell SQL Source Control to ignore the underlying column for comparison.

   We want to be… more

   9 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   0 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

2. Foreign keys recreated with NOCHECK during data sync

   We have the same issue as noted here: http://www.red-gate.com/messageboard/viewtopic.php?t=13662&highlight=foriegn+foreign+key

   The FK is recreated with NOCHECK during data sync which triggers a schema change.

   104 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   5 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

3. SQL Source Control Static Views (or Table Subsets using WHERE clause)

   I would like the ability to source control a subset of a table (perhaps using a view).

   I have inherited a database where lookup data is intermixed with user defined data. We reference the lookup data in the table using a code (VARCHAR) that is unique when another field is NULL, so it is possible to ensure that records are created/updated/deleted without concern for the primary key involved.

   For more information, you can refer to this forum post:
   http://www.red-gate.com/messageboard/viewtopic.php?p=50087

   636 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   30 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close
   under review  ·  Kendra responded

   Hi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.

   With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.

   SQL Source Control introduced pre- and post- scripts in v6.3.

   A post-deployment script gives you a good amount of flexibility over exactly which rows or columns of data you want to include in your project. Example post-deployment scripts for static data are here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data

   If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.

   SQL Change Automation:

   • Supports column filtered static data tables in the SCA plugin in SSMS
   • Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
   • Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts

   If there are other use cases where a migrations approach would add value to the team – such as easily being able to run data modification statements as part of deployments, or being able to customize the TSQL for schema changes – then changing from SQL Source Control to SQL Change Automation might make sense.

   Hi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.

   With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.

   SQL Source Control introduced pre- and post- scripts in v6.3.

   A post-deployment script gives you a good amount of flexibility over exactly which rows or columns of data you want to include in your project. Example post-deployment scripts for static data are here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data

   If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.

   SQL Change Automation:

   • Supports column filtered static data tables in the SCA plugin in SSMS
   • Supports multiple post-deployment scripts, in case there is… more

4. Make SQL Data Easier to Merge (Static Data)

   Currently, SQL Data files contain a bunch of insert statements, but the order of the insert statements can be inconsistent. For example, new records might get inserted on the top or the bottom. Also, multiple SET IDENTITYINSERT statements sometimes get created. This makes it difficuly to merge data changes between multiple branches in TFS Source Control. If the Insert statements had a consistent order and only included one SET IDENTITYINSERT diffs and merges would be easier.

   530 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   37 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close
   under review  ·  Kendra responded

   Thanks for this suggestion and for the many comments and upvotes. I realize that this is a pain point.

   I have a few shorter-term workarounds to summarize as well as some information on the longer roadmap in this update. I know these shorter-term workarounds aren’t perfect (I summarize the pros and cons), but I’m posting them as they may help a few folks.

   Workaround 1) When data changes to static data need to be made, use a “relink the table” pattern
   One can “cleanly rescript” a static data table in SQL Source Control by:

   • Unlinking the static data table
   • Committing
   • Relinking the static data table
   • Committing

   Pro: This works with the GUI and requires no special knowledge or comfort with TSQL. This may help folks with just a few static data tables.
   Con: This requires extra steps and results in extra commits in the history, which I realize can be annoying, particularly if the team doesn’t squash commit history when merging or do similar things.

   Workaround 2) Use a post-deployment script to manage static data tables instead of linking them
   Another option is to not use the “link static data tables” feature at all, and instead manage the static data table yourself with a custom script.

   I’ve added a code sample called “post-deployment script to manage static data for a table with an identity column” into our reference on post-deployment scripts: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data

   Pro: This gives you complete control over the TSQL.

   Con: Your post-deployment script might become quite complex and create its own issue in merge conflicts, depending on how many static data tables you have and whether you are using the post-deployment script for other things.

   An alternate approach using SQL Change Automation
   If customers have licenses for SQL Change Automation, the SCA Migrations approach does not have this issue and has additional features for flexibility with static data:

   • Since migrations only script out the data which has changed, this scripting issue of the entire table doesn’t occur in SCA – it only ever needs to generate commands for what has been modified since the last migration
   • Supports column filtered static data tables in the SCA plugin in SSMS
   • Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
   • Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts

   If there are other use cases where a migrations approach would add value to the team – such as easily being able to run data modification statements as part of deployments, or being able to customize the TSQL for schema changes – then changing from SQL Source Control to SQL Change Automation might make sense.

   Roadmap
   I’ve chatted with our engineering teams and Redgate does have the desire to better support this scenario longer term. This is not a “quick fix” scenario and will likely require a rethinking of the way static data is managed within the tool.

   While the “rethinking” bit of this has begun, we don’t specifically have this on the SQL Source Control roadmap (https://www.red-gate.com/products/sql-development/sql-source-control/roadmap) at this point. We do have improved support for use with Git feature branches as a future candidate on the roadmap, and we do know from research with many customers that static data is quite important to folks. While I can’t make any promises, I think we are likely to be able to offer an improvement in this area.

   Thanks for this suggestion and for the many comments and upvotes. I realize that this is a pain point.

   I have a few shorter-term workarounds to summarize as well as some information on the longer roadmap in this update. I know these shorter-term workarounds aren’t perfect (I summarize the pros and cons), but I’m posting them as they may help a few folks.

   Workaround 1) When data changes to static data need to be made, use a “relink the table” pattern
   One can “cleanly rescript” a static data table in SQL Source Control by:

   • Unlinking the static data table
   • Committing
   • Relinking the static data table
   • Committing

   Pro: This works with the GUI and requires no special knowledge or comfort with TSQL. This may help folks with just a few static data tables.
   Con: This requires extra steps and results in extra commits in the history, which I realize can… more

5. Include Linked Servers as a versioned object

   We use Linked-Servers to connect to Oracle databases. These objects can be added by a developer and then need to be versioned like any other SQL object.

   7 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   4 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

6. showing table row count in the link static data screen could help avoid adding large tables

   don't have to perform a count, just a quick look at sysindexes would be sufficient.

   3 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   1 comment  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

7. Put blue indicators on Object Explorer for changes to static data

   I have my database linked to source control, and if I make a change to a schema, it shows a blue dot in the Object Explorer to indicate there are pending changes. However the same does not apply to static data – if I change some data in a table that is setup for static data source control, there is no icon to indicate there are changes to be committed. The data change does appear when I go to the commit changes tab however.

   15 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   1 comment  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

8. Incorrect differences shown for data changes

   I just did a data edit (inserted one row) but in the differences I see three rows inserted and two removed. Two of the rows inserted are letter for letter identical to the two rows removed.

   Doesn't really matter, just a little confusing to read

   13 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   2 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

9. Undo data changes

   I can undo any DDL operation but I don't have the option to undo a data change in a source controlled table. I think data changes should also be in the list of changes I can undo.

   200 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   8 comments  ·  Static Data  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close
   Kendra responded

   While SQL Source Control does not currently provide a simple way to right click and ‘Undo’ static data changes on the ‘Commit’ screen as suggested, there is a workaround which may help some users.

   If you wish to revert the static data in the table to a previous version which you have committed, you can do this by viewing the history of commits for the object in SQL Source Control, and then launching SQL Data Compare to update the database.

   Please note that this workaround requires a license for Data Compare. More detail on how to do this are here: https://documentation.red-gate.com/soc/common-tasks/update-to-a-revision-from-source-control

10. Must treat Static Data like source code

    Our application is highly metadata driven thus we must treat our static data like source code. Specifically we need accept deleteions that are made locally, apply insertions made globally, and identify the differences. Currently Sql Source Control does NOT differentiate between rows missing from the local database because either 1) the local developer deleted them or 2) they were added by a global developer to the latest source code controlled version. In case 1, we want to drive that deleteion back into the latest source code controlled version. In case 2, we want to drive that insertion into the local database (as part of get latest/ commit changes).

    I believe to do this you will have to either keep a local copy of the Source code controlled files or the version/changeset # so you can see what was originally in the database when the last "Get latest" was done.

    Without this capability we cannot deploy the product as we will be destroying each other's work.

    Our application is highly metadata driven thus we must treat our static data like source code. Specifically we need accept deleteions that are made locally, apply insertions made globally, and identify the differences. Currently Sql Source Control does NOT differentiate between rows missing from the local database because either 1) the local developer deleted them or 2) they were added by a global developer to the latest source code controlled version. In case 1, we want to drive that deleteion back into the latest source code controlled version. In case 2, we want to drive that insertion into the local… more

    13 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    2 comments  ·  Static Data  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close