| Author |
Topic |
|
pras2007
Posting Yak Master
216 Posts |
 Posted - 2008-12-12 : 18:28:27
|
| Hello All,I wanted to create a table that has a column that would use varchar(max) as a datatype. Below is a sample code that I’m referring too.CREATE TABLE [dbo].[Test_table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Col1] [datetime] NULL, [Col2] [varchar](max) NULL, [Description] [varchar](max) NULL)The above code give me an “invalid data type” when I try to create it under the design view or gives me the below error when running it as a script.Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near 'max'.Using Varchar(8000) is not an option because I will be storing data bigger than 8000. Please advice. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-12-12 : 18:50:29
|
| Thanks for the quick response tkizer, but I do not know how to check the compatibility level set on the database, please provide instructions on how. Thanks once again. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-12-12 : 19:14:14
|
| tkizer, I see it now. But I only have "SQL Server 7.0 (70)" and "SQL Server 2000 (80)" has the options. Its currently set to "SQL Server 2000 (80)". How can I get the Compatibility level of "SQL Server 2005 (90)" to show up as an option? Do I need to download some type of fix? please advice. Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-12 : 20:23:23
|
| You need to have SQL Server 2005(90) installed to use varchar(max). |
|
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-12-12 : 21:02:19
|
| Thanks sodeep,How can I install SQL Server 2005(90) on my sql server? Please advice. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-13 : 01:00:57
|
quote:
Originally posted by pras2007
Thanks sodeep,How can I install SQL Server 2005(90) on my sql server? Please advice. Thanks.
are you sure you're using sql 2005? first check thisSELECT @@VERSIONwhat does it return?if its 8. then its sql 2000 and you've to install sql2005if its 9. then you've sql 2005 but you're using compatibility level of sql 2000.In that case you just need to change compatibnility level to 90 as belowEXEC sp_dbcmptlevel yourdbnamehere,90 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-13 : 08:19:13
|
quote:
Originally posted by visakh16
quote:
Originally posted by pras2007
Thanks sodeep,How can I install SQL Server 2005(90) on my sql server? Please advice. Thanks.
are you sure you're using sql 2005? first check thisSELECT @@VERSIONwhat does it return?if its 8. then its sql 2000 and you've to install sql2005if its 9. then you've sql 2005 but you're using compatibility level of sql 2000.In that case you just need to change compatibnility level to 90 as belowEXEC sp_dbcmptlevel yourdbnamehere,90
He already said he can only see 70 and 80 in options-compatibility level which is SQL 2000.He needs to have 2005 installed from CD/DVD. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-13 : 08:35:43
|
oops..i missed that...  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-12-15 : 11:20:28
|
| Thanks for your responses folks!Tara Kizer,The SQL Server 2005 that I installed on my server (Windows Server 2003) has a 180 - trial version, that I downloaded from Microsoft; Could this be the program? Please advice. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Data type not showing varchar(max) has an option