November 19, 2014 at 7:41 am
In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field.
Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.
I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists.
Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.
I know I have done this type of change before with no issues. Can someone give me an idea of what may have changed?
If it matters, I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.
Thanks.
November 19, 2014 at 8:32 am
nomen (11/19/2014)
In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field.Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.
I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists.
Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.
I know I have done this type of change before with no issues. Can someone give me an idea of what may have changed?
If it matters, I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.
Thanks.
After unchecking the prevent did you get out and back in?
Are you sure no process drops and recreates the table?
November 19, 2014 at 8:38 am
Yes, I logged out of both SSMS and the RDP to the server itself several times. There are no other jobs that delete and recreate the table.
November 19, 2014 at 9:08 am
nomen (11/19/2014)
In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field.Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.
I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists.
Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.
I know I have done this type of change before with no issues. Can someone give me an idea of what may have changed?
If it matters, I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.
Thanks.
Check for jobs or code that drop and recreate the table. If you added data to the new field, the issue is nothing to do with ssms saving your changes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 19, 2014 at 9:35 am
I guess I have some digging to do. I just wanted to make sure there were no other alternatives. Thanks all for the help.
November 19, 2014 at 12:23 pm
Yup, something is dropping and recreating the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2014 at 1:41 pm
You can use the following to check the table's 'Created_datetime', which will confirm if the job or some other process is dropping and re-creating the table.
sp_help 'MyTable';
Also, is it possible that for some crazy reason you happen to have implicit transaction turned on?
What that means is that any changes you make within your SSMS session (generally one session per query tab), are visible to that session, but are generally not visible to other sessions, and the changes are not persisted (made permanent) until COMMIT TRAN command is executed. Closing the session without COMMIT will ROLLBACK the transaction, losing the changes.
Within SSMS, confirm the following setting is not turned on. The default should be unchecked (OFF).
Tools.. Options.. Query Execution.. SQL Server.. ANSI.. SET IMPLICIT TRANSACTIONS
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 19, 2014 at 1:52 pm
I will check into your suggestions. I am new with this installation and need to get a handle on what has been set up in the past. It is highly likely that I am missing something.
Thanks.
November 19, 2014 at 1:56 pm
From SSMS, under your database, have you checked schema changes history report? It will show DDL changes occured on your database objects pulling from default trace. Hope you can find some clues there...
November 19, 2014 at 2:43 pm
SreeSql (11/19/2014)
From SSMS, under your database, have you checked schema changes history report? It will show DDL changes occured on your database objects pulling from default trace. Hope you can find some clues there...
I didn't know about this report. It even shows account name that made the schema change. Thanks.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 19, 2014 at 3:37 pm
Something else to check:
Did you explicitly specify the schema name of the table on the ALTER?
If not, make sure you are not accidentally working with a similar table / table structure but in the wrong schema. [For example, people sometimes accidentally create tables in their own schema rather than 'dbo', and their code uses the unqualified table name just fine, since that is their default schema anyway.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply