March 28, 2009 at 12:12 am
Hi,
i have the following error
Server: Msg 2514, Level 16, State 1, Line 1
Table error: Data type 987 (type '987') does not match between 'SYSCOLUMNS' and 'SYSTYPES'.
Server: Msg 2514, Level 16, State 1, Line 1
Table error: Data type 987 (type '987') does not match between 'SYSCOLUMNS' and 'SYSTYPES'.
during running of
dbcc checkcatalog ('SALE') in the SQL2000
can i reslove this issue? ,then how?
ARUN SAS
April 3, 2009 at 11:50 am
is this database upgraded from SQL 2000 to SQL 2005.
CHECKCATALOG in sql 2005 is more rigrous than sql 2000
April 3, 2009 at 1:30 pm
arun.sas (3/28/2009)
Hi,i have the following error
Server: Msg 2514, Level 16, State 1, Line 1
Table error: Data type 987 (type '987') does not match between 'SYSCOLUMNS' and 'SYSTYPES'.
Server: Msg 2514, Level 16, State 1, Line 1
Table error: Data type 987 (type '987') does not match between 'SYSCOLUMNS' and 'SYSTYPES'.
during running of
dbcc checkcatalog ('SALE') in the SQL2000
can i reslove this issue? ,then how?
Is this 2000 or 2005 DB? Or have you upgraded from 2000. could you provide more insight into this please?
April 3, 2009 at 3:08 pm
This is definitely 2000, from the error messages. 2005 errors from CHECKCATALOG are completely different.
Did you create a user-defined type, and then someone mess with the system tables directly?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 3, 2009 at 9:01 pm
Hi,
Ok Paul,
How to locate the problem data type?
How to resolve this issue?
ARUN SAS
🙂
April 4, 2009 at 2:45 am
Is this still a SQL 2000 server or has it been upgraded to a SQL 2005 server?
It's an important question that needs an answer. The methods of fixing are very different between the two.
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
April 4, 2009 at 2:47 am
Hi,
SQL 2000 server Only,No upgread to 2005
ARUN SAS
🙂
April 4, 2009 at 3:16 am
Please post SQL 2000-related questions in the SQL 2000 forums in the future. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.
What's the results from the following two queries?
select distinct type, xusertype, prec, scale from syscolumns
where xusertype = 987
select xtype, xusertype, prec, scale from systypes
where xusertype = 987
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
April 4, 2009 at 3:42 am
Hi,
Thanks,
After run your statement
I got
typexusertypeprecscale
108987288
(1 row(s) affected)
xtypexusertypeprecscale
(0 row(s) affected)
Then got the id from syscolumns, and located the table thro syscommends
Table structure also attached
and i find out the col having the datatype null
(how this happned, without the datatype how the table created
and data still stored?)
ARUN SAS
:w00t:
April 4, 2009 at 3:58 am
Most likely someone's been directly updating the system tables after configuring the server to allow updates to them. This is the usual kind of mess that results from someone doing that.
How much effort is it to export the data from that table to a file, drop the table, recreate the userdefined data types that it used (if they don't exist) and then recreate and repopulate the table?
I'm not 100% sure, but I think that'll be the safest fix for this.
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
April 4, 2009 at 4:08 am
Thanks a lot Gail Shaw
Now the issue rectified by your guide
ARUN SAS
😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply