Columns are missing... even though they are created and listed in SSM

Question

I am at a complete loss.

I have a database which is working fine on another machine. Several machines, actually. I used the tasks to script the entire DB (no data) so I could set it up on my new laptop. I have done this several times, never had a problem.

Here is the table definition script for one table:

/****** Object:  Table [dbo].[UUT_RESULT]    Script Date: 2022-07-05 18.12.41 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE TABLE [dbo].[UUT_RESULT](  
	[ID] [uniqueidentifier] NOT NULL,  
	[STATION_ID] [varchar](255) NULL,  
	[BATCH_SERIAL_NUMBER] [varchar](255) NULL,  
	[TEST_SOCKET_INDEX] [bigint] NULL,  
	[UUT_SERIAL_NUMBER] [varchar](255) NULL,  
	[USER_LOGIN_NAME] [varchar](255) NULL,  
	[START_DATE_TIME] [datetime] NULL,  
	[EXECUTION_TIME] [float] NULL,  
	[UUT_STATUS] [varchar](32) NULL,  
	[UUT_ERROR_CODE] [bigint] NULL,  
	[UUT_ERROR_MESSAGE] [varchar](255) NULL,  
	[PART_NUMBER] [varchar](255) NULL,  
	[TSR_FILE_NAME] [varchar](255) NULL,  
	[TSR_FILE_ID] [varchar](64) NULL,  
	[TSR_FILE_CLOSED] [bit] NULL,  
	[testName] [varchar](255) NULL,  
	[productionFlag] [bit] NULL,  
	[runTag] [varchar](255) NULL,  
	[testSystemPn] [varchar](32) NULL,  
	[testSwPn] [varchar](32) NULL,  
	[seqFileName] [varchar](255) NULL,  
	[fixtureId] [varchar](255) NULL,  
	[lot] [varchar](32) NULL,  
	[reportPrefix] [varchar](255) NULL,  
	[note] [varchar](max) NULL,  
	[tsReportName] [varchar](255) NULL,  
	[productionStage] [int] NULL,  
	[retest] [bit] NULL,  
	[failure_parameter] [varchar](255) NULL,  
 CONSTRAINT [UUT_RESULT_CONSTRAINT] PRIMARY KEY CLUSTERED   
(  
	[ID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  

This seems to have created perfectly correctly. And all those columns are listed in the tree on the left in SSMS.

Before any data was in there, I queried the tale with SELECT * and received back an empty set with all the columns.

Then I rant this insert...

INSERT INTO [UUT_RESULT]  
(  
	[ID],  
	[STATION_ID],  
	[BATCH_SERIAL_NUMBER],  
	[TEST_SOCKET_INDEX],  
	[UUT_SERIAL_NUMBER],  
	[USER_LOGIN_NAME],  
	[START_DATE_TIME],  
	[EXECUTION_TIME],  
	[UUT_STATUS],  
	[UUT_ERROR_CODE],  
	[UUT_ERROR_MESSAGE],  
	[PART_NUMBER],  
	[TSR_FILE_NAME],  
	[TSR_FILE_ID],  
	[TSR_FILE_CLOSED],  
	[testName],  
	[productionFlag],  
	[runTag],  
	[testSystemPn],  
	[testSwPn],  
	[seqFileName],  
	[fixtureId],  
	[lot],  
	[reportPrefix],  
	[note],  
	[tsReportName],[productionStage],[retest],[failure_parameter])  
VALUES  
('5859E391-00D2-11ED-9D7B-00802F303F4B','ATE-3TX','',-1,'1209414900018','administrator','Jul 10 2022  9:28:49:000PM',1.655454089000000e+002,'Passed',0,'','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)  

And I got an error that the last three columns did not exist:

Msg 207, Level 16, State 1, Line 3  
Invalid column name 'productionStage'.  
Msg 207, Level 16, State 1, Line 3  
Invalid column name 'retest'.  
Msg 207, Level 16, State 1, Line 3  
Invalid column name 'failure_parameter'.  

AND When I query * again, they are not there any more!

• They are listed in the tree on the left
• They are created if I script the table creation
• They were there when I did the initial query (I have restarted the DB service)
• SSMS is underlining them as if they don't exist
• I have reconnected, restarted SSMS, etc.

What is going on here?!!

Answer 1

Just to be clear.

You have 2 or more versions of the table UUT_RESULT in different schemas.

In your create you specified "dbo.UUT_RESULT". In your INSERT you did not specify the schema. Therefore SQL Server tries to find the table in the "current" schema first. You should always specify the schema when running commands.

Answer 2

You have small green men in your machine that drops your columns, it is a simple as that.

No, more seriously, this nothing we can answer here, because we don't have access to your system. Nor do we know exactly what you did.

But from experience, here are some possible goofs you may have done:

• You are connected to a different server/instance that you think you are.
• You are connect to a different database than you think you are.
• You restored an older version of the database.
• There was this chain of events:

1. You started a transaction.
2. You started an execution, and then stopped it with the red button, leaving the transaction open.
3. You ran DROP + CREATE TABLE + SELECT.
4. You had some execution error of the type that aborts the batch and rolls back the transaction.
5. You got back the old version of the table. (I note that the missing columns are the last three ones.)

For the last point, you can avoid this issue with this setting in SSMS:

Answer 3

This problem is really strange. I ran it locally and found no problems. Did you check whether the column under the path after creating the table exists? Like this：

If it exists, then it is possible that you did not switch your database, such as this table created by the master you used (of course not recommended in this database). Now you will be prompted to copy directly to other databases, but sometimes this It does not affect the execution of the command. You can try ctrl+A and press F5 to execute all the statements to see if there is any error.

Bert Zhou

Answer 4

@Tom Phillips

I copied this out of the create statement for the entire DB. I did not run it separately.

How do I have different versions of this table in different schemas? I only have the one Database on this server (other than the built-in databases).

Now, on this particular machine, this database did exist in a previous form. However, I dropped that DB and ran the create DB file that contains the code above.

When I first query, I get an empty table that correctly lists the columns. It's when I attempt to run an insert statement that the Server reports that the columns don't exist.

If I change the name of the DB, it works perfectly.

So I think that something may be cached.

Are there caches I can clear out? I am looking at execution plans, but I don't know much about them.

Answer 5

It seems those three are missing in the Information_schema...

SELECT  
  	TABLE_NAME, COLUMN_NAME  
FROM  
  	INFORMATION_SCHEMA.COLUMNS  
WHERE table_name='UUT_RESULT'  

Returns:

TABLE_NAME	COLUMN_NAME  
UUT_RESULT	ID  
UUT_RESULT	STATION_ID  
UUT_RESULT	BATCH_SERIAL_NUMBER  
UUT_RESULT	TEST_SOCKET_INDEX  
UUT_RESULT	UUT_SERIAL_NUMBER  
UUT_RESULT	USER_LOGIN_NAME  
UUT_RESULT	START_DATE_TIME  
UUT_RESULT	EXECUTION_TIME  
UUT_RESULT	UUT_STATUS  
UUT_RESULT	UUT_ERROR_CODE  
UUT_RESULT	UUT_ERROR_MESSAGE  
UUT_RESULT	PART_NUMBER  
UUT_RESULT	TSR_FILE_NAME  
UUT_RESULT	TSR_FILE_ID  
UUT_RESULT	TSR_FILE_CLOSED  
UUT_RESULT	testName  
UUT_RESULT	productionFlag  
UUT_RESULT	runTag  
UUT_RESULT	testSystemPn  
UUT_RESULT	testSwPn  
UUT_RESULT	seqFileName  
UUT_RESULT	fixtureId  
UUT_RESULT	lot  
UUT_RESULT	reportPrefix  
UUT_RESULT	note  
UUT_RESULT	tsReportName  

How can I deal with this?

If I drop a database, how can I ensure that all of it's metadata is cleared from the Information_schema?