XML Feeds
With SQL Server versions prior to SQL2005, the only way to store large amounts of data was to use the text, ntext, or image data types. SQL2005 introduced new data types that replace these data type, while also allowing all of the useful string handling functions to work. Changing the data types to the new SQL2005+ equivalent should be relatively simple and quick to implement (depending on the size of your tables). So, why wait? Convert the data types now.
The query presented below will display all the columns in all the tables within your database that are text, ntext or image.
How to detect this problem:
- SELECT O.Name,
- col.name AS ColName,
- systypes.name
- FROM syscolumns col
- INNER Join sysobjects O
- ON col.id = O.id
- INNER join systypes
- ON col.xtype = systypes.xtype
- WHERE O.Type = 'U'
- And OBJECTPROPERTY(o.ID, N'IsMSShipped') = 0
- And systypes.name In ('text','ntext','image')
- ORDER BY O.Name, Col.Name
How to correct it: Change the data type to a SQL2005+ version. Text should be converted to varchar(max), ntext should be converted to nvarchar(max) and image should be converted to varbinary(max).
Level of severity: Low
Level of difficulty: Easy
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.