Featured Post

Traffic Roundabouts

Now I know that roundabouts are something new to most American’s when they encounter them, but damn people use some common sense in regards to other drivers. Drivers on a roundabout near my house simply do not grasp that there are two lanes moving around that circle. Lets go over the basic rules...

Read More

Having Trouble Scripting SQL Tables Containing nvarchar(max) fields

Posted by Joe King | Posted in MS SQL | Posted on 22-08-2010

0

Have you trying to script tables in your SQL2005 or 2008 database and you receive this error?

Column projectDescription in object [TABLENAME] contains type NVarCharMax, which is not supported in the target server version, SQL Server 2000. (Microsoft.SqlServer.Smo)

This error is caused by an incorrect scripting option in Microsoft SQL Server Management Studio being set to a version prior to 2005.

Obviously, to use the nVarCharMax field type the compatibility mode on the database needs to be 90 or 100, which is SQL 2005 and 2008.  You can easily check this via script (like below) or the Properties > Options screen on the database properties.

SELECT compatibility_level from sys.databases WHERE name='[MYDATABASE]'

To correct the error caused by the table creation; using Microsoft SQL Server Management Studio, go to TOOLS menu, then OPTIONS.  Expand “SQL Server Object Explorer”, then select “Scripting”.  You should see a screen like the example below (SQL2008).  Simply update the “Script for server version” to the appropriate setting, then try scripting your table again.

Write a comment