Unable to cache database objects

• 
  Thanks for the feedback.
  It doesn't look like an insufficient memory problem, but rather a problem with cacheing your database.
  
  It would help us solve your problem if you could answer a few questions:
  - Which version of SQL Server were you connecting to
  - Does this error occur repeatedly, on all databases, or was it just once on one database?
  - If the error occurs repeatedly on one database could you run this query against it and post/pm me the results (if any):
  
  SELECT sysobjects.name ,
  schemas.name ,
  id ,
  uid ,
  type ,
  xtype
  FROM sys.sysobjects
  LEFT JOIN sys.schemas ON sysobjects.uid = schemas.schema_id
  WHERE sys.schemas.name IS NULL
  
  - Is it likely that the database schema changed whilst it was being cached by prompt?

  simon.jackson July 14, 2009 05:32

  - 0 +
• 
  I'm running a MS SQL Server 2008 Client against a MS SQL Server 2000 database. The error occurs repeatedly against one database.
  That query doesn't work. Invalid object name 'schemas'.
  
  Also, the schema wasn't changed while being cached.

  damcis July 14, 2009 10:26

  - 0 +
• 
  Ah, the equivalent query for SQL 2000 is
  
  
  SELECT sysobjects.* FROM sysobjects LEFT JOIN sysusers ON dbo.sysusers.uid = dbo.sysobjects.uid
  WHERE sysusers.name is null

  simon.jackson July 14, 2009 10:32

  - 0 +
• 
  Thanks for the query.
  
  It returns no results.

  damcis July 14, 2009 11:14

  - 0 +
• 
  Would it be possible to send me one of these (in descending order of preference):
  
  - A backup of the database
  - A SQL Compare snapshot of the database
  - The SQL used to create the schema of the database

  simon.jackson July 14, 2009 11:50

  - 0 +
• 
  Sure, I can provide a sql compare snapshot of our database.
  How should I send the file?

  damcis July 15, 2009 10:25

  - 0 +
• 
  Oops, sorry. Email to
  simon <dot> jackson <at> red-gate <dot> com
  Would be excellent.

  simon.jackson July 15, 2009 10:26

  - 0 +
• 
  Just wanted to post a resolution to this problem. With Simon's help I figured out one of our tables was lacking a column datatype because it was being created on the fly every few minutes. A drop table was being called and then a select into with one of the columns defined like this: (MAX(rate_eff_dt)-1) As proposed_end_dt. The proposed end dt column had no datatype and caused sql prompt to fail to cache the database.
  
  I changed the sql job that called this script to explicitly create the table and then insert into table with a select. This fixed the problem.

  damcis July 27, 2009 09:15

  - 0 +

Add comment

Please sign in to leave a comment.