|
|
Error Message
Server: Msg 243, Level 16, State 1, Line 1
Type XML is not a defined system type.
Server: Msg 243, Level 16, State 1, Line 1
Type GEOMETRY | GEOGRAPHY is not a defined system type.
Server: Msg 243, Level 16, State 1, Line 1
Type DATE | TIME | DATETIME2 | DATETIMEOFFSET is not a
defined system type.
|
The XML data type, introduced in SQL Server 2005, stores XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. Columns and local variables can be created as XML data type and store XML instances.
The GEOGRAPHY data type, introduced in SQL Server 2008, stores ellipsoidal (round-earth) data, such as GPS latitude and longitude. On the other hand, the GEOMETRY planar spatial data type, also introduced in SQL Server 2008, represents data in a Euclidean (flat) coordinate system.
The DATE, TIME, DATETIME2 and DATETIMEOFFSET data types are all introduced in SQL Server 2008. The DATE data type defines a date and the TIME data type defines a time of a day without time zone awareness and is based on a 24-hour clock. The DATETIME2 data type defines a date that is combined with a time of day that is based on 24-hour clock and can be considered as an extension of the existing DATETIME data type but has a larger date range, a larger default fractional precision and an optional user-specified precision. Lastly, the DATETIMEOFFSET data type defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.
As these new data type were introduced on different versions of SQL Server, using these data types in older versions of SQL Server will generate this error message. The simplest way of generating this error is by casting NULL to the undefined system data type, as follows done in SQL Server 2000:
SELECT CAST(NULL AS XML)
Msg 243, Level 16, State 1, Line 1
Type XML is not a defined system type.
SELECT CAST(NULL AS Geometry)
Msg 243, Level 16, State 1, Line 1
Type Geometry is not a defined system type.
SELECT CAST(NULL AS Geography)
Msg 243, Level 16, State 1, Line 1
Type Geography is not a defined system type.
SELECT CAST(NULL AS Date)
Msg 243, Level 16, State 1, Line 1
Type Date is not a defined system type.
SELECT CAST(NULL AS Time)
Msg 243, Level 16, State 1, Line 1
Type Time is not a defined system type.
SELECT CAST(NULL AS DateTime2)
Msg 243, Level 16, State 1, Line 1
Type DateTime2 is not a defined system type.
SELECT CAST(NULL AS DateTimeOffset)
Msg 243, Level 16, State 1, Line 1
Type DateTimeOffset is not a defined system type.
If the same SELECT statements were issued in SQL Server 2005, the first SELECT statement will not generate an error as the XML data type was introduced in SQL Server 2005 but the other 6 statements will generate an error as the Geometry, Geography, Date, Time, DateTime2 and DateTimeOffset data types were introduced in SQL Server 2008.
Solution / Work Around
When this error message is encountered in a script, the first thing to do is verify the version of SQL Server being used. This can easily be verified using the @@VERSION system function:
SELECT @@VERSION
The @@VERSION function returns the version, processor architecture, build date and operating system for the current installation of SQL Server. What is important here is the version. This will show either “Microsoft SQL Server 2000”, “Microsoft SQL Server 2005” or “Microsoft SQL Server 2008” (and whatever version of SQL Server the user is connected). To avoid this error, make sure the data type being used is available to the version of SQL Server the user is connecting to.
Alternatively, if the data type is not available to the particular version of SQL Server, an equivalent data type can be used. In the case of XML data type, the VARCHAR(8000) can be used in its place. The only disadvantage of using VARCHAR(8000) is that it is limited to 8,000 characters wherein the XML data type can store up to 2 gigabytes (GB) of XML data.
In the case of DATE, TIME, DATETIME2 and DATETIMEOFFSET data types, the only data type that can be used in its place is the DATETIME data type. The SMALLDATETIME data type can also be used but with less accuracy than DATETIME.
Lastly, in the case of the GEOMETRY and GEOGRAPHY data types, the IMAGE data type can be used in its place. The IMAGE data type can store a variable-length binary data from 0 through 2,147,483,647 bytes.
|