Can't use LONG data type

Question

I'm trying to get some legacy SQL 2005 code to work on SQL 2012 Express. However, whenever I set the compatibility_level to 90, I error out when I try to use older data types. In theory the following code should work:

USE wsus_results
GO

ALTER DATABASE wsus_results
SET compatibility_level = 90
GO

CREATE TABLE ScTable (
 TblName VARCHAR(255) NULL,
 TblType VARCHAR(255) NULL, 
 FieldCnt INTEGER NULL,
 RecordCnt LONG NULL,
 Description LONGVARCHAR NULL,
 TblId AUTOINCREMENT PRIMARY KEY)
GO

But, I get the following error:

Msg 2715, Level 16, State 6, Line 2 Column, parameter, or variable #4: Cannot find data type LONG.

I'm sure there's something simple I'm missing, and I just need a nudge in the right direction. This isn't a permission issue and as far as I can tell, the SET compatibility_level = 90 executes fine with no errors. Still, I get an error when using LONG.

Answer 1

LONG is not a valid data type in any version of SQL Server. And changing compatibility level will not affect your ability to use old or new data types. This only affects the way certain language constructs are parsed.

Perhaps you meant DECIMAL or BIGINT.

And to pre-empt further questions: LONGVARCHAR and AUTOINCREMENT are not valid data types either (check the documentation instead of guessing). Where did you get this script, and who suggested it should work in SQL Server? I think you may have been pranked. Try this instead:

USE wsus_results;
GO

ALTER DATABASE wsus_results
SET compatibility_level = 110;
GO

CREATE TABLE dbo.ScTable -- schema prefix is important!
(
 TblName     VARCHAR(255),
 TblType     VARCHAR(255), 
 FieldCnt    INT,
 RecordCnt   BIGINT,
 Description VARCHAR(MAX),
 TblId       INT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
GO

As an aside, is every other column in the table really nullable? Does your table name really need a suffix Table? What does Sc mean? Why not actually call the table what it represents (such as SocialCows or ScientificCholesterol) instead of obfuscating the name and adding a meaningless suffix just to incur more typing?