query problem

please help me solve the problem in query
following is the query

CREATE TABLE [ISMMDM] (
[MDMRFNUM] [BIGINT] NOT NULL IDENTITY (1, 1) NOT NULL ,
[NAME] [NVARCHAR] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONTENT] [VARCHAR] (10000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TDMRFNUM] [BIGINT] NULL ,
[SUBJECT] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FROM] [NVARCHAR] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TO] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TOQDMRFNUM] [BIGINT] NULL ,
[CC] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCQDMRFNUM] [BIGINT] NULL ,
[BCC] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BCCQDMRFNUM] [BIGINT] NULL ,
[LOG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATEDATE] [datetime] NULL,
[MODIDATE] [datetime] NULL,
[FLDSTR1] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FLDSTR2] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FLDSTR3] [NVARCHAR] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DELETED] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL WITH DEFAULT 'N',
[CREATEDBY] [BIGINT] NOT NULL WITH DEFAULT 0,
CONSTRAINT [PK_ISMMDM] PRIMARY KEY CLUSTERED
(
[MDMRFNUM]
) ON [PRIMARY]
) ON [PRIMARY]

what changes should I doThe error messages are clues :)

10000 is too big for the datatype and there is no "With" when defining a default value.|||I dont want alter 10000 so which data type I should use and how

so what would be the correct syntax for this query|||instead of VARCHAR(10000) which is too big, use TEXT (with no number in parentheses after it)|||Or, if you are using SQL Server 2005, VARCHAR(MAX).|||it looks like you are using 2005 since you are using SSMS. in that case use varchar(max) as pootle suggests. text, ntext, image are deprecated in 2005.|||Call me Mr Picky if you like but also... that ain't a query :)|||it looks like you are using 2005 since you are using SSMS. Good spot. One of us is paying attention to the picture clues then :)|||text, ntext, image are deprecated in 2005.ta very much

didn't know that

:)|||:) My problem is solved Thanks to you all for giving suggestion

BUT I heard that using TEXT data type decreases performance so can we
use VARCHAR(MAX) upto how much character it stores and what is the synatax|||varchar(max) has the same size limitation as TEXT. 2gb worth of text as I recall.

just to be clear: do not use TEXT. use nvarchar(max) - text suffers from certain limitations that nvarchar(max) does not.|||can any one tell the correct syntax for using VARCHAR(MAX)
with example

name VARCHAR(MAX) (10000) not working how we use|||drop the (10000).|||drop the (10000).
i have to 10000 with VARCHAR (MAX) but how ?|||how? by leaving it out ;)|||no hablo inglis.|||create table bleh
(
int id identity(1,1) primary key,
someText varchar(max) not null
)|||spot the differences:

create table meh
(
id int identity(1,1) primary key,
someText varchar(max) not null
)

;)|||whoops. ;)

Query problem

I have a table called PriceName having schema
CREATE TABLE PriceName (PriceNameID INT IDENTITY(1,1), Price
DECIMAL(9,2), PriceName VARCHAR(100))
In this table the data is populated in the Price field. Now i want to
update this table to update the PriceName column, that shows the range
in which that price lies.
Ex is shown below
PriceNameID Price PriceName
1 200 0-200
2 300 200-300
3 350 300-350
.....
....
99 1500 1400-1500
100 NULL 1500 & Above
Can someone suggest me the update query for this '
RegardsUPDATE PriceName p1
SET PriceName = ISNULL(CONVERT(VARCHAR(9), (SELECT MAX(p2.Price) FROM
PriceName p2 WHERE p2.Price < p1.Price)), '0') +
ISNULL(' - ' + CONVERT(VARCHAR(9), Price), ' & Above')
Jacco Schalkwijk
SQL Server MVP
"Prashant Thakwani" <thakwani@.rediffmail.com> wrote in message
news:bf0d42bf.0403120517.3815a063@.posting.google.com...
> I have a table called PriceName having schema
>
> CREATE TABLE PriceName (PriceNameID INT IDENTITY(1,1), Price
> DECIMAL(9,2), PriceName VARCHAR(100))
>
> In this table the data is populated in the Price field. Now i want to
> update this table to update the PriceName column, that shows the range
> in which that price lies.
> Ex is shown below
> PriceNameID Price PriceName
> 1 200 0-200
> 2 300 200-300
> 3 350 300-350
> .....
> ....
> 99 1500 1400-1500
> 100 NULL 1500 & Above
>
>
> Can someone suggest me the update query for this '
>
> Regards|||Have u run that script '
I am getting the following error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'p1'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.
Pls take care of this
Regards
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||UPDATE PriceName
SET PriceName = ISNULL(CONVERT(VARCHAR(9), (SELECT MAX(p2.Price) FROM
PriceName p2 WHERE (p2.Price < PriceName.Price) OR PriceName.Price IS
NULL)), '0') +
ISNULL(' - ' + CONVERT(VARCHAR(9), Price), ' & Above')
Jacco Schalkwijk
SQL Server MVP
"Prashant Thakwani" <thakwani@.rediffmail.com> wrote in message
news:OJxZIkDCEHA.624@.TK2MSFTNGP10.phx.gbl...
> Have u run that script '
> I am getting the following error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'p1'.
> Server: Msg 170, Level 15, State 1, Line 3
> Line 3: Incorrect syntax near ')'.
>
> Pls take care of this
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!