November 19, 2001 at 8:33 am
We have a problem that causes fields to be lost from tempdb..syscomments.
I am not sure of the cause yet (I know that the suspected procedure uses temp tables, uses xp_cmdshell and does a few other things). Before I head into further investigations, has anyone else experienced anything similar?
Columns in tempdb..syscomments:
id, number, colid, status, ctext
Columns in model..syscomments:
id, number, colid, status, ctext, texttype, language, encrypted, compressed, text
We are running SQL 2000 SP1.
We first spotted the problem when, after the Server started playing up, we double clicked on tempdb..syscomments in Enterprise Manager and got an error, complaining about the missing columns.
Any suggestions gratefully received.
November 19, 2001 at 9:35 am
Haven't seen anything like this. Can you provide more information? What was the server doing? What happens if you select * from Quary Analyzer?
Steve Jones
November 19, 2001 at 3:36 pm
What about checking syscolumns, information_schema.columns, or even sysobjects.
Steve Jones
November 20, 2001 at 3:26 am
These tables appear to be truncated too:
syscolumns - OK as far as language, later columns missing
SELECT * FROM information_schema.columns
Returns
Server: Msg 207, Level 16, State 3, Procedure COLUMNS, Line 5
Invalid column name 'text'.
Server: Msg 207, Level 16, State 1, Procedure COLUMNS, Line 5
Invalid column name 'isnullable'.
Server: Msg 207, Level 16, State 1, Procedure COLUMNS, Line 5
Invalid column name 'scale'.
Server: Msg 207, Level 16, State 1, Procedure COLUMNS, Line 5
Invalid column name 'collation'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'information_schema.columns' because of binding errors.
sysobjects - still has all columns
I have now re-set the Server and the tables are back to normal.
The problem was probably caused by a procedure that FTPs a file. This builds a couple of temp tables, outputs their contents to files and then executes them.
I will add further details if we manage to track it down further.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply