User profile for yzguy

Not exactly, you did reproduce something similar, but not what we did. You created a sequence and set it as a default value for a column, that is NOT what we did. We created a table with an identity column, and the SYSTEM created the sequence. This is NOT our issue. Our issue is trying to resolve why schema compare is not properly identifying an identity column in our db, as an identity. It tries to MAKE it an identity on EVERY release, even though there should be no change. There is likely a db issue, or oracle issue, but something is preventing schema compare from properly identifying this column as an identity in the db, so it repeatedly tries to make it an identity, every single time. / comments

Not exactly, you did reproduce something similar, but not what we did. You created a sequence and set it as a default value for a column, that is NOT what we did.We created a table with an identity...

0 votes

let me see if I can simplify this. We start by running this statement on our db (drop the table, nothing existing): CREATE TABLE BATCH ( batch_id NUMBER GENERATED BY DEFAULT AS IDENTITY, coor_id VARCHAR2(50 BYTE) NOT NULL, stts VARCHAR2(30 BYTE), batch_dt DATE, crt_nm VARCHAR2(256 BYTE) DEFAULT USER, crt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP, last_udt_usr_nm VARCHAR2(256 BYTE) DEFAULT USER, last_udt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP ); then we SAVE that script in a script location, then use schema compare to compare the script location to our db, and it "sees" a difference between the script location and the db table (even though there is not any). Schema compare thinks it needs to make the db table an identity, when in fact it ALREADY is. The problem here is schema compares ability to determine that the column is an identity is not working, on this db. This works fine on another db, and I suspect will work fine for you. I suspect there is something wrong with one of our db's (or rights missing, or something different) as this works fine on another. My problem is that I don't know what schema compare is looking at, so I don't know where to start looking for problems. / comments

let me see if I can simplify this. We start by running this statement on our db (drop the table, nothing existing):CREATE TABLE BATCH ( batch_id NUMBER GENERATED BY DEFAULT AS IDENTITY, coor_id ...

0 votes

I did some more digging. At first I thought this was a general issue that would happen any time a table was recreated by schema compare (like when you have column order enforced and add a column in the middle), but then when I tried to reproduce it on a different server, I was unable to. I was actually pretty surprised at how well it did handle it (so good job there [image] ). So here is what I learned: I am pretty sure the issue is that schema compare is NOT able to correctly determine that the column is already an identity in the db. We are comparing a script location to a live db, then generating the release script as part of our release process. The script generated is what I posted above, where the table is recreated, data inserted, and sequence not updated to start at a higher number. Our release process ALSO compares the db back to the script location, to create a "backout" script. This back out script looks like this: ---------------------------- WHENEVER SQLERROR EXIT SQL.SQLCODE -- -- Script generated by Schema Compare for Oracle 5.2.3.1222 on 13/03/2020 15:56:52 -- SET DEFINE OFF ALTER TABLE espsvc_owner."BATCH" MODIFY batch_id DROP IDENTITY; ALTER TABLE espsvc_owner."BATCH" MODIFY (batch_id NUMBER DEFAULT espsvc_owner."ISEQ$$_23832".nextval NOT NULL); ------------------------------ This tells me that schema compare thinks that the db column is NOT an identity, and just that the default value is the sequence.nextval. This same process will happen over and over. If the release script runs, and redgate makes the table what it thinks it needs, the next release of the SAME table code, will generate the SAME release script. I'm pretty sure it is comparing a column that is identity in the scripts folder, to the db, and thinking that the db column is NOT an identity (when in fact it is, schema compare made the script to ensure that last release), and EVERY release results in the same script that messes up the sequence number. Now I'm pretty sure there is something not quite right in our db (at least this one, I am not able to reproduce this issue in another), but I don't know exactly what I am looking for. Can you tell me what schema compare is looking at to determine a columns properties, like is it an identity? So we can check that on this db, to see if we have a db issue that needs to be addresses so that schema compare can properly read the table structure? / comments

I did some more digging. At first I thought this was a general issue that would happen any time a table was recreated by schema compare (like when you have column order enforced and add a column i...

0 votes

Not exactly. The table was NEVER created with a default value and was ALWAYS an identity. We can recreate this on one db we have with a new table. We create the table, with identity, then use that script in a schema folder to generate an update script (which should do nothing) and it generates the script it thinks it needs, which is to change it TO an identity, but it already WAS an identity, it always was. The issue here is that schema compare is not detecting the column correctly when reading from the db. The issue is only reproduce able on one server that we have tried, it works fine on another. There is something different on the one server, but I have no idea what, or what to even look for. That is why I asked what specifically is schema compare looking for to detect if something is an identity column. It would give me a place to start looking. / comments

Not exactly. The table was NEVER created with a default value and was ALWAYS an identity. We can recreate this on one db we have with a new table. We create the table, with identity, then use th...

0 votes

I have been using sql compare for quite some time, and can use that just fine. There seems to be some rights issue I'm having, but I'm not sure exactly what sql source control is doing so I'm not sure what rights to ask for. For that matter, I'm not even 100% sure it is db rights, it could be TFS rights, but judging by the list of things it says it is doing, it looks to be all db related stuff until the end (and this is the very first thing it is trying to do). I can probably get the rights, I just don't know what to ask for. BTW, the compare and the list of items to check in works fine (which is what I would suspect to be similar to sql compare) it isthe commit changes that is failing. / comments

I have been using sql compare for quite some time, and can use that just fine. There seems to be some rights issue I'm having, but I'm not sure exactly what sql source control is doing so I'm not ...

0 votes

in case anyone else needs to know, this statement needs to return 1 for the user trying to commit changes SELECT HAS_PERMS_BY_NAME('YourDBName', 'DATABASE', 'ALTER') / comments

in case anyone else needs to know, this statement needs to return 1 for the user trying to commit changes SELECT HAS_PERMS_BY_NAME('YourDBName', 'DATABASE', 'ALTER')

0 votes

How can we help you today?

yzguy

Comments