nvarchar(max)

[Rob]

Hello All,

I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
error:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.

If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?

Regards,

Rob

[Eric Isaacs]

Their SQL Server 2005 database might possibly be running in SQL Server
2000 compatibility mode. NVARCHAR(MAX) was new to SQL Server 2005, so
the compatibility mode would need to be set to SQL Server 2005.

Here's how to change the db to SQL Server 2005 compatibility mode:

EXEC sp_dbcmptlevel databasename, 90

Here's more information: http://msdn.microsoft.com/en-us/library/ms178653.aspx

-Eric Isaacs

[Aaron Bertrand [SQL Server MVP]]

Post by Eric Isaacs
Their SQL Server 2005 database might possibly be running in SQL Server
2000 compatibility mode. NVARCHAR(MAX) was new to SQL Server 2005, so
the compatibility mode would need to be set to SQL Server 2005.

No, not true for data types at least for tables and local variables. On a
2005 instance, try this:

CREATE DATABASE foo;
GO
EXEC master..sp_dbcmptlevel 'foo', '80';
GO
USE foo;
GO
DECLARE @x NVARCHAR(MAX);
GO
CREATE TABLE dbo.foo(bar NVARCHAR(MAX));
GO
USE master;
GO
DROP DATABASE foo;
GO

[Eric Isaacs]

No, not true for data types at least for tables and local variables.  

That being the case, that doesn't explain why the other client can't
execute the scripts. That leads me to believe that the other client
must be must be running SQL Server 2000 instead of SQL Server 2005.

Also, it might accept VARCHAR(MAX), but it truncates to VARCHAR(8000)
removing any trailing spaces...

CREATE DATABASE foo;
GO
EXEC master..sp_dbcmptlevel 'foo', '80';
GO
USE foo;
GO
DECLARE @x NVARCHAR(MAX);
GO
CREATE TABLE dbo.foo(bar NVARCHAR(MAX));
GO

DELETE FROM foo

INSERT INTO dbo.foo(
bar)
SELECT 'A' + SPACE(8000) + 'B'
UNION SELECT 'C' + SPACE(7999) + 'D'
UNION SELECT 'E' + SPACE(7998) + 'F'

SELECT LEN(bar) FROM foo
go

USE master;
GO
DROP DATABASE foo;
GO

-Eric Isaacs

[Plamen Ratchev]

Try this:

INSERT INTO dbo.foo(bar)
SELECT 'A' + CAST(SPACE(8000) AS VARCHAR(MAX)) + 'B'
UNION SELECT 'C' + SPACE(7999) + 'D'
UNION SELECT 'E' + SPACE(7998) + 'F';

SELECT LEN(bar) FROM foo;

Plamen Ratchev
http://www.SQLStudio.com

[Aaron Bertrand [SQL Server MVP]]

Post by Eric Isaacs
That being the case, that doesn't explain why the other client can't
execute the scripts.

Well I think there is at least a minor difference between creating the
database on 2000 and upgrading/migrating to 2005, vs. creating the database
on 2005 and downgrading the compat level. The op did the former. I don't
have the ability to test that today.

A

[Rob]

Eric,

Can't I just go into the datbase properites in SQL Sever Management Studio
and change Options - Compatibility Level to 90?

Post by Eric Isaacs
Their SQL Server 2005 database might possibly be running in SQL Server
2000 compatibility mode. NVARCHAR(MAX) was new to SQL Server 2005, so
the compatibility mode would need to be set to SQL Server 2005.
EXEC sp_dbcmptlevel databasename, 90
Here's more information: http://msdn.microsoft.com/en-us/library/ms178653.aspx
-Eric Isaacs

[Aaron Bertrand [SQL Server MVP]]

Yes, but guess what it does for you behind the scenes? It call
sp_dbcmptlevel.




On 8/5/08 1:31 PM, in article

Post by Rob
Eric,
Can't I just go into the datbase properites in SQL Sever Management Studio
and change Options - Compatibility Level to 90?

Post by Eric Isaacs
Their SQL Server 2005 database might possibly be running in SQL Server
2000 compatibility mode. NVARCHAR(MAX) was new to SQL Server 2005, so
the compatibility mode would need to be set to SQL Server 2005.
EXEC sp_dbcmptlevel databasename, 90
http://msdn.microsoft.com/en-us/library/ms178653.aspx
-Eric Isaacs

[Roy Harvey (SQL Server MVP)]

Post by Rob
I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?

Because the specific database is set to 8.0 or earlier comparability
level. Look up comparability level in the docs.

Roy Harvey
Beacon Falls, CT

[Aaron Bertrand [SQL Server MVP]]

I think they are running SQL Server 2000 but *think* they are running SQL
Server 2005.

Post by Rob
Hello All,
I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?
Regards,
Rob

[Rob]

Aaron,

They are running 2005. I have confirmed that. I am having them check the
Compatibility Level in Properties - Options on the database. This database
was origionaly created in 2000 and then updgraded.

Rob

Post by Aaron Bertrand [SQL Server MVP]
I think they are running SQL Server 2000 but *think* they are running SQL
Server 2005.

Post by Rob
Hello All,
I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?
Regards,
Rob

[Aaron Bertrand [SQL Server MVP]]

Ok, you didn't mention that part when you said they were using SQL Server
2005. Maybe you could create a new database in 2005, transfer the objects
over, and then you will be able to use the new data type.




On 8/5/08 1:33 PM, in article

Post by Rob
Aaron,
They are running 2005. I have confirmed that. I am having them check the
Compatibility Level in Properties - Options on the database. This database
was origionaly created in 2000 and then updgraded.
Rob

Post by Aaron Bertrand [SQL Server MVP]
I think they are running SQL Server 2000 but *think* they are running SQL
Server 2005.

Post by Rob
Hello All,
I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?
Regards,
Rob

[Rob]

Can't I just change the Compatibility Level in the Options?

Post by Aaron Bertrand [SQL Server MVP]
Ok, you didn't mention that part when you said they were using SQL Server
2005. Maybe you could create a new database in 2005, transfer the objects
over, and then you will be able to use the new data type.
On 8/5/08 1:33 PM, in article

Post by Rob
Aaron,
They are running 2005. I have confirmed that. I am having them check the
Compatibility Level in Properties - Options on the database. This database
was origionaly created in 2000 and then updgraded.
Rob

Post by Aaron Bertrand [SQL Server MVP]
I think they are running SQL Server 2000 but *think* they are running SQL
Server 2005.

Post by Rob
Hello All,
I am using SQL 2005 and can use the nvarchar(max) field type with no
problem. If i send an alter statement that adds a field with the
nvarchar(max) type to a client that also has SQL 2005 they get the folowing
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
If they modify the table and try to change it the max they get the error
that states that the limit of this filed type is 4000. Why would this work
on my SQL 2005 and not theirs?
Regards,
Rob

[Eric Isaacs]

Use this script and see if it works...

EXEC sp_dbcmptlevel databasename, 90

-Eric Isaacs

[Aaron Bertrand [SQL Server MVP]]

I doubt it. I am guessing that because I can create a database in SQL
Server 2005, and set it to 80 compatibility level, and the script still
runs, that the issue isn't solely about compatibility level.

However, instead of asking this question a bunch of times, you probably
could have tried it by now and told us the results. If it doesn't work,
then you could try my suggestion.

Post by Rob
Can't I just change the Compatibility Level in the Options?