XML Feeds
There are several string data types in SQL Server. There are varchar, nvarchar, char, and nchar. Most front end languages do not require you to identify the length of string variables, and SQL Server is no exception. When you do not specify the length of your string objects in SQL Server, it applies its own defaults. For most things, the default length is one character. This applies to columns, parameters, and locally declared variables. The notable exception is with the cast and convert functions which default to 30 characters.
How to detect this problem:
SQL 2005 (and up)
- SELECT Name
- FROM sys.sysobjects
- WHERE XType = 'P'
- And Object_Definition(ID) Like '%varchar[^(]%'
- And OBJECTPROPERTY(ID, N'IsMSShipped') = 0
- ORDER BY Name
SQL2000
- SELECT Name
- FROM (
- SELECT S.Name, C.TEXT
- FROM sysobjects S
- INNER Join syscomments C
- ON S.id = C.id
- And S.xtype = 'P'
- WHERE OBJECTPROPERTY(S.ID, N'IsMSShipped') = 0
- UNION All
- SELECT OBJECT_NAME(A.id), LeftText + RightText
- FROM sysobjects s
- INNER Join (
- SELECT Id, RIGHT(TEXT, 10) AS LeftText, ColId
- FROM syscomments
- ) AS A
- ON S.id = A.id
- And OBJECTPROPERTY(S.ID, N'IsMSShipped') = 0
- And S.xtype = 'P'
- INNER Join (
- SELECT Id, LEFT(TEXT, 10) AS RightText, ColId
- FROM syscomments
- ) AS B
- ON A.id = B.id
- and A.ColId = B.ColId - 1
- ) AS A
- WHERE TEXT Like '%varchar[^(]%'
- ORDER BY Name
How to correct it: To correct this problem, identify where it exists (using the SQL shown above). Then, for each occurrence, identify the size. If the problem occurred when declaring a column in a table and you WANT the size to be 1 character, then specify (1). Other places, you will need to determine the size that it should be. Sometimes this involves looking up the size in the table definition.
Level of severity: High, because this problem can corrupt your data.
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.